select 'M5000' as DBFNAME, WORKER, sum(LISTFARE) as LISTM, sum(WORKFARE) as WORKM, sum(ACTMONEY) as ACTM, count(ACTMONEY) as FIXTIMES, 0.0 as P1, 0.0 as PARTTIME
from M5000
where ((FIXDATE>='970501') and (FIXDATE<='970510')) and (CONFIRM="Y") and FIXHEAD in ("900")
group by WORKER
union
select 'M5001' as DBFNAME, a.WORKER, 0.0 as LISTM, 0.0 as WORKM, 0.0 as ACTM, 0 as FIXTIMES, sum(b.P1) as P1, sum(b.PARTTIME) as PARTTIME
from M5000 a, M5001 b
where ((FIXDATE>='970501') and (FIXDATE<='970510')) and (CONFIRM="Y") and FIXHEAD in ("900")
and a.FIXHEAD=b.FIXHEAD and a.FIXBODY=b.FIXBODY
group by a.WORKER

這是我的一個sql語法, 得到的結果如圖


請問有沒有辦法把 M5001 中的林, 曹, 盛三人的 P1, PARTTIME 值移到 M5000 記錄之後相同的欄位, 使其變成三筆記錄, 而不是分成兩段,
註: M5000為 MASTER DBF, M5001為 DETAIL DBF, 資料庫格式為 FIREBIRD 1.5X
M5000與M5001之間是利用 FIXHEAD, FIXBODY 兩個欄位做關聯的, 或許有人會想到利用 WORKER 來做KEYFIELD 處理
但很可惜, M5000 有 WORKER 欄位, 但 M5001 沒有, 所以無法利用 WORKER 來操作

謝謝!

小弟之前也有類似的情形在用union
會讓資料分出來
之後改成Left join時就不會了
以下是小弟之後用的語法
不知是否和大大的類似情形
SELECT a.out_date, a.vender, b.pp
FROM
(SELECT out_date, vender FROM out_iMEI WHERE (out_date = '2008/5/10') and (vender = 'M003')) AS a
LEFT OUTER JOIN
(SELECT SUM(seq) AS pp, in_date, vender FROM in_iMEI GROUP BY in_date, vender) AS b
ON a.out_date = b.in_date AND a.vender = b.vender

不過不知大大是否還是要保留住M5000和M50001~這個2顯示

------
程式沒有這麼難
只是還沒打通其中要絕

編輯記錄

情況有點不同
因為你的案例, a, b 是利用 out_date, vender做關聯, 而且 a, b 都有這個欄位
而我的是以 worker 為 group by 做sum值, 但 M5000(a)有 , 而 M5001(b)卻沒有

===================引 用 shunaaron 文 章===================
小弟之前也有類似的情形在用union
會讓資料分出來
之後改成Left join時就不會了
以下是小弟之後用的語法
不知是否和大大的類似情形
SELECT a.out_date, a.vender, b.pp
FROM
(SELECT out_date, vender FROM out_iMEI WHERE (out_date = '2008/5/10') and (vender = 'M003')) AS a
LEFT OUTER JOIN
(SELECT SUM(seq) AS pp, in_date, vender FROM in_iMEI GROUP BY in_date, vender) AS b
ON a.out_date = b.in_date AND a.vender = b.vender

不過不知大大是否還是要保留住M5000和M50001~這個2顯示

在union下面那一段的0.0 as LISTM改成sum(a.LISTFARE) as LISTM,
這樣只要一段sql就不用二段sql再用union合併了
這樣應該就可以了吧(在ms sql可以,FIREBIRD沒用過).....

------
OS : Win 7 pro
Program : Delphi 7
DataBase : Ms Sql 2008

不知道我有沒有誤解你的意思, 你是指這樣的做法嗎?

