如何按某一字段内容把一个表拆分成多个表？
有表如下：
ID NAME CLASS
1 AA II
2 BB IV
3 AB IV
4 AC II
5 CC IX
6 SS II
7 AS IX
8 ES IX
---------------------
用什么方法可以吧上的表按照class的内容分成多个表
结果如下：
ID NAME CLASS
1 AA II
4 AC II
6 SS II
--------------------
ID NAME CLASS
2 BB IV
3 AB IV
--------------------
ID NAME CLASS
5 CC IX
7 AS IX
8 ES IX
[解决办法]
insert into table1 select * from tablename where class= 'II '
insert into table2 select * from tablename where class= 'IV '
insert into table3 select * from tablename where class= 'IX '
[解决办法]
DECLARE @CLASS sysname, @s nvarchar(4000)
DECLARE tb CURSOR LOCAL
FOR
SELECT DISTINCT
CLASS
FROM 你的表
OPEN tb
FETCH tb INTO @CLASS
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s = N '
SELECT *
INTO ' + QUOTENAME( 'tb_ ' + @CLASS) + N ' -- 生成的表名为: tb_ <CLASS> , 根据需要自行调整
FROM 你的表
WHERE CLASS = @CLASS
'
EXEC sp_executesql @s, N '@CLASS sysname ', @CLASS
FETCH tb INTO @CLASS
END
CLOSE tb
DEALLOCATE tb
------解决方案--------------------
关注
[解决办法]
create table T(ID int, NAME varchar(10), CLASS varchar(10))
insert T select 1, 'AA ', 'II '
union all select 2, 'BB ', 'IV '
union all select 3, 'AB ', 'IV '
union all select 4, 'AC ', 'II '
union all select 5, 'CC ', 'IX '
union all select 6, 'SS ', 'II '
union all select 7, 'AS ', 'IX '
union all select 8, 'ES ', 'IX '
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ' select * into '+quotename( 'T_ '+CLASS)+ ' from T where CLASS= '+quotename(CLASS, ' ' ' ')+ '; ' from T group by CLASS
exec(@sql)
select * from [T_II]
select * from [T_IV]
select * from [T_IX]
[解决办法]
关注``
[解决办法]
declare @classname varchar(20)
declare cursor_t cursor
for select distinct class from t order by class
open cursor_t
fetch next from cursor_t
into @classname
while @@fetch_status = 0
begin
declare @sql varchar(8000)
select @sql = 'select [ID], [NAME], CLASS into CLASS ' + @classname
+ ' from t where class = ' ' ' + @classname + ' ' ' '
exec(@sql)
fetch next from cursor_t
into @classname
end
close cursor_t
deallocate cursor_t
select * from [CLASSII]
select * from [CLASSIV]
select * from [ClASSIX]
[解决办法]
学习
[解决办法]
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)
USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
