我的SQL水平有限，最近需要查詢資料庫，無奈自己嘗試了很多方法還是得不到想要的結果，在此求助大家。
我有一個要查詢的表X，內容如下：
ID NAME CLASS AREA
0001 甲 A1 100
0002 甲 A2 200
0003 丙 A1 100
0004 已 B1 400
0005 丙 B2 300
我想要在NAME相同時，如果同時存在A1和A2，則保留A1行，同時對AREA進行相加匯總，結果如下：
ID NAME CLASS AREA
0001 甲 A1 300
0003 丙 A1 100
0004 已 B1 400
0005 丙 B2 300
uj5u.com熱心網友回復：
CREATE TABLE #A
(
ID VARCHAR(20),
NAME VARCHAR(20),
CLASS VARCHAR(20),
AREA INT
)
INSERT INTO #A VALUES ('0001','甲','A1',100)
INSERT INTO #A VALUES ('0002','甲','A2',200)
INSERT INTO #A VALUES ('0003','丙','A1',100)
INSERT INTO #A VALUES ('0004','已','B1',400)
INSERT INTO #A VALUES ('0005','丙','B2',300)
SELECT A.ID,A.NAME,A.CLASS,A.AREA+ISNULL((SELECT B.AREA FROM #A B WHERE B.CLASS = 'A2' AND B.NAME = A.NAME),0) FROM #A A WHERE A.CLASS != 'A2'
DROP TABLE #A
uj5u.com熱心網友回復：
-- 湊個熱鬧
select ID NAME, case CLASS when 'A3' then 'A1' else CLASS end CLASS, sum(area)
from x
group by ID NAME, case CLASS when 'A3' then 'A1' else CLASS end
uj5u.com熱心網友回復：
CREATE TABLE #T
(
ID VARCHAR(10), NAME VARCHAR(10), CLASS VARCHAR(10), AREA INT
)
INSERT INTO #t VALUES('0001', '甲', 'A1', 100)
INSERT INTO #t VALUES('0002', '甲', 'A2', 200)
INSERT INTO #t VALUES('0003', '丙', 'A1', 100)
INSERT INTO #t VALUES('0004', '已', 'B1', 400)
INSERT INTO #t VALUES('0005', '丙', 'B2', 300)
INSERT INTO #t VALUES('0006', '丁', 'A2', 700)
SELECT MIN(id) AS id,name,CLASS,SUM(AREA) AS AREA FROM
(
SELECT ID,NAME,AREA ,CASE WHEN CLASS IN ('A1','A2') THEN 'A1' ELSE CLASS END AS CLASS FROM #T
) a
GROUP BY name,CLASS
ORDER BY id
uj5u.com熱心網友回復：
不行，如果有兩個甲A2這樣就會報錯，select那句要改成
SELECT A.ID , A.NAME , A.CLASS , A.AREA +
ISNULL((SELECT SUM(B.AREA) FROM #A B WHERE B.CLASS = 'A2' AND B.NAME = A.NAME),0)
FROM #A A WHERE A.CLASS != 'A2'
uj5u.com熱心網友回復：
SELECT MIN(A.ID) ID,A.NAME,MIN(A.CLASS) CLASS,SUM(A.AREA) AREA FROM #A A WHERE A.CLASS LIKE 'A%' GROUP BY A.NAME
UNION ALL
SELECT * FROM #A A WHERE A.CLASS NOT LIKE 'A%'
被#3提醒了，我那樣寫會直接過濾掉所有A2
uj5u.com熱心網友回復：
這么快就發現了
uj5u.com熱心網友回復：
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(
ID VARCHAR(20),
NAME VARCHAR(20),
CLASS VARCHAR(20),
AREA INT
)
INSERT INTO #T VALUES ('0001','甲','A1',100)
INSERT INTO #T VALUES ('0002','甲','A2',200)
INSERT INTO #T VALUES ('0003','丙','A2',100)
INSERT INTO #T VALUES ('0004','已','A2',400)
INSERT INTO #T VALUES ('0005','丙','A1',300)
INSERT INTO #T VALUES ('0006','丙','A3',300)
INSERT INTO #T VALUES ('0007','已','A2',400)
GO
SELECT ID,NAME,CLASS,(SELECT SUM(AREA) FROM #T WHERE NAME=A.NAME AND CLASS IN ('A1','A2')) AS AREA
FROM #T A
WHERE EXISTS (SELECT 1 FROM #T WHERE A.NAME=NAME AND CLASS='A2' AND ID<>A.ID)
AND CLASS='A1'
UNION ALL
SELECT ID,NAME,CLASS,AREA
FROM #T B
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE NAME=B.NAME AND CLASS IN ('A1','A2') AND CLASS<>B.CLASS)
AND CLASS IN ('A1','A2')
UNION ALL
SELECT ID,NAME,CLASS,AREA
FROM #T
WHERE CLASS NOT IN ('A1','A2')
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/238832.html
標籤：應用實例
上一篇：求指教