各位前輩 請教一下
我SQL的語法,從資料庫裡撈出來的資料排列像下圖這樣
(圖一)
可是 我想要的是 像下圖這樣
(圖二)
我的語法是
SELECT A1.北一區銷貨金額, A1.北一區退貨金額, A1.北一區銷貨淨額, A1.北一區主任獎勵, derivedtbl_1.北二區銷貨金額,
derivedtbl_1.北二區退貨金額, derivedtbl_1.北二區銷貨淨額, derivedtbl_1.北二區主任獎勵
FROM (SELECT '北一區' AS 區別, D1.北一區銷貨金額, D2.北一區退貨金額,
D1.北一區銷貨金額 - ISNULL(D2.北一區退貨金額, 0) AS 北一區銷貨淨額,
(D1.北一區銷貨金額 - ISNULL(D2.北一區退貨金額, 0)) * 0.01 AS 北一區主任獎勵
FROM (SELECT SUM(A.TH013) AS 北一區銷貨金額
FROM dbo.COPTH AS A INNER JOIN
dbo.COPTG AS C ON A.TH001 = C.TG001 AND A.TH002 = C.TG002
WHERE (C.TG003 BETWEEN '20180501' AND '20180531') AND (A.TH020 = 'Y') AND
(A.TH001 IN ('2301', '2302', '2303', '2304', '2308', '2309', '2331', '2332', '2333', '2334',
'2339', '2311', '2312', '2314')) AND (A.TH004 IN ('1500202', '1500212', '1500409',
'1500410', '1500602', '1500603', '1500901', '1500902', '1500903', '1500904', '1501207',
'1501208', '1501401', '1501511', '1501512', '1501703', '1501803', '1501812', '1501902',
'1900801', '1900806', 'X000039')) AND (C.TG005 IN ('AA', 'AA111', 'AA112', 'AA251',
'AA252'))) AS D1 CROSS JOIN
(SELECT SUM(A.TJ012) AS 北一區退貨金額
FROM dbo.COPTJ AS A INNER JOIN
dbo.COPTI AS C ON A.TJ001 = C.TI001 AND A.TJ002 = C.TI002
WHERE (C.TI003 BETWEEN '20180501' AND '20180531') AND (A.TJ021 = 'Y') AND
(A.TJ001 IN ('2401', '2404', '2408', '2409', '2431', '2434', '2439', '2411')) AND
(A.TJ004 IN ('1500202', '1500212', '1500409', '1500410', '1500602', '1500603',
'1500901', '1500902', '1500903', '1500904', '1501207', '1501208', '1501401', '1501511',
'1501512', '1501703', '1501803', '1501812', '1501902', '1900801', '1900806', 'X000039'))
AND (C.TI005 IN ('AA', 'AA111', 'AA112', 'AA251', 'AA252'))) AS D2) AS A1 CROSS JOIN
(SELECT 區別, 北二區銷貨金額, 北二區退貨金額, 北二區銷貨淨額, 北二區主任獎勵
FROM (SELECT '北二區' AS 區別, D1_1.北二區銷貨金額, D2_1.北二區退貨金額,
D1_1.北二區銷貨金額 - ISNULL(D2_1.北二區退貨金額, 0) AS 北二區銷貨淨額,
(D1_1.北二區銷貨金額 - ISNULL(D2_1.北二區退貨金額, 0))
* 0.01 AS 北二區主任獎勵
FROM (SELECT SUM(A.TH013) AS 北二區銷貨金額
FROM dbo.COPTH AS A INNER JOIN
dbo.COPTG AS C ON A.TH001 = C.TG001 AND A.TH002 = C.TG002
WHERE (C.TG003 BETWEEN '20180501' AND '20180531') AND (A.TH020 = 'Y') AND
(A.TH001 IN ('2301', '2302', '2303', '2304', '2308', '2309', '2331', '2332',
'2333', '2334', '2339', '2311', '2312', '2314')) AND (A.TH004 IN ('1500202',
'1500212', '1500409', '1500410', '1500602', '1500603', '1500901',
'1500902', '1500903', '1500904', '1501207', '1501208', '1501401',
'1501511', '1501512', '1501703', '1501803', '1501812', '1501902',
'1900801', '1900806', 'X000039')) AND (C.TG005 IN ('AA211', 'AA212',
'AA221'))) AS D1_1 CROSS JOIN
(SELECT SUM(A.TJ012) AS 北二區退貨金額
FROM dbo.COPTJ AS A INNER JOIN
dbo.COPTI AS C ON A.TJ001 = C.TI001 AND A.TJ002 = C.TI002
WHERE (C.TI003 BETWEEN '20180501' AND '20180531') AND (A.TJ021 = 'Y')
AND (A.TJ001 IN ('2401', '2404', '2408', '2409', '2431', '2434', '2439',
'2411')) AND (A.TJ004 IN ('1500202', '1500212', '1500409', '1500410',
'1500602', '1500603', '1500901', '1500902', '1500903', '1500904',
'1501207', '1501208', '1501401', '1501511', '1501512', '1501703',
'1501803', '1501812', '1501902', '1900801', '1900806', 'X000039')) AND
(C.TI005 IN ('AA211', 'AA212', 'AA221'))) AS D2_1) AS derivedtbl_1_1)
AS derivedtbl_1
請問 我要怎麼寫我的語法 才能讓排列方式 像(圖二)一樣
已邀請的邦友 {{ invite_list.length }}/5
先將 單別、品號、部門 資料，利用 ERP 的備註欄位做為查詢簡化的設定。
單據設定檔(CMSMQ).MQ022 備註欄位
將 '2301', '2302', '2303', '2304', '2308', '2309', '2331', '2332', '2333',
'2334','2339', '2311', '2312', '2314' 設定為 北區銷貨單別。
品號基本檔(INVMB).MB028 備註欄位
將 '1500202', '1500212', '1500409','1500410', '1500602', '1500603', '1500901', '1500902', '1500903', '1500904', '1501207','1501208', '1501401', '1501511',
'1501512', '1501703', '1501803', '1501812', '1501902', '1900801', '1900806',
'X000039' 設定為 北區銷貨品號。
部門資料檔(CMSME).ME003 備註欄位
將 'AA', 'AA111', 'AA112', 'AA251', 'AA252' 設定為 北一區
'AA211', 'AA212', 'AA221' 設定為 北二區
透過 WITH CTE(Common Table Expression) 先將 銷貨與銷退資料個別計算出來。
WITH SALES( DEPT_ID , SALES ) AS
( SELECT ME003 , SUM(TH013)
FROM COPTG, COPTH, CMSME, CMSMQ, INVMB
WHERE TG003 BETWEEN '20180501' AND '20180531'
AND ME003 IN ('北一區','北二區')
AND ME003=TG005
AND MB028='北區銷貨品號'
AND MB028=TH004
AND MQ022='北區銷貨單別'
AND MQ022=TG001
AND TG001=TH001
AND TG002=TH002
GROUP BY ME003
),
SELLBACK (DEPT_ID ,SELLBACK) AS
( SELECT ....
)
再用一個 SELECT 將上面的 SALES 與 SELLBACK JOIN 並做運算就可以達到你想要的資料與格式。
對了，另外一個方法用 Qlikview BI 單機版，不用錢的，好用，可以試試看。
不需要先做資料整理，可以直接在腳本上做，或是利用一個 excel 做對照表，
再到腳本做 join 就可以了。
SQL Server 可以使用 cross apply with values 簡單完成
CREATE TABLE T(
[北一區消貸金額] INT,
[北一區退貨金額] INT,
[北一區銷售淨額] INT,
[北一區主任獎金] INT,
[北二區消貸金額] INT,
[北二區退貨金額] INT,
[北二區銷售淨額] INT,
[北二區主任獎金] INT,
[南一區消貸金額] INT,
[南一區退貨金額] INT,
[南一區銷售淨額] INT,
[南一區主任獎金] INT,
[南二區消貸金額] INT,
[南二區退貨金額] INT,
[南二區銷售淨額] INT,
[南二區主任獎金] INT
);
insert into t values (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);
SELECT tt.*
FROM T
cross apply (
values
([北一區消貸金額],[北一區退貨金額],[北一區銷售淨額],[北一區主任獎金]),
([北二區消貸金額],[北二區退貨金額],[北二區銷售淨額],[北二區主任獎金]),
([南一區消貸金額],[南一區退貨金額],[南一區銷售淨額],[南一區主任獎金]),
([南二區消貸金額],[南二區退貨金額],[南二區銷售淨額],[南二區主任獎金])
)tt([消貸金額],[退貨金額],[銷售淨額],[主任獎金])
sqlfiddle:http://sqlfiddle.com/#!18/a1f23/9
Results:
| 消貸金額 | 退貨金額 | 銷售淨額 | 主任獎金 |
|---------|---------|----------|---------|
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
| 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 |
iThome鐵人賽
看影片追技術