求一个sql,返回行问题

[tds__609]

表A
id 编号 数量
1 A001 4
2 A002 2

表B
id 编号 姓名
1 A001 张三
2 A001 李四
3 A002 王五

求sql语句，要求除了返回对应的姓名外，数量不足的，补一行A表数据，返回结果
编号 数量 姓名
A001 4 张三
A001 4 李四
A001 4 （空）
A002 2 王五
A002 2 (空)

[spiritofdragon]

真心看不懂需求。

[Neo_whl]

]数量不足的，补一行A表数据怎么理解？

[tds__609]

引用 2 楼 u011015550 的回复:

]数量不足的，补一行A表数据怎么理解？

这是一个报表数据，数量一行会进行合并。 举例：A001 有4个人，但表B中只有2个， 所以需要的报表，除了列出已经的表B中的2个，还要补一行，显示还有2个人未知

[中国风]

SELECT 编号,数量, 姓名
FROM 
(
SELECT a.编号,a.数量,b.姓名,b.ID FROM B,A WHERE a.编号=b.编号
UNION ALL 
SELECT a.编号,a.数量,b.姓名,ID=a.数量+1 
FROM A 
	LEFT JOIN (SELECT 编号,姓名='空白',COUNT(*) AS 数量 FROM B GROUP BY 编号) AS b ON a.编号=b.编号 
	INNER JOIN master.dbo.spt_values AS c ON c.type='P' AND c.number<b.数量-ISNULL(a.数量,0)
WHERE b.数量  IS NULL OR b.数量<a.数量
)T
ORDER BY 编号,ID

[tds__609]

引用 2 楼 u011015550 的回复:

]数量不足的，补一行A表数据怎么理解？

最后得到的数据应该是： 编号 数量 姓名 A001 1 张三 A001 1 李四 A001 2 （空） A002 1 王五 A002 1 (空) 注：A001 4个人，已经2人，未知2人 A002 2个人，已知1人，未知1人

[中国风]

引用 4 楼 tds__609 的回复:

[quote=引用 2 楼 u011015550 的回复:] ]数量不足的，补一行A表数据怎么理解？

最后得到的数据应该是： 编号 数量 姓名 A001 1 张三 A001 1 李四 A001 2 （空） A002 1 王五 A002 1 (空) 注：A001 4个人，已经2人，未知2人 A002 2个人，已知1人，未知1人[/quote] 显示数量是什么格式？按以上格式这样调整

SELECT 编号,数量, 姓名
FROM 
(
SELECT a.编号,数量=1,b.姓名,b.ID FROM B,A WHERE a.编号=b.编号
UNION ALL 
SELECT a.编号,b.数量-a.数量,b.姓名,ID=a.数量+1 
FROM A 
	LEFT JOIN (SELECT 编号,姓名='空白',COUNT(*) AS 数量 FROM B GROUP BY 编号) AS b ON a.编号=b.编号 
WHERE b.数量  IS NULL OR b.数量<a.数量
)T
ORDER BY 编号,ID

[tds__609]

引用 6 楼 roy_88 的回复:

[quote=引用 4 楼 tds__609 的回复:] [quote=引用 2 楼 u011015550 的回复:] ]数量不足的，补一行A表数据怎么理解？

最后得到的数据应该是： 编号 数量 姓名 A001 1 张三 A001 1 李四 A001 2 （空） A002 1 王五 A002 1 (空) 注：A001 4个人，已经2人，未知2人 A002 2个人，已知1人，未知1人[/quote] 显示数量是什么格式？按以上格式这样调整

SELECT 编号,数量, 姓名
FROM 
(
SELECT a.编号,数量=1,b.姓名,b.ID FROM B,A WHERE a.编号=b.编号
UNION ALL 
SELECT a.编号,b.数量-a.数量,b.姓名,ID=a.数量+1 
FROM A 
	LEFT JOIN (SELECT 编号,姓名='空白',COUNT(*) AS 数量 FROM B GROUP BY 编号) AS b ON a.编号=b.编号 
WHERE b.数量  IS NULL OR b.数量<a.数量
)T
ORDER BY 编号,ID

[/quote] 这种方法有个问题，假如A003 规定有2个人，表B中无记录， 这时候，应该出来一条数据 A003 2 空 但按照您的方法，出来2条数据 A003 1 空 A003 空 空

[中国风]

方法相同，调整一下显示格式

use Tempdb
go
--> --> 
 
declare @A table([id] int,[编号] nvarchar(4),[数量] int)
Insert @A
select 1,N'A001',4 union all
select 2,N'A002',2
 

declare @B table([id] int,[编号] nvarchar(4),[姓名] nvarchar(10))
Insert @B
select 1,N'A001',N'张三' union all
select 2,N'A001',N'李四' union all
select 3,N'A002',N'王五'

SELECT 编号,数量,姓名
FROM 
(
SELECT a.编号,数量=1,b.姓名,b.ID FROM @B AS b,@A  AS a WHERE a.编号=b.编号
UNION ALL 
SELECT a.编号,a.数量-b.数量,b.姓名,ID=b.ID+1 
FROM @A  AS a
    LEFT JOIN (SELECT 编号,姓名='空白',COUNT(*) AS 数量,ISNULL(MAX(ID),0)+1 AS ID FROM @B GROUP BY 编号) AS b ON a.编号=b.编号 
WHERE b.数量  IS NULL OR b.数量<a.数量
)T
ORDER BY 编号,ID

/*
编号	数量	姓名
A001	1	张三
A001	1	李四
A001	2	空白
A002	1	王五
A002	1	空白
*/