22,301
社区成员




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
*/