使用OVER(PARTITION BY)实现小计合计

CREATE TABLE #T
(
手机 VARCHAR(10),
厂商 VARCHAR(10),
状态 varchar(10)
)

INSERT INTO #T VALUES('苹果-A1','001','0002')
INSERT INTO #T VALUES('苹果-A1','001','0003')
INSERT INTO #T VALUES('苹果-A1','001','0001')
INSERT INTO #T VALUES('苹果-A5','001','0002')
INSERT INTO #T VALUES('苹果-A4','001','0003')
INSERT INTO #T VALUES('苹果-A3','001','0001')
INSERT INTO #T VALUES('华为-A1','002','0003')
INSERT INTO #T VALUES('华为-A1','002','0002')
INSERT INTO #T VALUES('华为-A2','002','0001')
INSERT INTO #T VALUES('华为-A2','002','0005')
INSERT INTO #T VALUES('华为-A5','002','0004')
INSERT INTO #T VALUES('华为-A4','002','0002')
INSERT INTO #T VALUES('华为-A3','002','0003')
INSERT INTO #T VALUES('华为-A6','002','0001')

SELECT
厂商 AS 型号,
SUM(小计) OVER(PARTITION BY 厂商) AS 合计,
手机,小计,在线,故障,备用,报废,丢失
FROM
(
SELECT 手机,厂商,COUNT(1) AS 小计,
SUM(CASE WHEN 状态='0001' THEN 1 ELSE NULL END) AS 在线,
SUM(CASE WHEN 状态='0002' THEN 1 ELSE NULL END) AS 故障,
SUM(CASE WHEN 状态='0003' THEN 1 ELSE NULL END) AS 备用,
SUM(CASE WHEN 状态='0004' THEN 1 ELSE NULL END) AS 报废,
SUM(CASE WHEN 状态='0005' THEN 1 ELSE NULL END) AS 丢失
FROM #T GROUP BY 手机,厂商
) A