求助一段sql statement....

counterking

SELECT amount FROM credit_payment_record WHERE CPRID = SELECT CPRID FROM credit_payment_history WHERE Ref_ID = 'ABC'

谁能帮我修改一下上面的sql statement呢？

我要求的只是找出from credit_payment_record 等于CPRID的record(s)，但条件是先要找出those record from credit_payment_history which Ref_ID = ABC

milktin

SELECT amount FROM credit_payment_record WHERE CPRID IN (SELECT CPRID FROM credit_payment_history WHERE Ref_ID = 'ABC')

先从
SELECT CPRID FROM credit_payment_history WHERE Ref_ID = 'ABC'
拿出你要得records

再check 你的CRPID有没有在query records





[size=-1]-----------------------------------------
[size=-1]牛奶罐
[size=-1]零点一度 [url]www.dot1degree.com[/url]
[size=-1]从零点开始，寻找你人生第一度的变化！
[size=-1]我的 IT 日记

[ 本帖最后由 milktin 于 14-5-2006 06:43 PM 编辑 ]

counterking

谢谢！ 原来就是这样。。。

SELECT name FROM credit_payment_record WHERE CPRID IN (SELECT cprid
FROM history WHERE Ref_ID = 'ABC' ORDER BY date ASC)

为何显示出来的record, 并没有照着 history talble 的date ascending display 在datagrid...??

goatstudio

原帖由 kengkit 于 28-4-2006 04:20 PM 发表
SELECT name FROM credit_payment_record WHERE CPRID IN (SELECT cprid
FROM history WHERE Ref_ID = 'ABC' ORDER BY date ASC)

为何显示出来的record, 并没有照着 history talble 的date ascending displa ...

SELECT name FROM credit_payment_record WHERE CPRID IN (SELECT cprid
FROM history WHERE Ref_ID = 'ABC' ORDER BY date ASC)

那是因为你的 order by 是在 history 的, 改成以下的:

SELECT name FROM credit_payment_record WHERE CPRID IN (SELECT cprid
FROM history WHERE Ref_ID = 'ABC') ORDER BY date ASC

counterking

SELECT A.name FROM credit_payment_record as A WHERE A.CPRID IN (SELECT H.cprid
FROM history AS H WHERE H.Ref_ID = 'ABC' ORDER BY H.date ASC ) ORDER BY A.type


它只能sorting by A.Type.. 并不能也同时sorting by H.date

SELECT CPRID FROM credit_payment_history
WHERE Ref_ID = 'ABC'
ORDER BY a.type ASC;

counterking

原帖由 anakin81 于 9-5-2006 04:04 PM 发表
SELECT CPRID FROM credit_payment_history
WHERE Ref_ID = 'ABC'
ORDER BY a.type ASC;

可以post完整的sql statement 吗？

flashang

原帖由 counterking 于 2-5-2006 04:22 PM 发表
SELECT A.name FROM credit_payment_record as A WHERE A.CPRID IN (SELECT H.cprid
FROM history AS H WHERE H.Ref_ID = 'ABC' ORDER BY H.date ASC ) ORDER BY A.type


它只能sorting by A.Type.. 并不能也 ...

大概是這樣

SELECT A.name, B.date FROM credit_payment_record A left join
history B on A.CPRID = B.cprid
WHERE A.CPRID IN (SELECT H.cprid
FROM history H WHERE H.Ref_ID = 'ABC' ) ORDER BY A.type, B.date

不過, 以上的 FIELD NAME 使用許多 KEYWORD, 容易讓人混亂