这个合并的查询怎么写?
/*有一个表(注:每个箱内的产品编号和颜色是一样的)test1单号 箱号 产品编号 颜色 尺码 数量 ---------- ---------- ---------- ---------- ---------- ----------- 10001 001 AAA 红色 21 510001 001 AAA 红色 22 810001 001 AAA 红色 23 110001 002 AAA 蓝色 21 210001 002 AAA 蓝色 22 310002 003 BBB 黄色 21 510002 003 BBB 黄色 22 3-----------------------------------------------写一个查询要求结果如下:单号 箱号 信息-----------------------------------------------10001 001 AAA#红色#21-5#22-8#23-110001 002 AAA#蓝色#21-2#22-310002 003 BBB#黄色#21-5#22-3这个查询怎么写?*/---建立测试环境(MSSQL2000)if exists (select * from sysobjects where id = object_id(N'Test1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)begin drop table Test1endGOCREATE TABLE [test1] ( [单号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [箱号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [产品编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [颜色] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [尺码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [数量] [int] NULL ) ON [PRIMARY]GOInsert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA ','红色 ','21 ',5)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA ','红色 ','22 ',8)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA ','红色 ','23 ',1)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','002','AAA ','蓝色 ','21 ',2)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','002','AAA ','蓝色 ','22 ',3)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10002','003','BBB','黄色','21',5)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10002','003','BBB','黄色','22',3)select * from test1
create function f_hbtest(@单号 varchar(30),@箱号 varchar(30),@产品编号 varchar(30),@颜色 varchar(30)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + '#' + 尺码+'-'+ltrim(数量) from test1 where 单号=@单号 and 箱号=@箱号 and 产品编号=@产品编号 and 颜色=@颜色 set @str = right(@str , len(@str) - 1) return(@str) end goselect distinct 单号,箱号,信息=replace(产品编号+'#'+颜色+'#'+dbo.f_hbtest(单号,箱号,产品编号,颜色),' ','') from test1 /*单号 箱号 信息---------- ---------- ---------------------------10001 001 AAA#红色#21-5#22-8#23-110001 002 AAA#蓝色#21-2#22-310002 003 BBB#黄色#21-5#22-3*/
[解决办法]
合并列值 --*******************************************************************************************表结构，数据如下： id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果： id values ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即：group by id, 求 value 的和（字符串相加） 1. 旧的解决方法(在sql server 2000中只能用函数解决。) --=============================================================================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') go --1. 创建处理函数 CREATE FUNCTION dbo.f_strUnite(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @str varchar(8000) SET @str = '' SELECT @str = @str + ',' + value FROM tb WHERE id=@id RETURN STUFF(@str, 1, 1, '') END GO -- 调用函数 SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id drop table tb drop function dbo.f_strUnite go/* id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc （所影响的行数为 2 行） */ --===================================================================================2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 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') go -- 查询处理 SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM tb N WHERE id = A.id FOR XML AUTO ), ' <N value="', ','), '"/>', ''), 1, 1, '') )N drop table tb /* id values ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc (2 行受影响) */ --SQL2005中的方法2 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') go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id /* id values ----------- -------------------- 1 aa,bb 2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb
[解决办法]
CREATE TABLE [test1] ( [单号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [箱号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [产品编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [颜色] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [尺码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [数量] [int] NULL ) ON [PRIMARY]Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA ','红色 ','21 ',5)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA ','红色 ','22 ',8)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA ','红色 ','23 ',1)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','002','AAA ','蓝色 ','21 ',2)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','002','AAA ','蓝色 ','22 ',3)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10002','003','BBB','黄色','21',5)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10002','003','BBB','黄色','22',3)with t2 as(select 单号,箱号,replace(产品编号,' ','')+'#'+replace(颜色,' ','')+'#'+cast((select replace(尺码+'-'+cast(数量 as varchar)+'#',' ','') from test1 where 单号=t.单号 and 箱号=t.箱号 and 产品编号=t.产品编号 and 颜色=t.颜色for xml path('')) as varchar) xfrom test1 tgroup by 单号,箱号,产品编号,颜色)select 单号,箱号,left(x,len(x)-1) 信息from t2单号 箱号 信息---------- ---------- --------------------------10001 001 AAA#红色#21-5#22-8#23-110001 002 AAA#蓝色#21-2#22-310002 003 BBB#黄色#21-5#22-3(3 row(s) affected)
[解决办法]
--mssql2000的写法if exists (select * from sysobjects where id = object_id(N'Test1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)begin drop table Test1endGOCREATE TABLE [test1] ( [单号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [箱号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [产品编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [颜色] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [尺码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [数量] [int] NULL ) ON [PRIMARY]GOInsert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA','红色','21',5)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA','红色','22',8)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','001','AAA','红色','23',1)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','002','AAA','蓝色','21',2)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10001','002','AAA','蓝色','22',3)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10002','003','BBB','黄色','21',5)Insert test1 (单号,箱号,产品编号,颜色,尺码,数量) Values ( '10002','003','BBB','黄色','22',3)if object_id('uf_str') is not null drop function uf_strgocreate function uf_str(@单号 varchar(10),@箱号 varchar(10))returns varchar(200)asbegin declare @str varchar(200) select @str=isnull(@str+'#','')+尺码+'-'+ltrim(数量) from test1 where 单号=@单号 and 箱号=@箱号 return(@str)endgoselect 单号,箱号,信息=max(产品编号)+'#'+max('颜色')+'#'+dbo.uf_str(单号,箱号) from test1 group by 单号,箱号go/*单号 箱号 信息---------- ---------- ------------------------------------------------------------------------------------------------------------10001 001 AAA#颜色#21-5#22-8#23-110001 002 AAA#颜色#21-2#22-310002 003 BBB#颜色#21-5#22-3(3 行受影响)*/