两表查询问题，请高手进来看看

create table dbo.A(编码 varchar(10),批次 varchar(10), 数量 int)

insert A

select 'ZAB01' ,'1', 20

union select 'YAB01' ,'20', 30

union select 'DA01' ,'15', 15

union select 'ZAB01' ,'5', 100

union select 'YAB02' ,'Y13', 300

union select 'ZAB02' ,'8', 50

union select 'YBB01' ,'Y10', 50



create table dbo.B(销售单号 varchar(10),编码 varchar(10), 生产单号 int)

insert B

 select '1' ,'ZAB01' ,123

union select '2' ,'ABC01' ,11

union select '15' ,'DA01' ,12

union select '1' ,'ZAB01' ,13

union select '1' ,'ZAB01' ,15

union select '5' ,'ZAB01' ,18

union select '8' ,'ZAB02' ,21



go



create function dbo.f1(@s1 varchar(10),@s2 varchar(10))

returns varchar(8000)

as

begin 

	declare @str varchar(8000)



	select @str = isnull(@str,'') + ',' + rtrim(生产单号) from dbo.B where 编码 = @s1 and 销售单号 = @s2

	

	return stuff(@str,1,1,'')

end



go





select a.编码,批次,max(数量),max(case when left(批次,1) = 'Y' or left(a.编码,1) = 'Y' then replace(批次,'Y','') else dbo.f1(a.编码,批次) end) 生产单号,case when max(b.编码) is null then null else 批次 end  销售单号

from A left join B  on a.编码 = b.编码

group by a.编码,批次







drop table A,B

drop function f1



/*

编码         批次                     生产单号   销售单号       

---------- ---------- ----------- ------------- ---------- 

ZAB01      1          20          13,15,123    1

DA01       15         15          12           15

YAB01      20         30          20           NULL

ZAB01      5          100         18           5

ZAB02      8          50          21           8

YBB01      Y10        50          10           NULL

YAB02      Y13        300         13           NULL



(7 row(s) affected)





*/

create table A(编码 varchar(100),批次 varchar(100), 数量 int)

insert A

select 'ZAB01' ,'1', 20

union select 'YAB01' ,'20', 30

union select 'DA01' ,'15', 15

union select 'ZAB01' ,'5', 100

union select 'YAB02' ,'Y13', 300

union select 'ZAB02' ,'8', 50

union select 'YBB01' ,'Y10', 50



create table B(销售单号 varchar(100),编码 varchar(100), 生产单号 varchar(100))

insert B

 select '1' ,'ZAB01' ,'123'

union select '2' ,'ABC01' ,'11'

union select '15' ,'DA01' ,'12'

union select '1' ,'ZAB01' ,'13'

union select '1' ,'ZAB01' ,'15'

union select '5' ,'ZAB01' ,'18'

union select '8' ,'ZAB02' ,'21'







create function f1(@no varchar(100))

returns varchar

as

begin 

	declare @str varchar(1000)



	select @str = isnull(@str,'') + ',' + 生产单号 from B where 编码 = @no

	

	return stuff(@str,1,1,'')

end









select a.编码,批次,数量,max(case when left(批次,1) = 'Y' or left(a.编码,1) = 'Y' then replace(批次,'Y','') else dbo.f1(a.编码) end) 生产单号,max(销售单号) 销售单号

from A left join B  on a.编码 = b.编码

group by a.编码,批次,数量







drop table A,B

drop function f1



/*

我想查询成

编码 批次 数量 生产单号 销售单号

ZAB01 1   20 123，13，15 1

YAB01 20  30 20 null

DA01  15  15 12 15

ZAB01 5   100 18 5

YAB02 Y12 300 13 null

ZAB02 8   50 21 8

YBB01 Y10 50 10 null





说明：（1）如果表A的编码字段是以Y开头，或者表A的批次字段是以Y开头，则把批次号

作为生产订单查询出来（批次如带Y把Y去掉），销售单号为空。

（2）如果不满者上述条件，则根据表A批次和编码去表B中查询对应的生产单号，销售单号为批次号

（表A的批次和表B的销售单号是对应的）  

（3）如果同一个销售单号，编码对应多个生产单号，生产单号合并用逗号分开



请高手帮写个sql

*/

--> 生成测试数据表: [A]

IF OBJECT_ID('[A]') IS NOT NULL

	DROP TABLE [A]

GO

CREATE TABLE [A] ([编码] [nvarchar](10),[批次] [nvarchar](10),[数量] [int])

INSERT INTO [A]

SELECT 'ZAB01','1','20' UNION ALL

SELECT 'YAB01','20','30' UNION ALL

SELECT 'DA01','15','15' UNION ALL

SELECT 'ZAB01','5','100' UNION ALL

SELECT 'YAB02','Y13','300' UNION ALL

SELECT 'ZAB02','8','50' UNION ALL

SELECT 'YBB01','Y10','50'



--> 生成测试数据表: [B]

IF OBJECT_ID('[B]') IS NOT NULL

	DROP TABLE [B]

GO

CREATE TABLE [B] ([销售单号] [int],[编码] [nvarchar](10),[生产单号] [int])

INSERT INTO [B]

SELECT '1','ZAB01','123' UNION ALL

SELECT '2','ABC01','11' UNION ALL

SELECT '15','DA01','12' UNION ALL

SELECT '1','ZAB01','13' UNION ALL

SELECT '1','ZAB01','15' UNION ALL

SELECT '5','ZAB01','18' UNION ALL

SELECT '8','ZAB02','21'







-->SQL查询如下:

;WITH T AS

(

	SELECT DISTINCT 编码,

		生产单号=STUFF((SELECT DISTINCT ','+LTRIM([生产单号]) FROM B WHERE B.编码=B1.编码 FOR XML PATH('')),1,1,''),

		销售单号=STUFF((SELECT DISTINCT ','+LTRIM([销售单号]) FROM B WHERE B.编码=B1.编码 FOR XML PATH('')),1,1,'')

	FROM [B] B1

)

SELECT * 

FROM [A]

	OUTER APPLY(

		SELECT 生产单号,销售单号

		FROM T 

		WHERE A.编码=T.编码

		) B

/*

编码	批次	数量	生产单号	销售单号

ZAB01	1	20	123,13,15,18	1,5

YAB01	20	30	NULL	NULL

DA01	15	15	12	15

ZAB01	5	100	123,13,15,18	1,5

YAB02	Y13	300	NULL	NULL

ZAB02	8	50	21	8

YBB01	Y10	50	NULL	NULL

*/