請問各位!
我苦思好久找不到解答
有兩張 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',1000, 2
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

編輯記錄

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]

謝謝 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]

發帖後我再試了一下, 找到了解法, 我把使用的標準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

PD大，您突破盲腸了！
===================引 用 P.D. 文 章===================
發帖後我再試了一下, 找到了解法, 我把使用的標準sql列出來, (上面是為了方便舉例用的)
... 43 ...

假如把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',1000, 2
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

感謝老大仔的說明,
我最上面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

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',1000, 2
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

編輯記錄