SQL在主表中显示附表合计例二
主表GGNR(select * from product)
| id |
title |
BigClassName |
| 1 |
谢祥文
|
局领导 |
| 2 |
廖全明
|
局领导 |
| 3 |
夏荣辉
|
局领导 |
附表(select * from Productvote)
| hits1 |
hits2 |
hits3 |
hits4 |
vote_id |
| 1 |
0 |
0 |
0 |
3 |
| 0 |
1 |
0 |
0 |
3 |
| 1 |
0 |
0 |
0 |
2 |
结果
select id,title,BigClassName,isnull(bbb.hits1,0) as hits1,isnull(bbb.hits2,0) as hits2,isnull(bbb.hits3,0) as hits3,isnull(bbb.hits4,0) as hits4 from product left join (select vote_id,sum(hits1) as hits1,sum(hits2) as hits2,sum(hits3) as hits3,sum(hits4) as hits4 from productvote group by vote_id) bbb on product.id=bbb.vote_id
| id |
title |
BigClassName |
hits1 |
hits2 |
hits3 |
hits4 |
| 1 |
谢祥文 |
局领导 |
0 |
0 |
0 |
0 |
| 2 |
廖全明 |
局领导 |
1 |
0 |
0 |
0 |
| 3 |
夏荣辉 |
局领导 |
1 |
1 |
0 |
0 |
注：如果加查询条件如下：
select id,title,BigClassName,isnull(bbb.hits1,0) as hits1,isnull(bbb.hits2,0) as hits2,isnull(bbb.hits3,0) as hits3,isnull(bbb.hits4,0) as hits4 from product left join (select vote_id,sum(hits1) as hits1,sum(hits2) as hits2,sum(hits3) as hits3,sum(hits4) as hits4 from productvote group by vote_id) bbb on product.id=bbb.vote_id（加查询条件where 。。。）
ASP下ACCESS调用方法：
select id,title,BigClassName,bbb.hits1,bbb.hits2,bbb.hits3,bbb.hits4 from product left join (select vote_id,sum(hits1) as hits1,sum(hits2) as hits2,sum(hits3) as hits3,sum(hits4) as hits4 from productvote group by vote_id) bbb on product.id=bbb.vote_id
|
当前位置：