表A
id userid f ...
1 1 1
2 1 1
3 2 0
4 2 1
5 3 1
...
表B
userid username ...
1 aa
2 bb
3 cc
...
实现表C
username count_f
aa 2
bb 1
cc 1
就是要按表A里字段f=1的多少排序
排出userid的弄出来了,可要从表B里再把username弄出来搞了半天都没搞好~~
先谢谢了
id userid f ...
1 1 1
2 1 1
3 2 0
4 2 1
5 3 1
...
表B
userid username ...
1 aa
2 bb
3 cc
...
实现表C
username count_f
aa 2
bb 1
cc 1
就是要按表A里字段f=1的多少排序
排出userid的弄出来了,可要从表B里再把username弄出来搞了半天都没搞好~~
先谢谢了
from a , b
where a.userid = b.userid and a.f = 1
group by b.username
order by count_f desc
insert into a values(1, 1, 1)
insert into a values(2, 1, 1)
insert into a values(3, 2, 0)
insert into a values(4, 2, 1)
insert into a values(5, 3, 1)
create table b(userid int,username varchar(10))
insert into b values(1, 'aa')
insert into b values(2, 'bb')
insert into b values(3, 'cc')
go
select b.username, count(*) count_f
from a , b
where a.userid = b.userid and a.f = 1
group by b.username
order by count_f descdrop table a,b/*
username count_f
---------- -----------
aa 2
bb 1
cc 1(所影响的行数为 3 行)
*/
Select username,(Select Sum(f) From A Where userid=B.userid) As count_f From B Order By userid
Select username,(Select Count(f) From A Where f=1 And userid=B.userid) As count_f From B Order By userid