现有两个表: A表:CODE QTY1 DATA B表 CODE QTY2 A 10 4 A 50 B 15 4 B 51 C 20 4 C 52 A 21 3 D 22 3 要求查询结果是: CODE QTY1-3 QTY1-4 QTY2 A 21 10 50 B 0 15 51 C 0 20 52 D 22 0 0 SQL语句该怎么写?
select code,sum(qty1-3),sum(qty1-4),qty2 from a,b where a.code=b.code group by code 就ok了你试试。
----------------------------------------------
中流一壶,千金争挈。宁为铅刀,毋为楮叶。错节盘根,利器斯别。识时务者,呼为俊杰!
select code,qty1 as [qty1-3] into temp1 from A where data=3 select code,qty1 as [qty1-4] into temp2 from A where data=4 select case when isnull(temp2.code,'a')='a' then temp1.code else temp2.code end as code,temp1.[qty1-3],temp2.[qty1-4] into temp3 from temp2 full join temp1 ON TEMP1.CODE=TEMP2.code select case when isnull(temp3.code,'a')='a' then b.code else temp3.code end as code,temp3.[qty1-3],temp3.[qty1-4],b.qty2 from temp3 full join b on temp3.code=b.code
----------------------------------------------
| | 凡事无绝对 | | 不须太强求 | |
我想了一下午,想了个笨方法: select distinct code,(select qty from A where A.code=code and data=4), ,(select qty from A where a.code=code and data=3),(select * from B where A.code=B.code) from A
为什么?我试过了,可以呀!: select distinct code,(select qty from A where data=4), (select qty from A where data=3),(select * from B where A.code=B.code) from A 这样应该好看点,,,