遇到一個SQL的問題想請
我有3個SELECT
SELECT SUM(欄位A) FROM TABLE 1 WHERE DAYA BETWEEN day1 AND day2
SELECT SUM(欄位B) FROM TABLE 1 WHERE DAYB BETWEEN day1 AND day2
SELECT SUM(欄位C) FROM TABLE 1 WHERE DAYC BETWEEN day1 AND day2
主要問題是我需要將SUM之後的欄位A,B,C做一個SUM(ROUND((A-B+C),0))的動作
不知道怎麼組...想請教一下各位大大 有勞了
如果有表達不清楚的地方還請提指正
P.S我是用SQL SERVER 2012
抱歉忘記條件有不一樣的了...已修正
已邀請的邦友 {{ invite_list.length }}/5
WITH 欄位A_LIST AS (
SELECT DAYA AS '1' ,
SUM(欄位A) AS SUM_欄位A
FROM TABLE 1
WHERE DAYA BETWEEN day1 AND day2
),
欄位B_LIST AS (
SELECT DAYB AS '1' ,
SUM(欄位B) AS SUM_欄位B
FROM TABLE 1
WHERE DAYB BETWEEN day1 AND day2
),
欄位C_LIST AS (
SELECT DAYC AS '1' ,
SUM(欄位C) AS SUM_欄位C
FROM TABLE 1
WHERE DAYC BETWEEN day1 AND day2
),
TOTAL AS (
SELECT ROUND(SUM_欄位A-SUM_欄位B+SUM_欄位C),0) AS ROUND_CHECK
FROM 欄位A_LIST
LEFT JOIN 欄位B_LIST ON 欄位B_LIST.DAYB=欄位A_LIST.DAYA
LEFT JOIN 欄位C_LIST ON 欄位C_LIST.DAYC=欄位A_LIST.DAYA
)
SELECT *
FROM TOTAL
之前有誤,已重新修正
恩恩~感覺蠻接近的,只是我想要的是顯示A欄為全部的值然後如果B跟C欄位有值我才去對A欄位做變化,這部份一開始沒表達清楚不好意思,可以請教一下這一段是要用CASE去做嗎?
TOTAL AS 這部份要改為
TOTAL AS (
SELECT CASE
WHEN SUM_欄位B<> NULL AND SUM_欄位C<> NULL
THEN ROUND(SUM_欄位A-SUM_欄位B+SUM_欄位C),0)
ELSE SUM_欄位A
END AS ROUND_CHECK
FROM 欄位A_LIST
LEFT JOIN 欄位B_LIST ON 欄位B_LIST.DAYB=欄位A_LIST.DAYA
LEFT JOIN 欄位C_LIST ON 欄位C_LIST.DAYC=欄位A_LIST.DAYA
)
恩恩~感謝你的回答~
同一個TABLE ,相同的日期區間.
SELECT ROUND(SUM(欄位A+欄位B+欄位C),0)+ FROM TABLE 1 WHERE BETWEEN day1 AND day2
有試過這樣的語法是可行的，
不過你原本的SQL怪怪的，
為什麼TABLE 跟 1 是分開的，
像下面的 TABLE1 是資料表名稱
SELECT ROUND((sum1-sum2+sum3),0) FROM (
SELECT
(SELECT SUM(欄位A) FROM TABLE1 WHERE DAYA BETWEEN day1 AND day2) AS sum1,
(SELECT SUM(欄位B) FROM TABLE1 WHERE DAYB BETWEEN day1 AND day2) AS sum2,
(SELECT SUM(欄位C) FROM TABLE1 WHERE DAYC BETWEEN day1 AND day2) AS sum3
) AS root
感謝您的回答...我試過以後會顯示
子查詢傳回不只 1 個值。這種狀況在子查詢之後有 =、!=、<、<=、>、>= 或是子查詢做為運算式使用時是不允許的。
感覺應該是我日期區間的資料量很多的關係...子查詢是不是一次只能有一筆資料?
還有TABLE是我自己打太開了XDD
的確只能有一筆資料，可是你不是已經SUM了嗎？為什麼還會有多筆？？
在最上面的SELECT會有多筆的計算
SELECT ROUND(
(SELECT SUM(欄位A) FROM TABLE 1 WHERE DAYA BETWEEN day1 AND day2) -
(SELECT SUM(欄位B) FROM TABLE 1 WHERE DAYB BETWEEN day1 AND day2) +
(SELECT SUM(欄位C) FROM TABLE 1 WHERE DAYC BETWEEN day1 AND day2)
, 0)
iThome鐵人賽
看影片追技術