求一SQL語句的算法:高手請進

[zjllxy]

有主明細表結構如下
tableA:
No InvoiceNo InvoiceMoney
001 4180123 3312.00
002 4180124 1390.50
003 4180125 759.00

tableB:
No Seq Qty Price
001 01 1200 .29
001 02 3800 .29
001 03 4900 .38
002 01 1950 .29
002 02 2500 .33
003 01 2300 .33

表tableA的InvoiceMoney字段的值是tableB表的Qty*price的按No分的總和
現在我用
select tableA.No,tableA.InvoiceNo,tableB.Qty,tableB.Price, convert(numeric(18,2),round(a.CheckOkQty*d.Price,2,1)) as YHPay,tableB.InvoiceMoney from tableA join tableB on tableA.No=tableB.No 得到結果如下:
No InvoiceNo Qty Price YHPay InvoiceMoney
001 4180123 1200 .29 348.00 3312.00
001 4180123 3800 .29 1102.00 3312.00
001 4180123 4900 .38 1862.00 3312.00
002 4180124 1950 .29 565.50 1390.50
002 4180124 2500 .33 825.00 1390.50
003 4180125 2300 .33 759.00 759.00

而我想要的結果如下:
No InvoiceNo Qty Price YHPay InvoiceMoney
001 4180123 1200 .29 348.00 0
001 4180123 3800 .29 1102.00 0
001 4180123 4900 .38 1862.00 3312.00
002 4180124 1950 .29 565.50 0
002 4180124 2500 .33 825.00 1390.50
003 4180125 2300 .33 759.00 759.00

問可不可以得到想要的結果,sql語句如何寫呢?

[Yang_]

select a.No,a.InvoiceNo,b.Qty,b.Price,
convert(numeric(18,2),round(b.Qty*b.Price,2,1)) as YHPay,
case when Seq=(select max(Seq) from tableB where No=b.No) then a.InvoiceMoney else 0 end as InvoiceMoney
from tableA a join tableB b on a.No=b.No

你原来的语句好多错误

[lightJing]

select a.no,a.invoiceno,b.qty,t.price,cast(b.qty*t.price as decimal(10,2)) as YHpay,case when x.seq=b.seq then a.invoiceMoney else 0 end as InvoiceMoney
from (select no,max(seq) as seq from tableB group by no) x
inner join tableA a on x.no=a.no
inner join tableB b on a.no=b.no

[liuweicai772]

看到这个问题 我突然想起了另外一个问题 嗨 愁的我现在都想不起来了！