SQL查询合并查询列的问题
假设我有一个表是上图的样子，我想实现group by on_tick_sellnumber_str 的效果，就是说按找这个列来合并最后两列的产品及数量，合并后的样子应是下面的样子，请大家指点一下有没有好的办法呢
on_tick_sellnumber_str detail(合并后的列名)
NH-AX000020 精选年货【5】；特色年货【27】
[解决办法]
/*标题：按某字段合并字符串之一(简单合并)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-11-06地点：广东深圳描述：将如下形式的数据按id字段合并value字段。id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id value------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）*/--1、sql2000中只能用自定义的函数解决create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')gocreate function dbo.f_str(@id int) returns varchar(100)asbegin declare @str varchar(1000) set @str = '' select @str = @str + ',' + cast(value as varchar) from tb where id = @id set @str = right(@str , len(@str) - 1) return @strendgo--调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb--2、sql2005中的方法create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb--3、使用游标合并数据create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')godeclare @t table(id int,value varchar(100))--定义结果集表变量--定义游标并进行合并处理declare my_cursor cursor local forselect id , value from tbdeclare @id_old int , @id int , @value varchar(10) , @s varchar(100)open my_cursorfetch my_cursor into @id , @valueselect @id_old = @id , @s=''while @@FETCH_STATUS = 0begin if @id = @id_old select @s = @s + ',' + cast(@value as varchar) else begin insert @t values(@id_old , stuff(@s,1,1,'')) select @s = ',' + cast(@value as varchar) , @id_old = @id end fetch my_cursor into @id , @valueENDinsert @t values(@id_old , stuff(@s,1,1,''))close my_cursordeallocate my_cursorselect * from @tdrop table tb
[解决办法]
行列转换
select on_tick_sellnumber_str ，精选年货=sum(case 倒数第二列的列名 when '精品年货' then exp end),
特色年货=sum(case 倒数第二列的列名 when '特色年货' then exp end)
from tb
group by on_tick_sellnumber_str
[解决办法]
http://blog.csdn.net/happy664618843/archive/2010/03/05/5350835.aspx 参考
[解决办法]
create table on_tick( on_tick_sellnumber_str nvarchar(256), ticktype_name_des nvarchar(256), expr2 int)insert into on_tick values(N'NH-AX000020', N'精选年货', 5)insert into on_tick values(N'NH-AX000020', N'特色年货', 27)insert into on_tick values(N'NH-AX000021', N'精选年货', 1)insert into on_tick values(N'NH-AX000021', N'特色年货', 2)select on_tick_sellnumber_str,'精选年货'=sum(case ticktype_name_des when '精选年货' then expr2 end),'特色年货'=sum(case ticktype_name_des when '特色年货' then expr2 end)from on_tickgroup by on_tick_sellnumber_str
[解决办法]
dawugui 给出的就很全面了。支持1楼。
[解决办法]
应该是字符串的合并select on_tick_sellnumber_str , [ticketype_name_dec] = stuff((select ',' + [ticketype_name_dec] from tb t where on_tick_sellnumber_str = tb.on_tick_sellnumber_str for xml path('')) , 1 , 1 , '')from tbgroup by on_tick_sellnumber_str
[解决办法]
dawugui的很经典，我以前问了个类似的问题也是他解决的
[解决办法]
--(1)字符串分拆并统计 create table #tb ( id int, col varchar(50), num int ) insert into #tb select 1,'aa,bb,cc',10 union all select 2,'aa,aa,bb',20 union all select 3,'aa,aa,bb',20 union all select 4,'dd,ccc,c',30 union all select 5,'ddaa,ccc',40 union all select 6,'eee,ee,c',50 declare @Len int select top 1 @Len=len(col)+1 from #tb order by len(col) select @Len set rowcount @Len select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B set rowcount 0 ;with hgo as ( select b.id, number=substring(col,A.id,charindex(',',col+',',A.id)-A.id) from #TT A join #tb b on substring(','+col,A.id,1)=',' ) select number,count(distinct id) [count],count(number) [number] from hgo group by number --(2)分拆字符串求和 create table #tb ( id int, col varchar(50), num int ) insert into #tb select 1,'aa,bb,cc',10 union all select 2,'aa,aa,bb',20 union all select 3,'aa,aa,bb',20 union all select 4,'dd,ccc,c',30 union all select 5,'ddaa,ccc',40 union all select 6,'eee,ee,c',50 declare @len int select top 1 @len=len(col)+1 from #tb order by len(col) desc set rowcount @len select id=identity(int,1,1) into # from dbo.syscolumns A,dbo.syscolumns B set rowcount 0 ;with hgo as ( select distinct data=substring(A.col,b.id,charindex(',',A.col+',',b.id)-b.id), A.num,b.id from #tb A,# b where substring(','+A.col,b.id,1)=',' ) select data,sum(num) from hgo group by data --(3)合并法(函数) create table tb1 ( col1 varchar(10), col2 int ) insert into tb1 select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'b',3 create function dbo.FC_Str(@col1 varchar(100)) returns varchar(100) as begin declare @i varchar(100) set @i='' select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1 return(stuff(@i,1,1,'')) end select col1,dbo.FC_Str(col1) from tb1 group by col1 --(4)固定行合并法 create table #tb ( col1 varchar(10), col2 int ) insert into #tb select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'c',3 select col1, col2=cast(min(col2) as varchar)+ case when count(*)=1 then '' else +','+cast(max(col2) as varchar) end from #tb group by col1 --(5)临时表合并法 if object_id('tb') is not null drop table tb create table tb ( col1 varchar(10), col2 int ) insert into tb select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'b',3 select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2 declare @col1 varchar(20) declare @col2 varchar(100) update #t1 set @col2= case when @col1=col1 then @col2+ ',' +col2 else col2 end, @col1=col1, col2=@col2 select * from #t1 select col1,col2=max(col2) from #t1 group by col1 --(6)字符串拆分 create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100)) returns @R table (Col nvarchar(100)) as begin declare @StrLen int set @StrLen=len(@Str) while charindex(@Split,@StrLen)>0 begin insert into @R values(left(@Str,charindex(@Split,@StrLen)-1)) set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'') end insert into @R values(@Str) return end declare @a nvarchar(4000) set @a='1,23,a' select dbo.FC_SlpitStr(@a,',') --(2)动态T-sql语句 declare @S varchar(100) set @s='1,23,a' declare @sql varchar(100) set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+'''' print(@sql) --(8)分拆数据到列 ;declare @tb table ( col varchar(20) ) insert into @tb select 'aa,bb,cc' union all select 'AAA,BB' union all select 'AAA' declare @i int,@j varchar set @j=1 select col+',' as col into #t1 from @tb--加上',' select @i=max(len(Col)-len(replace(Col,',',''))) from #t1 while @j!>@i begin exec('alter table #t1 add [Col'+@j+'] varchar(10)') exec('update #t1 set [Col'+@j+']=left(col,charindex('','',col)-1),col=stuff(col,1,charindex('','',col),'''') where Len(col)>1') set @j=@j+1 end alter table #T1 drop column Col select * from #t1 drop table #t1; /* Col1 Col2 Col3 ---------- ---------- ---------- aa bb cc AAA BB NULL AAA NULL NULL */ --(9)字符串分拆并统计 create table #tb ( id int, col varchar(50), num int ) insert into #tb select 1,'aa,bb,cc',10 union all select 2,'aa,aa,bb',20 union all select 3,'aa,aa,bb',20 union all select 4,'dd,ccc,c',30 union all select 5,'ddaa,ccc',40 union all select 6,'eee,ee,c',50 declare @Len int select top 1 @Len=len(col)+1 from #tb order by len(col) select @Len set rowcount @Len select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B set rowcount 0 ;with hgo as ( select b.id, number=substring(col,A.id,charindex(',',col+',',A.id)-A.id) from #TT A join #tb b on substring(','+col,A.id,1)=',' ) select number,count(distinct id) [count],count(number) [number] from hgo group by number number count number -------------------------------------------------- ----------- ----------- aa 3 5 bb 3 3 c 2 2 cc 1 1 ccc 2 2 dd 1 1 ddaa 1 1 ee 1 1 eee 1 1 (9 行受影响) 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/22/5224723.aspx
[解决办法]
贴错啦
[解决办法]
up
[解决办法]
create table tttbbb( ON_Tick nvarchar(50), TickTypeName nvarchar(50), Expr2 nvarchar(50))insert into tttbbb select 'NH-AX000020','精选年货','5'insert into tttbbb select 'NH-AX000020','特色年货','27'insert into tttbbb select 'NH-AX000021','精选年货','3'insert into tttbbb select 'NH-AX000021','特色年货','27'drop function dbo.GetInfocreate function dbo.GetInfo(@Tick nvarchar(30))returns nvarchar(100)asbegin declare @sql nvarchar(1000) set @sql='' select @sql=@sql+TickTypeName+Expr2+';' from tttbbb where ON_Tick=@Tick return @sqlendselect distinct (dbo.GetInfo(ON_Tick)) as on_tick_sellnumber_strdetail,ON_Tickfrom tttbbbon_tick_sellnumber_strdetail ON_Tick---------------------------------------------------------------- --------------------------------------------------精选年货3;特色年货27; NH-AX000021精选年货5;特色年货27; NH-AX000020(2 行受影响)
[解决办法]
create table tb(id varchar(20) , name nvarchar(20), val int)insert into tb values('nh-ax000020',N'精选年货',5)insert into tb values('nh-ax000020',N'特色年货',27)insert into tb values('nh-ax000021',N'精选年货',3)insert into tb values('nh-ax000021',N'特色年货',27)goselect id, detail = stuff((select ',' + name + N'【' + cast(val as varchar) + N'】' from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb/*id detail-------------------- ---------------------------nh-ax000020 精选年货【5】,特色年货【27】nh-ax000021 精选年货【3】,特色年货【27】(2 行受影响)*/
[解决办法]
dawugui解答得好详尽啊！
佩服！
[解决办法]