62,074
社区成员
发帖
与我相关
我的任务
分享
declare @btable table (bid int,name nvarchar(5))
insert @btable select 1, 'a'
union all select 2, 'aa'
union all select 3, 'ccc'
union all select 4, 'dd'
declare @stable table (sid int,bid int,userid int)
insert @stable select 1 , 2 , 1
union all select 2 , 2, 2
union all select 3 , 4 , 2
union all select 4 , 3 , 3
union all select 5 , 2 , 2
union all select 6 , 1 , 2
union all select 7 , 1 , 2
union all select 8 , 2 , 2
select a.bid,a.name,count(1) as '次数'
from @btable a join @stable b on a.bid=b.bid
where b.userid=2 group by a.bid,a.name
order by 次数 desc
declare @btable table (bid int,name nvarchar(5))
insert @btable select 1, 'a'
union all select 2, 'aa'
union all select 3, 'ccc'
union all select 4, 'dd'
declare @stable table (sid int,bid int,userid int)
insert @stable select 1 , 2 , 1
union all select 2 , 2, 2
union all select 3 , 4 , 2
union all select 4 , 3 , 3
union all select 5 , 2 , 2
union all select 6 , 1 , 2
union all select 7 , 1 , 2
union all select 8 , 2 , 2
select max(a.bid) as bid,name,count(a.bid) as '次数'
from @btable a left join @stable b on a.bid=b.bid
where b.userid=2 group by (name)
/*
bid name 次数
----------- ----- -----------
1 a 2
2 aa 3
4 dd 1
*/
create table btable(bid int,name varchar(20))
insert into btable select 1,'a'
insert into btable select 2,'aa'
insert into btable select 3,'ccc'
insert into btable select 4,'dd'
create table stable(sid int,bid int,userid int)
insert into stable select 1,2,1
insert into stable select 2,2,2
insert into stable select 3,4,2
insert into stable select 4,3,2
insert into stable select 5,2,2
insert into stable select 6,1,2
insert into stable select 7,1,2
insert into stable select 8,2,2
--
--我想根据分类表中获得userid=2这样的数据
-- bid name 次数
-- 2 aa 3
-- 1 a 2
-- 4 dd 1
select a.bid,a.name,count(1) as '次数'
from btable a join stable b on a.bid=b.bid
where b.userid=2
group by a.bid,a.name
select bid,name,count(1) as '次数' from btable a join stable b on a.bid=b.bid where b.userid=2