全國最多中醫師線上諮詢網站-台灣中醫網
發文 回覆 瀏覽次數:1516
推到 Plurk!
推到 Facebook!

請教一個sql語法

答題得分者是:GrandRURU
P.D.
版主


發表:571
回覆:3884
積分:3672
註冊:2006-10-31

發送簡訊給我
#1 引用回覆 回覆 發表時間:2015-07-14 14:07:04 IP:118.169.xxx.xxx 未訂閱
請問各位!
我苦思好久找不到解答
有兩張 table, 分別為 A, B
A 的欄位有
DATE, NO, DEPTNO, TOTALPRICE
B 的欄位有
DATE, NO, DEPTNO, PARTNO, UNITPRICE, COUNT, TOTAL
案例
A 與 B 的關聯是 DATE, NO, PARTNO
A 有三筆記錄
20140305, 001, 'A1', 1000
20140305, 002, 'A1', 100
20140305, 002, 'A2', 500

B 有六筆記錄
20140305, 001, 'A1', S001, 100, 2 , 200
20140305, 001, 'A1', S002, 100, 3, 300
20140305, 001, 'A1', S003, 100, 4, 400
20140305, 001, 'A1', S004, 100, 1, 100
20140305, 002, 'A1', S005, 100, 1, 100
20140305, 002, 'A2', P001, 500, 1, 500

現今我想以一個SQL 統計出 20140305, deptno='A1' 的統計記錄
DATE, DEPTNO, TOTALPRICE, COUNTS
20140305, 'A1', 1100, 11
所以我下了
select a.DATE, a.NO, sum(a.TOTALPRICE), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO

但結果出來是是這樣
DATE, DEPTNO, TOTALPRICE, COUNTS
20140305, 'A1', 4100, 11
數量是對的, 但金額是不對的
原因是如果調出結果記錄來看是這樣的f
select a.DATE, a.NO, a.TOTALPRICE, b.COUNT
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and.DEPTNO='A1'

DATE, NO, DEPTNO, TOTALPRICE, COUNTS
20140305, 001, 'A1', 1000, 2
20140305, 001, 'A1', 1000, 3
20140305, 001, 'A1', 1000, 4
20140305, 001, 'A1', 1000, 1
20140305, 002, 'A1', 100, 1
原因是它把 A.TOTALPRICE 加了4次(因為有4筆), 而這4筆也是B表的4筆
不知道SQL要怎麼下才能達到一次檢索一筆, 金額及數量都正確結果
20140305, A1, 1100, 11

我現在是分段寫統計A 再統計 B, 再把兩者放在 StringGrid 表現出來, 但效能不好(假設我要分析一年的話)
而我也不能這麼下, 當然以本例來看, 確實可以達到算出 totalprice= 1100,
但 a.TOTALPRICE 不一定會等於 sum(b.TOTAL), 有可能使用者會有優惠價, 造成 每筆單筆與實際的總價有差別
select a.DATE, a.NO, sum(b.TOTAL), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO
編輯記錄
P.D. 重新編輯於 2015-07-14 14:11:03, 註解 無‧
P.D. 重新編輯於 2015-07-14 14:14:12, 註解 無‧
GrandRURU
站務副站長


發表:234
回覆:1654
積分:1752
註冊:2005-06-21

發送簡訊給我
#2 引用回覆 回覆 發表時間:2015-07-14 16:11:46 IP:59.120.xxx.xxx 未訂閱
JOIN 是只要符合的資料都會帶出來,故會帶出四筆1000,按PD大的SQL,跑出4000是正確的結果 (笑)
要一格左邊加總,一格右邊加總,我想不出JOIN可以解的方法

所以我寫成如下的 SQL:

[code sql]
select A.DATE, A.NO, A.TP, B.CT
from (
select tta.date, tta.no, SUM(tta.TOTALPRICE) TP
from TTA
where tta.DATE='20140305' and tta.DEPTNO='A1'
GROUP BY tta.DATE, tta.no) A, (
select ttb.DATE, ttb.NO, sum(ttb.COUNT) CT
from ttB
where ttB.DATE='20140305' and ttB.DEPTNO='A1'
group by ttB.DATE, ttB.NO) B
WHERE A.date=b.date and a.no=b.NO
[/code]
P.D.
版主


