SQL查询分组后再分别插入新表中

a476678078 2016-10-26 08:44:03

例：表a
姓名地址
张三地址123
张三地址598
李四幸福路333
李四健康路555
李四地址88888
王五地址77777

结果要求：将结果按照姓名分组，再分别插入3（分组个数）个表中

...全文

335 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

a476678078 2016-10-26

打赏
举报

引用 10 楼 fredrickhu 的回复:

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2016-10-26 09:06:08
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
--	Feb 10 2012 19:39:15 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([姓名] varchar(4),[地址] varchar(9))
insert [a]
select '张三','地址123' union all
select '张三','地址598' union all
select '李四','幸福路333' union all
select '李四','健康路555' union all
select '李四','地址88888' union all
select '王五','地址77777'
--------------开始查询--------------------------
DECLARE @TableName VARCHAR(50)
DECLARE @sql VARCHAR(1000)
SET @sql=''
DECLARE test_cursor CURSOR FOR
SELECT DISTINCT 姓名 FROM dbo.a
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='SELECT * INTO '+@TableName+' FROM (SELECT 姓名,地址 FROM A WHERE 姓名= '''+@TableName+''''+') AS T'
EXEC(@SQL)  
FETCH NEXT FROM test_cursor INTO @TableName
END
CLOSE test_cursor
DEALLOCATE test_cursor
GO

SELECT * FROM 李四
SELECT * FROM 王五
SELECT * FROM 张三
----------------结果----------------------------
/* 姓名   地址
---- ---------
李四   幸福路333
李四   健康路555
李四   地址88888

(3 行受影响)

姓名   地址
---- ---------
王五   地址77777

(1 行受影响)

姓名   地址
---- ---------
张三   地址123
张三   地址598

(2 行受影响)

*/

已经解决，麻烦了。不过感觉你们写代码很规范哦。

a476678078 2016-10-26

打赏
举报

引用 8 楼 wmxcn2000 的回复:


create table test(name varchar(10), address varchar(10))
go
insert into test values
('张三','地址123'),
('张三','地址598'),
('李四','幸福路333'),
('李四','健康路555'),
('李四','地址88888'),
('王五','地址77777')
go
declare @sql varchar(100),@name varchar(10)
declare ss cursor for select distinct name from test
open ss
fetch next from ss into @name 
while @@FETCH_STATUS = 0
begin
    set @sql = 'select * into new_' + @name + ' from test where name =''' + @name + ''''
    exec(@sql)
    fetch next from ss into @name 
end
close ss
deallocate ss 
go
select name,type_desc from sys.tables where name like 'new_%'
go
drop table test 
go


-- drop table new_李四,new_王五,new_张三

name              type_desc
----------------- ------------------------------------------------------------
new_李四          USER_TABLE
new_王五          USER_TABLE
new_张三          USER_TABLE

(3 行受影响)

刚把游标看懂了，然后这边做好了。感谢感谢！！！

--小F-- 2016-10-26

打赏
举报

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2016-10-26 09:06:08
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
--	Feb 10 2012 19:39:15 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([姓名] varchar(4),[地址] varchar(9))
insert [a]
select '张三','地址123' union all
select '张三','地址598' union all
select '李四','幸福路333' union all
select '李四','健康路555' union all
select '李四','地址88888' union all
select '王五','地址77777'
--------------开始查询--------------------------
DECLARE @TableName VARCHAR(50)
DECLARE @sql VARCHAR(1000)
SET @sql=''
DECLARE test_cursor CURSOR FOR
SELECT DISTINCT 姓名 FROM dbo.a
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='SELECT * INTO '+@TableName+' FROM (SELECT 姓名,地址 FROM A WHERE 姓名= '''+@TableName+''''+') AS T'
EXEC(@SQL)  
FETCH NEXT FROM test_cursor INTO @TableName
END
CLOSE test_cursor
DEALLOCATE test_cursor
GO

SELECT * FROM 李四
SELECT * FROM 王五
SELECT * FROM 张三
----------------结果----------------------------
/* 姓名   地址
---- ---------
李四   幸福路333
李四   健康路555
李四   地址88888

(3 行受影响)

姓名   地址
---- ---------
王五   地址77777

(1 行受影响)

姓名   地址
---- ---------
张三   地址123
张三   地址598

(2 行受影响)

*/

卖水果的net 2016-10-26

打赏
举报


-- 如果不想用游标，可以这样

declare @sql varchar(1000) = ''
select @sql = 
@sql + 'select * into new_' + name + ' from test where name =''' + name + ''';'
from (
    select distinct name from test 
) x
exec(@sql) 
go

卖水果的net 2016-10-26

打赏
举报


create table test(name varchar(10), address varchar(10))
go
insert into test values
('张三','地址123'),
('张三','地址598'),
('李四','幸福路333'),
('李四','健康路555'),
('李四','地址88888'),
('王五','地址77777')
go
declare @sql varchar(100),@name varchar(10)
declare ss cursor for select distinct name from test
open ss
fetch next from ss into @name 
while @@FETCH_STATUS = 0
begin
    set @sql = 'select * into new_' + @name + ' from test where name =''' + @name + ''''
    exec(@sql)
    fetch next from ss into @name 
end
close ss
deallocate ss 
go
select name,type_desc from sys.tables where name like 'new_%'
go
drop table test 
go


-- drop table new_李四,new_王五,new_张三

name              type_desc
----------------- ------------------------------------------------------------
new_李四          USER_TABLE
new_王五          USER_TABLE
new_张三          USER_TABLE

(3 行受影响)

a476678078 2016-10-26