这种复杂SQL语句怎么写?

Question

表一:
 商品     数量      单号    行号   商品一     3        2        1
商品二     1        2        2
商品三     2        2        3
商品四     2        2        4表二:单号   行号     商品     序列号 
2       1      商品一    11111
2       1      商品一    11112
2       1      商品一    11113
2       2      商品二    21111
2       3      商品三    31111
2       3      商品三    31112
需要显示的效果为:
 商品     数量      单号    行号     序列号
商品一     3        2        1     11111,11112,11113
商品二     1        2        2     21111
商品三     2        2        3     31111,31112 
商品四     2        2        4
这个SQL语句怎么写?

Answer 1

--******************************************************************
--                       合并列值 
--******************************************************************
表结构，数据如下： 
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 

Answer 2

拆解组装SQL字符串全过程
http://blog.csdn.net/jinjazz/archive/2009/09/01/4507720.aspx

Answer 3

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([商品] varchar(6),[数量] int,[单号] int,[行号] int)
insert [tb]
select '商品一',3,2,1 union all
select '商品二',1,2,2 union all
select '商品三',2,2,3 union all
select '商品四',2,2,4
--> 测试数据：[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([单号] int,[行号] int,[商品] varchar(6),[序列号] int)
insert [tb2]
select 2,1,'商品一',11111 union all
select 2,1,'商品一',11112 union all
select 2,1,'商品一',11113 union all
select 2,2,'商品二',21111 union all
select 2,3,'商品三',31111 union all
select 2,3,'商品三',31112
 
create function f_join(@单号 int,@行号 int) 
returns varchar(200)
asbegin
declare @s varchar(200)
 select @s= isnull(ltrim(@s)+',','')+ltrim(序列号)  from tb2 where [单号]=@单号 and [行号]=@行号
  return @s
end
select tb.商品,数量,单号,行号,序列号  from   tb 
left join  (select [商品],序列号=dbo.f_join([单号],[行号]) from tb2 group by [单号],[行号],[商品] ) m 
on tb.商品=m.商品/*
商品     数量          单号          行号          序列号
------ ----------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
商品一    3           2           1           11111,11112,11113
商品二    1           2           2           21111
商品三    2           2           3           31111,31112
商品四    2           2           4           NULL(4 行受影响)
*/
 

Answer 4

if OBJECT_ID('tb1') is  not null drop table tb1
  go
create table tb1(商品  nvarchar(10),  数量  int,    单号 int,    行号  int)
  go 
insert tb1
select
'商品一',    3  ,      2   ,     1 union all select
'商品二',    1 ,       2  ,      2 union all select
'商品三',    2   ,     2 ,       3 union all select
'商品四' ,   2,        2,        4 
if OBJECT_ID('tb2') is  not null drop table tb2
  go
create table tb2(单号 int, 行号 int,    商品  nvarchar(10) , 序列号  int)
  go 
insert tb2
select
2     , 1,      '商品一',   11111    union all select
2    ,  1 ,     '商品一' ,   11112 union all select
2   ,   1  ,    '商品一'  ,  11113 union all select
2  ,    2   ,   '商品二'   , 21111 union all select
2 ,     3    ,  '商品三'    ,31111 union all select
2,      3     , '商品三'    ,31112 
if object_id('f_tb') is not null drop function f_tb
go
  create function f_tb(@sp nvarchar(100))
    returns nvarchar(100)
    as
   begin
     declare @sql nvarchar(4000)
     set @sql=N''
     select @sql=@sql+N','+cast(序列号 as nvarchar) from tb2 where 商品=@sp
     set @sql=stuff(@sql,1,1,N'')
     return(@sql)
   end
goselect *,dbo.f_tb(商品) as 序列号 from tb1/*
商品 数量 单号 行号 序列号
商品一 3 2 1 11111,11112,11113
商品二 1 2 2 21111
商品三 2 2 3 31111,31112
商品四 2 2 4 NULL
*/

Answer 5

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-16 19:26:33.200●●●●●
　★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb1') is  not null drop table tb1
  go
create table tb1(商品  nvarchar(10),  数量  int,    单号 int,    行号  int)
  go 
insert tb1
select
'商品一',    3  ,      2   ,     1 union all select
'商品二',    1 ,       2  ,      2 union all select
'商品三',    2   ,     2 ,       3 union all select
'商品四' ,   2,        2,        4 
if OBJECT_ID('tb2') is  not null drop table tb2
  go
create table tb2(单号 int, 行号 int,    商品  nvarchar(10) , 序列号  int)
  go 
insert tb2
select
2     , 1,      '商品一',   11111    union all select
2    ,  1 ,     '商品一' ,   11112 union all select
2   ,   1  ,    '商品一'  ,  11113 union all select
2  ,    2   ,   '商品二'   , 21111 union all select
2 ,     3    ,  '商品三'    ,31111 union all select
2,      3     , '商品三'    ,31112 
if object_id('f_tb') is not null drop function f_tb
go
  create function f_tb(@sp nvarchar(100))
    returns nvarchar(100)
    as
   begin
     declare @sql nvarchar(4000)
     set @sql=N''
     select @sql=@sql+N','+cast(序列号 as nvarchar) from tb2 where 商品=@sp
     set @sql=stuff(@sql,1,1,N'')
     return(@sql)
   end
goselect *,dbo.f_tb(商品) as 序列号 from tb1/*
商品 数量 单号 行号 序列号
商品一 3 2 1 11111,11112,11113
商品二 1 2 2 21111
商品三 2 2 3 31111,31112
商品四 2 2 4 NULL
*/很简单的！呵呵！写一个函数合并相同商品的序列号（注意序列号要转换为字符型）

Answer 6

---写个2005的
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-09-16 19:29:31
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([商品] varchar(6),[数量] int,[单号] int,[行号] int)
insert [a]
select '商品一',3,2,1 union all
select '商品二',1,2,2 union all
select '商品三',2,2,3 union all
select '商品四',2,2,4
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([单号] int,[行号] int,[商品] varchar(6),[序列号] int)
insert [b]
select 2,1,'商品一',11111 union all
select 2,1,'商品一',11112 union all
select 2,1,'商品一',11113 union all
select 2,2,'商品二',21111 union all
select 2,3,'商品三',31111 union all
select 2,3,'商品三',31112
--------------开始查询--------------------------
;with f as
(select 行号, [序列号]=stuff((select ','+ltrim([序列号]) from b t where 行号=b.行号 for xml path('')), 1, 1, '') 
from b 
group by 行号)
select a.*,f.序列号  from a left join f on a.行号=f.行号
----------------结果----------------------------
/* 商品     数量          单号          行号          序列号
------ ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
商品一    3           2           1           11111,11112,11113
商品二    1           2           2           21111
商品三    2           2           3           31111,31112
商品四    2           2           4           NULL(4 行受影响)*/

Answer 7

---写个2005的
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-09-16 19:29:31
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([商品] varchar(6),[数量] int,[单号] int,[行号] int)
insert [a]
select '商品一',3,2,1 union all
select '商品二',1,2,2 union all
select '商品三',2,2,3 union all
select '商品四',2,2,4
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([单号] int,[行号] int,[商品] varchar(6),[序列号] int)
insert [b]
select 2,1,'商品一',11111 union all
select 2,1,'商品一',11112 union all
select 2,1,'商品一',11113 union all
select 2,2,'商品二',21111 union all
select 2,3,'商品三',31111 union all
select 2,3,'商品三',31112
--------------开始查询--------------------------
;with f as
(select 行号, [序列号]=stuff((select ','+ltrim([序列号]) from b t where 行号=b.行号 for xml path('')), 1, 1, '') 
from b 
group by 行号)
select a.*,f.序列号  from a left join f on a.行号=f.行号
----------------结果----------------------------
/* 商品     数量          单号          行号          序列号
------ ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
商品一    3           2           1           11111,11112,11113
商品二    1           2           2           21111
商品三    2           2           3           31111,31112
商品四    2           2           4           NULL(4 行受影响)*/