發表:571
回覆:3884
積分:3672
註冊:2006-10-31

發送簡訊給我
#3 引用回覆 回覆 發表時間:2015-07-14 23:27:50 IP:118.169.xxx.xxx 未訂閱
謝謝 RuRu 兄指點, 我試了一下這段語法, 單一日, 單一deptno的確可以做到我要的結果,
想不到sql還能這樣寫, 真是受教了,
但如果我要分析出一個月中每一天左邊的總金額 與右邊的單日數量
這段SQL我目前看只能用迴圈方式分別計算30次, 如果客戶要分析3個月, 就要跑90次
不知道有沒有更好的方法, 一次就能算出來日期區間的結果
===================引 用 GrandRURU 文 章===================
JOIN 是只要符合的資料都會帶出來,故會帶出四筆1000,按PD大的SQL,跑出4000是正確的結果 (笑)
要一格左邊加總,一格右邊加總,我想不出JOIN可以解的方法

所以我寫成如下的 SQL:

[code sql]
select A.DATE, A.NO, A.TP, B.CT
from (
select tta.date, tta.no, SUM(tta.TOTALPRICE) TP
from TTA
where tta.DATE='20140305' and tta.DEPTNO='A1'
GROUP BY tta.DATE, tta.no) A, (
select ttb.DATE, ttb.NO, sum(ttb.COUNT) CT
from ttB
where ttB.DATE='20140305' and ttB.DEPTNO='A1'
group by ttB.DATE, ttB.NO) B
WHERE A.date=b.date and a.no=b.NO
[/code]
P.D.
版主


發表:571
回覆:3884
積分:3672
註冊:2006-10-31

發送簡訊給我
#4 引用回覆 回覆 發表時間:2015-07-14 23:34:03 IP:118.169.xxx.xxx 未訂閱
發帖後我再試了一下, 找到了解法, 我把使用的標準sql列出來, (上面是為了方便舉例用的)

select
a.FORMDATE, a.ARS, a.CASH, a.VISA, a.DEPOSIT, a.PAYED, b.COUNTS
from
(select
DP000.FORMDATE, sum(DP000.ARS) as ARS, sum(DP000.CASH) as CASH, sum(DP000.VISA) as VISA,
sum(DP000.DEPOSIT) as DEPOSIT, sum(DP000.PAYED) as PAYED
from DP000
where
DP000.FORMTYPE='1' and DP000.ISCONFIRM='Y' and DP000.FORMDATE>= '20140301' and
DP000.FORMDATE<= '20140305' and DP000.DEPTNO= 'E'
group by DP000.FORMDATE
) a ,
(select
DP001.FORMDATE, sum(DP001.COUNTS) as COUNTS
from DP001
where
DP001.FORMTYPE='1' and DP001.ISCONFIRM='Y' and DP001.FORMDATE>='20140301' and DP001.FORMDATE<='20140305'
and DP001.DEPTNO='E'
group by DP001.FORMDATE
) b
where a.FORMDATE=b.FORMDATE
GrandRURU
站務副站長


發表:234
回覆:1654
積分:1752
註冊:2005-06-21

發送簡訊給我
#5 引用回覆 回覆 發表時間:2015-07-15 09:09:29 IP:59.120.xxx.xxx 未訂閱
PD大,您突破盲腸了!
===================引 用 P.D. 文 章===================
發帖後我再試了一下, 找到了解法, 我把使用的標準sql列出來, (上面是為了方便舉例用的)
... 43 ...
老大仔
尊榮會員


發表:77
回覆:835
積分:1082
註冊:2006-07-06

