biaoA
id name aa bb cc .........
--------------------------------------------------
631 zhang fdfa 53645 oip ........
388 li fda 986967 ioio .........
1085 li hgfg 7657 jkfj .......
689 chen fdsafds 563634 jhfgjf ......biaoB
Bid Bname score other
----------------------------
631 zhang 79 xxxx
631 zhang 53 yyyy
388 li 90 zzzz
631 zhang 60 gggggg
689 chen 70 gfdggd
1085 li 99 fdasfas
388 li 45 afdda
1085 li 69 fadfdas我想取出所有biaoA的数据,外加对应biaoB的sum(biaoB.score)
即得到类似如下表
id name aa bb cc ........... totalscore
-------------------------------------------------------------
631 zhang fdfa 53645 oip ........ 192
388 li fda 986967 ioio ......... 135
1085 li hgfg 7657 jkfj ....... 168
689 chen fdsafd 563634 jhfgjf ...... 70现在我只会如下的sql查询
select aa,
bb,
cc,
sum(score) as totalscore,
name,
id
from biaoA,biaoB
where id=Bid and name=Bname
group by id,name,aa,bb,ccbiaoA中列一多起来,我的实现就太麻烦了,我觉得用group by 和 join应该可以
很方便的实现类似于
select * from biaoA join biaoB on id=Bid and name=Bname group by id
当然,我这个实现是错误的,提示biaoA的其他列未包含在group by中请教应该如何实现,万分感谢。
id name aa bb cc .........
--------------------------------------------------
631 zhang fdfa 53645 oip ........
388 li fda 986967 ioio .........
1085 li hgfg 7657 jkfj .......
689 chen fdsafds 563634 jhfgjf ......biaoB
Bid Bname score other
----------------------------
631 zhang 79 xxxx
631 zhang 53 yyyy
388 li 90 zzzz
631 zhang 60 gggggg
689 chen 70 gfdggd
1085 li 99 fdasfas
388 li 45 afdda
1085 li 69 fadfdas我想取出所有biaoA的数据,外加对应biaoB的sum(biaoB.score)
即得到类似如下表
id name aa bb cc ........... totalscore
-------------------------------------------------------------
631 zhang fdfa 53645 oip ........ 192
388 li fda 986967 ioio ......... 135
1085 li hgfg 7657 jkfj ....... 168
689 chen fdsafd 563634 jhfgjf ...... 70现在我只会如下的sql查询
select aa,
bb,
cc,
sum(score) as totalscore,
name,
id
from biaoA,biaoB
where id=Bid and name=Bname
group by id,name,aa,bb,ccbiaoA中列一多起来,我的实现就太麻烦了,我觉得用group by 和 join应该可以
很方便的实现类似于
select * from biaoA join biaoB on id=Bid and name=Bname group by id
当然,我这个实现是错误的,提示biaoA的其他列未包含在group by中请教应该如何实现,万分感谢。
totalsocre=SUM(score)
from a join b on a.bid=b.bid
group by id , name , aa , bb , cc .........
totalsocre=SUM(score)
from a join b on a.bid=b.bid
group by a.id , a.name , a.aa , a.bb , a.cc .........
--后面Group by a的所有列
select biaoA.*,(select sum(score) from biaoB where Bid=A.id and Bname=A.name ) from biaoA A
FROM BIAOA A
LEFT JOIN (
SELECT BID,SUM(SCORE) 'TOTALSCORE'
) B ON A.ID=B.BID
select A.*,(select sum(score) from biaoB where Bid=A.id and Bname=A.name ) from biaoA A
totalsocre=SUM(score)
from a join (select id ,SUM(score) as p from b group by id) k on a.bid=k.bid
from a join (select bid,SUM(score) as totalsum from b group by bid) k on a.bid=k.bid
是否通过其他方法实现只group by id ?
from biaoA
join (
select bid,sum(score) score
from biaoB
group by bid
) b
on a.id=b.bid
select a.id,a.name,a.aa,a.bb,a.cc,totalscore=(select sum(score) from biaoB where bid=b.bid)
from biaoA a
left join biaoB b
on a.id=b.Bid and a.name=b.Bname
select a.*,totalscore
from a join (select bid,SUM(score) as totalscore from b group by bid) k on a.id=k.bid
from biaoA as a --漏了个别名
join (
select bid,sum(score) score
from biaoB
group by bid
) as b
on a.id=b.bid
----试
select A.*,
(select sum(score) from biaoB where Bid=A.id and Bname=A.name ) as otalscore
from biaoA A
select a.*,totalscore=(select sum(score) from biaoB where bid=a.id)
from biaoA a
呵呵