27,582
社区成员




SELECT * FROM table1 WHERE 序号 <= 5
UNION ALL
SELECT 6,
'其他',
SUM(SumTotal),
SUM(SumNumber),
AVG(PJ),
AVG(XdUnitPrice)
FROM table1
WHERE 序号 > 5
USE tempdb
GO
CREATE TABLE #TestTB
(
序号 INT,
GoodsName NVARCHAR(50),
SumTotal MONEY ,
SumNumber INT,
PJ FLOAT,
XdUnitPrice INT
)
INSERT INTO #TestTB (序号,goodsName,SumTotal,SumNumber,PJ,XdUnitPrice)
VALUES
(1,'厅装可乐',138.00,30,4.600000,3 ),
(2,'可乐',108.00,26,4.153846,3 ),
(3,'厅装雪碧',102.00,24,4.250000,3),
(4,'雪碧',48.00,16,3.000000,3 ),
(5,'水费',15.00,5,3.000000,3 ),
(6,'电费',12.00,4,3.000000,3),
(7,'液化气',12.00,4,3.000000,3 ),
(8,'房租',9.00,3,3.000000,3)
;WITH CTE AS(
SELECT TOP 5 序号 FROM #TestTB)
SELECT TOP 5
a.序号,goodsName,SumTotal,SumNumber,PJ,XdUnitPrice
FROM #TestTB a
INNER JOIN CTE b ON a.序号 = b.序号
UNION ALL
SELECT MIN(序号)-1,'其它',SUM(SumTotal),SUM(SumNumber),AVG(PJ),AVG(XdUnitPrice)
FROM #TestTB a
WHERE NOT EXISTS(SELECT * FROM CTE WHERE 序号 = a.序号)
WITH t AS (
SELECT ROW_NUMBER() OVER(ORDER BY SumTotal DESC) AS 序号,
*
FROM table1
)
SELECT * FROM t WHERE 序号 <= 5
UNION ALL
SELECT 6,
'其他',
SUM(SumTotal),
SUM(SumNumber),
AVG(PJ),
AVG(XdUnitPrice)
FROM t
WHERE 序号 > 5