上一个问题在这,已经结了
http://community.csdn.net/Expert/topic/5766/5766276.xml?temp=.9950373
改进如下
表A中没有userid ,而是变成了tid
表T如下:
tid userid
1 1
2 1
3 2
4 3
表Aid tid 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谢谢
http://community.csdn.net/Expert/topic/5766/5766276.xml?temp=.9950373
改进如下
表A中没有userid ,而是变成了tid
表T如下:
tid userid
1 1
2 1
3 2
4 3
表Aid tid 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谢谢
insert into t values( 1, 1)
insert into t values( 2, 1)
insert into t values( 3, 2)
insert into t values( 4, 3)
create table a(id int, tid int, f int)
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 , t , b
where a.tid = t.tid and a.f = 1 and t.userid = b.userid
group by b.username
order by count_f descdrop table a,b,t/*
username count_f
---------- -----------
aa 3
bb 1(所影响的行数为 2 行)
*/
insert into t values( 1, 1)
insert into t values( 2, 1)
insert into t values( 3, 2)
insert into t values( 4, 3)
create table a(id int, tid int, f int)
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')
goselect b.username , m.count_f from t,b,
(select tid , count(*) count_f from a where f = 1 group by tid) m
where t.tid = m.tid and b.userid = t.userid
order by count_f descdrop table a,b,t/*
username count_f
---------- -----------
aa 2
aa 1
bb 1(所影响的行数为 3 行)
*/