有如下三个表:
a 表:
cardno terminalno ctc traamount
------ ---------- ----------- --------------
1000111100000569893 110004820016 1 2000.00
1000111100000569893 131100000919 1 900.00
1000111100000569893 110004820016 2 2000.00
1000111100000569893 131100000918 2 749.98
1000111100000569893 131100001823 3 0.00 b 表:cardno ctc terminalno nodeno
------ ----------- ---------- ------
1000111100000117878 117 131100000918 11000349
1000111100000162518 94 131100000918 11000349
1000111100000334573 181 131100000918 11000349
1000111100000000072 66 131100001922 11000144
1000111100000258647 631 131100001823 11000349
1000111100000258685 182 131100001823 11000349
1000111100000000199 171 131100000291 11000145
1000111100000000204 285 131100002607 11000150
1000111100000258944 243 131100000918 11000349 c表:
nodename nodeno
-------- ------
益特诺 11000349
昌平东 11000144
昌平西 11000145 现在要得到这个答案:
select a.cardno,c.nodename提示:
对于每一个NODENO 他的CTC都有最大值
在此先谢了!
a 表:
cardno terminalno ctc traamount
------ ---------- ----------- --------------
1000111100000569893 110004820016 1 2000.00
1000111100000569893 131100000919 1 900.00
1000111100000569893 110004820016 2 2000.00
1000111100000569893 131100000918 2 749.98
1000111100000569893 131100001823 3 0.00 b 表:cardno ctc terminalno nodeno
------ ----------- ---------- ------
1000111100000117878 117 131100000918 11000349
1000111100000162518 94 131100000918 11000349
1000111100000334573 181 131100000918 11000349
1000111100000000072 66 131100001922 11000144
1000111100000258647 631 131100001823 11000349
1000111100000258685 182 131100001823 11000349
1000111100000000199 171 131100000291 11000145
1000111100000000204 285 131100002607 11000150
1000111100000258944 243 131100000918 11000349 c表:
nodename nodeno
-------- ------
益特诺 11000349
昌平东 11000144
昌平西 11000145 现在要得到这个答案:
select a.cardno,c.nodename提示:
对于每一个NODENO 他的CTC都有最大值
在此先谢了!
from A a
left join B b on a.cardno = b.cardno
left join C c on c.nodeno = b.nodeno
现在我再仔细说明一下:
我的目的是要根据a.terminalno和b.terminalno来找到a.cardno所对应的nodeno
然后从c表中查到对应的nodename
b表中的nodeno有许多相同值,但是他们的CTC是不同的,
查询结果应该是这样的:
cardno
cardno terminalno ctc traamount nodename
1000111100000569893 131100000918 2 749.98 益特诺
1000111100000569893 131100001823 3 0.00 益特诺
select a.cardno,a.terminalno,a.ctc,c.nodename
from A a
left join (select terminalno,nodeno from B group by terminalno,nodeno) b on a.terminalno = b.terminalno
left join C c on c.nodeno = b.nodeno
declare @cardno char(19)
select @cardno="1000111100000074059"
select a.cardno , a.ctc , a.tratime , a.traamount , c.nodename
from card_trainfor a,oilvouch b ,nodeinfor c
where c.nodeno=(select distinct(nodeno)from oilvouch where a.terminalno=b.terminalno)
and a.cardno=@cardno