select 'M5001' as DBFNAME, a.WORKER, sum(a.LISTFARE) as LISTM, sum(a.WORKFARE) as WORKM, sum(a.ACTMONEY) as ACTM, count(*) as FIXTIMES, sum(b.P1) as P1, sum(b.PARTTIME) as PARTTIME
from M5000 a, M5001 b
where ((FIXDATE>='970501') and (FIXDATE<='970510')) and (CONFIRM="Y") and FIXHEAD in ("900")
and a.FIXHEAD=b.FIXHEAD and a.FIXBODY=b.FIXBODY
group by a.WORKER

那就錯誤, 乍看之下好像是通的, 但是我舉一個例子

M5000-->MASTER
M5001-->DETAIL
小王 , 900-10000 , ACTMONEY $10000 ==> M5000
900-10000, P1 $800, 30
900-10000, P1 $1600, 40
小明, 900-10001, ACTMONEY $9000 ==> M5000
900-10001, P1 $1000, 55
小張, 900-10003, ACTMONEY $500 ==> M5000
900-10003, P1 $100, 10
900-10003, P1 $200, 20
900-10003, P1 $100, 30

真正要得到答案的是
WORKER ACTMONE P1 PARTTYPE
小王 10000 2400 70
小明 9000 1000 55
小張 500 500 60

可是上面的語法得到的結果是
小王, 20000,2400,70
小明, 9000,1000,55
小張, 1500,500,60

看出差異了嗎?

編輯記錄

P.D.版主不好意思,上面那個sql,我知錯在那了...
試試下面的語法,應該可以了...
select WORKER,sum(LISTM),sum(WORKM),sum(P1),sum(PARTTIME) from
(select WORKER,FIXHEAD,FIXBODY,sum(LISTFARE) AS LISTM,sum(WORKFARE) AS WORKM from M5000
group by WORKER,FIXHEAD,FIXBODY) M5000_temp,
(select FIXHEAD,FIXBODY,sum(P1) AS P1,sum(PARTTIME) AS PARTTIME from M5001
group by FIXHEAD,FIXBODY) M5001_temp
where M5000_temp.FIXHEAD=M5001_temp.FIXHEAD
and M5000_temp.FIXBODY=M5001_temp.FIXBODY
group by M5000_temp.WORKER

------
OS : Win 7 pro
Program : Delphi 7
DataBase : Ms Sql 2008

這個方法我也曾試用過, 樓上也有類似的用法, 但我發現
firebird 並不支援
select ..... from
(select .... )
的用法
===================引 用 jackiemi2_seed 文 章===================
P.D.版主不好意思,上面那個sql,我知錯在那了...
試試下面的語法,應該可以了...
select WORKER,sum(LISTM),sum(WORKM),sum(P1),sum(PARTTIME) from
(select WORKER,FIXHEAD,FIXBODY,sum(LISTFARE) AS LISTM,sum(WORKFARE) AS WORKM from M5000
group by WORKER,FIXHEAD,FIXBODY) M5000_temp,
(select FIXHEAD,FIXBODY,sum(P1) AS P1,sum(PARTTIME) AS PARTTIME from M5001
group by FIXHEAD,FIXBODY) M5001_temp
where M5000_temp.FIXHEAD=M5001_temp.FIXHEAD
and M5000_temp.FIXBODY=M5001_temp.FIXBODY
group by M5000_temp.WORKER

有支援這樣的語法嗎 ?
SELECT WORKER , LISTM,(SELECT SUM(P1) FROM M50l WHERE FH=A.FH AND FB=A.FB) AS P1,
(SELECT SUM(PARTTIME) FROM M501 WHERE FH=A.FH AND FB=A.FB) AS PARTTIME
FROM M500 A
WHERE
......

如果沒有我建議
1. 用兩段式語法 先處 M500 將結果存入暫存檔, 在 UPDATE M500 SET P1=(SELECT SUM(P1) FROM M501 WHERE ...)

編輯記錄

雖然沒有人解答出來, 但我想jackiemi2_seed所提出的方法在其他資料庫應該是可行的, 因為之前我也想過這樣的做法, 但FireBird就是不支援, 所以我還是把分數給jackiemi2_seed

For MS-SQL 
http://delphi.ktop.com.tw/board.php?cid=30&fid=66&tid=92650



MSSQL 2K Help 有標準的SQL語法