表a，表b，表c通過關鍵字JID關聯，資料以表a為準，想按月或年查詢求和。JNo的格式是SX2001-001，其中20代表年份，01是月份，最后3為是順序號
表a：
JID JNo
1 SX2001-001
2 SX2001-002
3 SX2001-003
4 SX2002-001
表 K：
JID Kaizhi
1 10.00
2 15.00
2 7.00
4 30.00
表 S：
JID Shouru
1 50
2 50
2 10
3 50
4 70
SELECT a.JNo,SUM(ISNULL(SR,0)) AS 收入,SUM(ISNULL(CB,0)) AS 成本,毛利=SUM(ISNULL(SR,0))-SUM(ISNULL(CB,0)) FROM a
LEFT JOIN (SELECT JID,CB=SUM(Kaizhi) FROM K GROUP BY JID) b
ON a.JID=b.JID
LEFT JOIN (SELECT JID,SR=SUM(Shouru) FROM S GROUP BY JID) c
ON c.JID=a.JID
WHERE a.JNo LIKE 'SX2001-%' GROUP BY a.JNo,CB,SR
按每一個編號查詢沒問題，如何實作按月或年查詢求和？ 也是條件中的 a.JNo LIKE 'SX2001-%' 改為a.JNo LIKE 'SX__01-%' 或 a.JNo LIKE 'SX20__-%'
uj5u.com熱心網友回復：
substring(a.JNO,3,2)='20'substring(a.JNO,5,2)='01'
這樣？
uj5u.com熱心網友回復：
不行，還是原來的結果，一條一條顯示的uj5u.com熱心網友回復：
GROUP BY 也要改啊uj5u.com熱心網友回復：
CREATE TABLE #A(
JID INT,
JNO VARCHAR(20)
)
CREATE TABLE #K(
JID INT,
KAIZHI INT
)
CREATE TABLE #S(
JID INT,
SHOURU INT
)
INSERT INTO #A VALUES (1,'SX2001-001'),(2,'SX2001-002'),(3,'SX2001-003'),(4,'SX2002-001')
INSERT INTO #K VALUES (1,10),(2,15),(2,7),(4,30)
INSERT INTO #S VALUES (1,50),(2,50),(2,10),(3,50),(4,70)
SELECT a.JNo,SUM(ISNULL(SR,0)) AS 收入,SUM(ISNULL(CB,0)) AS 成本,毛利=SUM(ISNULL(SR,0))-SUM(ISNULL(CB,0)) FROM #A a
LEFT JOIN (SELECT JID,CB=SUM(Kaizhi) FROM #K K GROUP BY JID) b
ON a.JID=b.JID LEFT JOIN (SELECT JID,SR=SUM(Shouru) FROM #S S GROUP BY JID) c
ON c.JID=a.JID WHERE a.JNo LIKE 'SX2001-%' GROUP BY a.JNo,CB,SR
SELECT substring(a.JNO,3,2),SUM(ISNULL(SR,0)) AS 收入,SUM(ISNULL(CB,0)) AS 成本,毛利=SUM(ISNULL(SR,0))-SUM(ISNULL(CB,0)) FROM #A a
LEFT JOIN (SELECT JID,CB=SUM(Kaizhi) FROM #K K GROUP BY JID) b
ON a.JID=b.JID LEFT JOIN (SELECT JID,SR=SUM(Shouru) FROM #S S GROUP BY JID) c
ON c.JID=a.JID WHERE substring(a.JNO,3,2)='20' GROUP BY substring(a.JNO,3,2)
SELECT substring(a.JNO,5,2),SUM(ISNULL(SR,0)) AS 收入,SUM(ISNULL(CB,0)) AS 成本,毛利=SUM(ISNULL(SR,0))-SUM(ISNULL(CB,0)) FROM #A a
LEFT JOIN (SELECT JID,CB=SUM(Kaizhi) FROM #K K GROUP BY JID) b
ON a.JID=b.JID LEFT JOIN (SELECT JID,SR=SUM(Shouru) FROM #S S GROUP BY JID) c
ON c.JID=a.JID WHERE substring(a.JNO,5,2)='01' GROUP BY substring(a.JNO,5,2)
DROP TABLE #A,#K,#S
不知道你要的是不是這個效果
uj5u.com熱心網友回復：
select a.jid 編號,sum(k.kaizhi) 開支合計,sum(s.shouru) 收入合計 from tablea a left join tablek k on a.jid=k.jid left join tables s on a.jid=s.jidgroup by a.jid
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/246018.html
標籤：基礎類