iT邦幫忙

0

SQL 查詢問題

sql

各位大大好,
我有兩個資料表
客戶資料(客戶編號,客戶名稱,信用額度)
訂單系統(訂單編號,客戶名稱,交易日期,數量,單價)

希望顯示客戶名稱,交易金額,信用度比率
1.交易金額=數量X單價
2.信用度比率=每位客戶的交易金額除以「客戶資料」資料檔的「信用額度」
我執行以下SQL
SELECT 客戶名稱,
SUM(數量單價) AS 交易金額,
信用度比率=SUM(數量
單價)/(SELECT 信用額度 FROM 客戶資料)
FROM 訂單系統
GROUP BY 客戶名稱
出現:
子查詢傳回不只 1 個值。這種狀況在子查詢之後有 =、!=、<、<=、>、>= 或是子查詢做為運算式使用時是不允許的。

請問該如何修正?謝謝。

伍德 iT邦新手 5 級 ‧ 2019-03-06 14:53:59 檢舉
沒說明兩個table的關係
真心建議不要把select子句又寫在select主句裡面,類似你提問的這種,就是乖乖先做完join,再select出來就好。
提供參考:https://ithelp.ithome.com.tw/articles/10203410
二、子句,只用在from
偶爾會看到一些人的寫法,把子句(select ... from ... where)寫在自己語法的where後面,或是select後面,小馬我非常不喜歡這樣,這會讓自己語法架構非常混亂,也會讓人不易理解、不易接手、不易解讀。
我個人常用的方式,只會把子句放在from之後,這是為了讓所有人,清楚理解自己每一層的內容,從最裡層,瞭解後,當作 temp table 或暫存的概念,再往後針對這個暫存做其他處理。一層一層如洋蔥般的包覆,架構完整且邏輯清楚。
gaga0826 iT邦新手 5 級 ‧ 2019-03-08 10:52:15 檢舉
了解,感謝大大
0
純真的人
iT邦大師 1 級 ‧ 2019-03-06 16:15:36

恩...

SELECT 客戶名稱,
SUM(數量單價) AS 交易金額,
信用度比率 = SUM(數量單價) / (SELECT top 1 信用額度 FROM 客戶資料)
FROM 訂單系統
GROUP BY 客戶名稱

或者...

SELECT 客戶名稱,
SUM(數量單價) AS 交易金額,
信用度比率 = SUM(數量單價) / (SELECT top 1 信用額度 FROM 客戶資料 WHERE 客戶資料.客戶名稱 = 訂單系統.客戶名稱)
FROM 訂單系統
GROUP BY 客戶名稱
0
做工仔人!
iT邦大師 1 級 ‧ 2019-03-06 17:20:45

SELECT A.客戶名稱 客戶名稱,
A.交易金額 交易金額,(A.交易金額 / B.信用額度)*100 信用度比率
FROM
(SELECT 客戶名稱,
SUM(數量單價) AS 交易金額,
FROM 訂單系統
GROUP BY 客戶名稱) A , 客戶資料 B
WHERE A.客戶名稱=B.客戶名稱

0
paicheng0111
iT邦高手 1 級 ‧ 2019-03-06 23:32:31
SELECT 
    A.客戶名稱,
    A.交易金額,
    A.交易金額/B.信用額度 AS 信用度比率
FROM
    (SELECT 客戶名稱, SUM(數量*單價) AS 交易金額 
    FROM 訂單系統 GROUP BY 客戶名稱
    ) A
    LEFT JOIN 客戶資料 B ON A.客戶名稱 = B.客戶名稱
0
thwu
iT邦新手 5 級 ‧ 2019-03-07 10:29:14
SELECT 客戶名稱,
SUM(數量單價) AS 交易金額,
信用度比率=SUM(數量單價)/(SELECT 信用額度 FROM 客戶資料)
FROM 訂單系統
GROUP BY 客戶名稱

錯誤提示:子查詢傳回不只 1 個值。這種狀況在子查詢之後有 =、!=、<、<=、>、>= 或是子查詢做為運算式使用時是不允許的。

在這段SQL中,子查詢是指你放在 SELECT 中的

SELECT 信用額度 FROM 客戶資料

出錯的原因在於這個子查詢回傳了超過1筆的資料,導致無法計算下列式子。
(因為分母有多個值,無法讓SQL知道要怎麼除)

SUM(數量單價)/(SELECT 信用額度 FROM 客戶資料)

關於這段 SQL,你應該是思考成:自[訂單系統]中計算每個客戶的[總交易金額],並同時列出[信用度比率]。
因此在計算信用度比率時,從[客戶資料]中抓取的信用額度應該要是與訂單系統中該筆資料的客戶相同對吧?

例如訂單系統中會計算出 3 筆客戶(A, B, C)的總交易金額,在計算信用度比率時就會是

  • A的總交易金額/A的信用額度
  • B的總交易金額/B的信用額度
  • C的總交易金額/C的信用額度

而你的寫法則是

  • A的總交易金額/所有客戶信用額度(多筆)
  • B的總交易金額/所有客戶信用額度(多筆)
  • C的總交易金額/所有客戶信用額度(多筆)

所以你的子查詢就是缺少 WHERE 條件

為了讓子查詢能對應該筆訂單系統的客戶,我加了一點小修改。

SELECT 
    so.客戶名稱,
    SUM(so.數量單價) AS '交易金額',
    SUM(so.數量單價)/(SELECT cu.信用額度 FROM 客戶資料 cu WHERE cu.客戶名稱 = so.客戶名稱) AS '信用度比率'
FROM 
    訂單系統 so
GROUP BY 
    so.客戶名稱

最後就如上面各位大大們提醒的,子查詢盡量不要放在 SELECT 或 WHERE 中。
我認為如果習慣了會讓思考變的不彈性,無法容易切換成多表格join的思維,未來在遇到效能問題或大量表格join情況時不容易做事。

gaga0826 iT邦新手 5 級 ‧ 2019-03-08 10:51:17 檢舉

了解,感謝大大

我要發表回答

立即登入回答