表AGuideID GuideName
1 aaa
2 bbb表B id GuideID ...
1 1
2 1
3 1
4 2Count表B GuideID相同的记录,并排序。 如以上数据得到 GuideID GuideName Count
1 aaa 3
2 bbb 1
1 aaa
2 bbb表B id GuideID ...
1 1
2 1
3 1
4 2Count表B GuideID相同的记录,并排序。 如以上数据得到 GuideID GuideName Count
1 aaa 3
2 bbb 1
insert into 表A(GuideID,GuideName)
select 1,'aaa' union
select 2,'bbb'
go
create table 表B(id int,GuideID int)
insert into 表B(id,GuideID)
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,2
go
select GuideID,GuideName,[Count] = (select count(*) from 表B where GuideID = 表A.GuideID)
from 表A
order by [count] desc
go
drop table 表A
drop table 表B
select a.GuideID,a.GuideName,count(b.GuideID) [count] from 表A a left join 表B b
on a.GuideID =b.GuideID
group by a.GuideID,a.GuideName
order by a.GuideID
on a.GuideID =b.GuideID
group by a.GuideID,a.GuideName
order by a.GuideID