iT邦幫忙

0

各位前輩 請教一下
我SQL的語法,從資料庫裡撈出來的資料排列像下圖這樣

(圖一)

https://ithelp.ithome.com.tw/upload/images/20180625/20061726aswZQgIw9d.jpg

可是 我想要的是 像下圖這樣
(圖二)
https://ithelp.ithome.com.tw/upload/images/20180625/20061726Zlq8wmmu7t.jpg

我的語法是

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

請問 我要怎麼寫我的語法 才能讓排列方式 像(圖二)一樣

棉花 iT邦新手 4 級 ‧ 2018-06-25 12:04:51 檢舉
或許能用UNPIVOT
loveotgi iT邦新手 5 級 ‧ 2018-06-25 13:41:36 檢舉
謝謝您
0
fuzzylee1688
iT邦新手 3 級 ‧ 2018-06-25 10:32:49
最佳解答

計算邏輯不變, 只是計算欄位變少(改以分區計算), 先落地為多working table, 最後再用union all方式合併 即可.

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邦大師 3 級 ‧ 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 並做運算就可以達到你想要的資料與格式。

對了,另外一個方法用 Qlikview BI 單機版,不用錢的,好用,可以試試看。
不需要先做資料整理,可以直接在腳本上做,或是利用一個 excel 做對照表,
再到腳本做 join 就可以了。

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

謝謝

2
dog830228
iT邦研究生 4 級 ‧ 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 檢舉

謝謝

我要發表回答

立即登入回答