發送簡訊給我
#6 引用回覆 回覆 發表時間:2015-07-15 10:03:31 IP:210.61.xxx.xxx 未訂閱
假如把NO暫先視為單號的話,
那麼這段SQL會有個問題:
select a.DATE, a.NO, sum(a.TOTALPRICE), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO
PD大的group by 是用A的日期 單號
那麼勢必會被拆成兩筆?
20140305, 001, 'A1', 1000, 10
20140305, 002, 'A1', 100, 1

===================引 用 P.D. 文 章===================
請問各位!
我苦思好久找不到解答
有兩張 table, 分別為 A, B
A 的欄位有
DATE, NO, DEPTNO, TOTALPRICE
B 的欄位有
DATE, NO, DEPTNO, PARTNO, UNITPRICE, COUNT, TOTAL
案例
A 與 B 的關聯是 DATE, NO, PARTNO
A 有三筆記錄
20140305, 001, 'A1', 1000
20140305, 002, 'A1', 100
20140305, 002, 'A2', 500

B 有六筆記錄
20140305, 001, 'A1', S001, 100, 2 , 200
20140305, 001, 'A1', S002, 100, 3, 300
20140305, 001, 'A1', S003, 100, 4, 400
20140305, 001, 'A1', S004, 100, 1, 100
20140305, 002, 'A1', S005, 100, 1, 100
20140305, 002, 'A2', P001, 500, 1, 500

現今我想以一個SQL 統計出 20140305, deptno='A1' 的統計記錄
DATE, DEPTNO, TOTALPRICE, COUNTS
20140305, 'A1', 1100, 11
所以我下了
select a.DATE, a.NO, sum(a.TOTALPRICE), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO

但結果出來是是這樣
DATE, DEPTNO, TOTALPRICE, COUNTS
20140305, 'A1', 4100, 11
數量是對的, 但金額是不對的
原因是如果調出結果記錄來看是這樣的f
select a.DATE, a.NO, a.TOTALPRICE, b.COUNT
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and.DEPTNO='A1'

DATE, NO, DEPTNO, TOTALPRICE, COUNTS
20140305, 001, 'A1', 1000, 2
20140305, 001, 'A1', 1000, 3
20140305, 001, 'A1', 1000, 4
20140305, 001, 'A1', 1000, 1
20140305, 002, 'A1', 100, 1
原因是它把 A.TOTALPRICE 加了4次(因為有4筆), 而這4筆也是B表的4筆
不知道SQL要怎麼下才能達到一次檢索一筆, 金額及數量都正確結果
20140305, A1, 1100, 11

我現在是分段寫統計A 再統計 B, 再把兩者放在 StringGrid 表現出來, 但效能不好(假設我要分析一年的話)
而我也不能這麼下, 當然以本例來看, 確實可以達到算出 totalprice= 1100,
但 a.TOTALPRICE 不一定會等於 sum(b.TOTAL), 有可能使用者會有優惠價, 造成 每筆單筆與實際的總價有差別
select a.DATE, a.NO, sum(b.TOTAL), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO
P.D.
版主


發表:571
回覆:3884
積分:3672
註冊:2006-10-31

發送簡訊給我
#7 引用回覆 回覆 發表時間:2015-07-17 09:59:39 IP:220.136.xxx.xxx 未訂閱
感謝老大仔的說明,
我最上面po的語法, 我知道是join方式, 會列出所有符合的記
目前以RuRu的做法已經可以解決手上的難題了, 速度也比我之前分兩個Query連動運算快了好幾倍,
RuRu兄提供的做法是我之前不知道的語法還可以這麼下, sql 真是偉大
我自己測試分析一整年的記錄大約花30秒(首次, 包含把記錄回填到StringGrid), 不過我的電腦是i7的
===================引 用 老大仔 文 章===================
假如把NO暫先視為單號的話,
那麼這段SQL會有個問題:
select a.DATE, a.NO, sum(a.TOTALPRICE), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO
PD大的group by 是用A的日期 單號
那麼勢必會被拆成兩筆?
20140305, 001, 'A1', 1000, 10
20140305, 002, 'A1', 100, 1
it1506
初階會員


