3次select中2次结合UNION返回的行数

Question

我很难给这篇文章起名。

我们如何做到以下几点?

SELECT 'Body' AS LineType, ItemName, Quantity FROM TableX
UNION ALL
SELECT 'Body' AS LineType, ItemName, Quantity FROM TableY
UNION ALL
SELECT 'Trailer' AS LineType, null As ItemName, <count of the above two SELECTs> 

结果应该是这样的:

LineType     ItemName     Qty
Body         Nut          19
Body         Bolt         25
Trailer      NULL         2

请帮;谢谢软体社区。

Answer 1

试试CTE:

;WITH FirstQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM TableX
), SecondQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM TableY
)
SELECT * FROM FirstQ
UNION ALL SELECT * FROM SecondQ
UNION ALL
SELECT 'Trailer' AS LineType, null As ItemName, ((SELECT COUNT(*) FROM FirstQ) +(SELECT COUNT(*) FROM SecondQ)) AS Quantity

示例代码:

declare @TableX table (ItemName varchar(5), Quantity int)
INSERT @TableX VALUES ('aa',10)
INSERT @TableX VALUES ('bb',20)
declare @TableY table (ItemName varchar(5), Quantity int)
INSERT @TableX VALUES ('cc',30)
;WITH FirstQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM @TableX
), SecondQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM @TableY
)
SELECT * FROM FirstQ
UNION ALL SELECT * FROM SecondQ
UNION ALL
SELECT 'Trailer' AS LineType, null As ItemName, ((SELECT COUNT(*) FROM FirstQ) +(SELECT COUNT(*) FROM SecondQ)) AS Quantity

输出:

LineType ItemName Quantity
-------- -------- -----------
Body     aa       10
Body     bb       20
Body     cc       30
Trailer  NULL     3
(4 row(s) affected)

Answer 2

declare @TableX table (ItemName varchar(10),Quantity int)
declare @TableY table (ItemName varchar(10),Quantity int)
insert @tableX VALUES('door',0)
insert @tableX VALUES('window',10)
insert @tableX VALUES('wheel',5)
insert @tableX VALUES('brake',7)
insert @tableX VALUES('handle',3)
insert @tableX VALUES('lamp',207)
insert @tableY VALUES('Ydoor',0)
insert @tableY VALUES('Ywindow',10)
insert @tableY VALUES('Ywheel',5)
insert @tableY VALUES('Ybrake',7)
insert @tableY VALUES('Yhandle',3)
insert @tableY VALUES('Ylamp',207)
SELECT 'Body' as LineType, ItemName, Sum(Quantity) qty, Count(*) [count] FROM
(
SELECT ItemName, Quantity FROM @TableX
UNION
SELECT ItemName, Quantity FROM @TableY
) u
GROUP BY ItemName WITH ROLLUP