请教一条sql语句的问题

小Leo 发布于 2012/11/29 19:44
阅读 659
收藏 3

【Gopher China万字分享】华为云的Go语言云原生实战经验!>>>

table A

userid     username

1            Lucy

2            Micher

3            Leo

=============================================================

table B

userid     productid

1            1

1            2

1            3

2            1

2            2

2            3

3            1

3            2

==========================================================

问题:能否通过一条sql语句 来实现 结果如下

userid     username     buycount

1            Lucy               3

2            Micher            3

3            Leo                2


谢谢~


加载中
0
Ryan-瑞恩
Ryan-瑞恩

select a.userid,  a.username,  sum(decode(b.userid,  1)) from A a, B.b

你试试,,,可能不对,我没有测。

0
小Leo
小Leo

引用来自“Rayn-瑞恩”的答案

select a.userid,  a.username,  sum(decode(b.userid,  1)) from A a, B.b

你试试,,,可能不对,我没有测。

先谢谢哈· mysql 好像没有这个decode
0
excepiton
excepiton

select a.userid,  a.username, count(1) from A a, B.b

where a.userid=b.userid group by a.userid,a.username

0
Ryan-瑞恩
Ryan-瑞恩

哦,,,,再给你给个写法

select ingtime as date, sum(case when sf='A' then 1 else 0 end) as A, sum(case when sf='B' then 1 else 0 end ) as B from person group by ingtime.

0
qhboy
qhboy
SELECT A.username,c.userid,c.buycount from A,(select B.userid,COUNT(*) as buycount from B GROUP BY b.userid)  as c WHERE A.userid = c.userid
0
只会百度的程序员
只会百度的程序员
select a.userid,a.username from a,(select userid,count(userid) as buycount from b group on userid) b where a.userid = b.userid
这样对不对,实在不行b表再选一次。
0
StormFour
StormFour

select a.userid, a.username, count(b.productid) buycount from a join b on (a.userid = b.userid)

group by a.userid, a.username;

done

0
William
William
select a.*, buycount = ( select count(*) from tableB b where b.userid =                    a.userid ) 
    from tableA a
    order by a.userid
0
A阿福A
A阿福A
一个内连接就能实现的问题,干嘛搞这么麻烦?
0
兵荒马乱
兵荒马乱
select  a.id ,a.username, max(b.buycount)  from tableA a LEFT JOIN  tableB b on a.userid=b.userid  group by  a.userid  ;
返回顶部
顶部