發表:30
回覆:83
積分:47
註冊:2011-02-16

發送簡訊給我
#8 引用回覆 回覆 發表時間:2017-09-14 13:53:08 IP:59.120.xxx.xxx 未訂閱
select a.DATE,  a.NO, sum(a.TOTALPRICE),  b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO

改成

[code sql]
select a.DATE, a.NO, sum(a.TOTALPRICE) as totalprice,
(select sum(b.COUNT) from B b where a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) as Bcount
from A a
where a.DATE='20140305' and a.DEPTNO='A1'
group by a.DEPTNO,a.DATE, a.NO
[/code]

再針對結果去做處理就行了! 外層再select sum....

另一個方式是用假欄位 , union起來當做一個table 外層再select sum
[code sql]

select sum(totalprice) ,sum([count]) from
(
select date ,[no] ,totalprice,0 as [count] from a where DATE='20140305' and DEPTNO='A1'
union
select date ,[no] ,0 as totalprice, [count] from b where DATE='20140305' and DEPTNO='A1'
) as t

[/code]



===================引 用 P.D. 文 章===================
請問各位!
我苦思好久找不到解答
有兩張 table, 分別為 A, B
A 的欄位有
DATE, NO, DEPTNO, TOTALPRICE
B 的欄位有
DATE, NO, DEPTNO, PARTNO, UNITPRICE, COUNT, TOTAL
案例
A 與 B 的關聯是 DATE, NO, PARTNO
A 有三筆記錄
20140305, 001, 'A1', 1000
20140305, 002, 'A1', 100
20140305, 002, 'A2', 500

B 有六筆記錄
20140305, 001, 'A1', S001, 100, 2 , 200
20140305, 001, 'A1', S002, 100, 3, 300
20140305, 001, 'A1', S003, 100, 4, 400
20140305, 001, 'A1', S004, 100, 1, 100
20140305, 002, 'A1', S005, 100, 1, 100
20140305, 002, 'A2', P001, 500, 1, 500

現今我想以一個SQL 統計出 20140305, deptno='A1' 的統計記錄
DATE, DEPTNO, TOTALPRICE, COUNTS
20140305, 'A1', 1100, 11
所以我下了
select a.DATE, a.NO, sum(a.TOTALPRICE), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO

但結果出來是是這樣
DATE, DEPTNO, TOTALPRICE, COUNTS
20140305, 'A1', 4100, 11
數量是對的, 但金額是不對的
原因是如果調出結果記錄來看是這樣的f
select a.DATE, a.NO, a.TOTALPRICE, b.COUNT
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and.DEPTNO='A1'

DATE, NO, DEPTNO, TOTALPRICE, COUNTS
20140305, 001, 'A1', 1000, 2
20140305, 001, 'A1', 1000, 3
20140305, 001, 'A1', 1000, 4
20140305, 001, 'A1', 1000, 1
20140305, 002, 'A1', 100, 1
原因是它把 A.TOTALPRICE 加了4次(因為有4筆), 而這4筆也是B表的4筆
不知道SQL要怎麼下才能達到一次檢索一筆, 金額及數量都正確結果
20140305, A1, 1100, 11

我現在是分段寫統計A 再統計 B, 再把兩者放在 StringGrid 表現出來, 但效能不好(假設我要分析一年的話)
而我也不能這麼下, 當然以本例來看, 確實可以達到算出 totalprice= 1100,
但 a.TOTALPRICE 不一定會等於 sum(b.TOTAL), 有可能使用者會有優惠價, 造成 每筆單筆與實際的總價有差別
select a.DATE, a.NO, sum(b.TOTAL), b.sum(b.COUNT)
from A, B
where (a.DATE= b.DATE and a.NO=b.NO and a.DEPTNO=b.DEPTNO) and
a.DATE='20140305' and a.DEPTNO='A1'
group by a.DATE, a.NO
編輯記錄
it1506 重新編輯於 2017-09-14 14:17:08, 註解 無‧
系統時間:2017-11-19 22:14:48
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!