表1：
Name Count
AAA 2
BBB 3
AAA -1
CCC 2
BBB -1
CCC 1
如何用sql得下面結果：
Name Count
AAA 1
BBB 1
BBB 1
CCC 1
CCC 1
CCC 1
要求Count列都是1，Count的合并值轉化成行
uj5u.com熱心網友回復：
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(NAME VARCHAR(10),
COUNT INT)
INSERT INTO #T
SELECT 'AAA',2 UNION ALL
SELECT 'BBB',3 UNION ALL
SELECT 'AAA',-1 UNION ALL
SELECT 'CCC',2 UNION ALL
SELECT 'BBB',-1 UNION ALL
SELECT 'CCC',1
GO
SELECT A.NAME,1
FROM
(SELECT NAME,SUM(COUNT) AS QTY FROM #T GROUP BY NAME) AS A
JOIN master.dbo.spt_values B ON A.QTY>=B.number
WHERE TYPE='P' AND number>0
uj5u.com熱心網友回復：
要表B是干什么的
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/226560.html
標籤：應用實例
下一篇：MySQL 高性能優化規范建議