0

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
``````

loveotgi iT邦新手 5 級 ‧ 2018-06-25 13:41:36 檢舉

4 個回答

0
fuzzylee1688
iT邦新手 1 級 ‧ 2018-06-25 10:32:49

loveotgi iT邦新手 5 級 ‧ 2018-06-25 13:41:59 檢舉

0

iT邦大師 1 級 ‧ 2018-06-25 12:11:32

UNION你覺得如何?

loveotgi iT邦新手 5 級 ‧ 2018-06-25 13:42:08 檢舉

0
richardsuma
iT邦大師 1 級 ‧ 2018-06-25 14:54:00
1. 先將 單別、品號、部門 資料，利用 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' 設定為 北二區

2. 透過 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 ....
)

3. 再用一個 SELECT 將上面的 SALES 與 SELLBACK JOIN 並做運算就可以達到你想要的資料與格式。

loveotgi iT邦新手 5 級 ‧ 2018-08-22 16:11:49 檢舉

2

iT邦研究生 1 級 ‧ 2018-06-26 02:38:48

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    |
``````
loveotgi iT邦新手 5 級 ‧ 2018-08-22 16:12:40 檢舉