--测试环境 Create table 表A(ID int,name varchar(10)) insert into 表A select 1,'小张' union all select 2,'小明' --union all select 3,'小李'create table 表B(ID int,BID int) insert into 表B select 1,1 union all select 2,1 union all select 3,2 union all select 4,2 union all select 5,1 union all select 6,1--查询 select ID=A.ID, Name, 个数=count(*) from 表A A inner join 表B B on A.ID=B.BID group by A.ID,NAME order by ID--结果ID Name 个数 ----------- ---------- ----------- 1 小张 4 2 小明 2(2 row(s) affected)--和上一个问题有什么关系吗??
select A.*,b.count from A, (select bid,cnt=count(*) from B group by bid) b where a.id=b.bid
select A.ID, Name, 个数=count(*) from 表A A inner join 表B on A.ID=B.BID group by A.ID,NAME order by NAME
select A.*,b.cnt from A, (select bid,cnt=count(*) from B group by bid) b where a.id=b.bid先合计count(bid),再联接一下就行了
create proc pA as declare @count int declare @A_userid varchar(10) declare @A_usename varchar(10) declare @a varchar(10) declare @i int set @i=0 declare A_cursor cursor local for select userid,usename from A open A_cursor fetch next from A_cursor into @A_userid,@A_usename while @@fetch_status=0 begin exec pB @A_userid,@a output select @count=@a if @i=0 create table #t(userid varchar(10),username varchar(10),userls int ) --写入 else insert into #t values(@A_userid,@A_usename,@count) fetch next from A_cursor into @A_userid,@A_usename set @i=@i+1 end select * from #t --truncate #temp drop table #t gocreate proc pB @A_Filter_userid varchar(10), @B_count int output as declare @B_id varchar(10) declare @B_userid varchar(10) declare @B_sum int set @B_sum=0 declare B_cursor cursor local for select b_id,b_userid from B open B_cursor fetch next from B_cursor into @B_id,@B_userid while @@fetch_status=0 begin if @A_Filter_userid=@b_userid begin set @B_sum=@B_sum+1 --出现总次数 end fetch next from B_cursor into @B_id,@B_userid end select @B_count=@B_sum select @B_count go
select countz(*) from table_a inner join table_b where id=bid order by name desc
大家能说说那种方法的优点和缺点吗? -------------------------------------- Create table 表A(ID int,name varchar(10),othercol varchar(3)) insert into 表A select 1,'小张','1st' union all select 2,'小明','2nd' union all select 3,'小李','3rd'create table 表B(ID int,BID int) insert into 表B select 1,1 union all select 2,1 union all select 3,2 union all select 4,2 union all select 5,1 union all select 6,1--查询 select A.*,cnt=isnull(b.cnt,0) from A left join (select bid,cnt=count(*) from B group by bid) b on a.id=b.bid order by a.name--结果ID Name othercol cnt ----------- ---------- ----------- ---------- 1 小张 1st 4 2 小明 2nd 2 3 小李 3rd 0 优点: 1)可以显示A表所有列(当A表不只有二列时) 2)可以显示B表没有的人的次数
select a.ID,a.Name,c.co from a left join (select Bid,count(Bid) co from b group by Bid) c on c.Bid= a.ID
select * from a b,(select bid,count(bid) as tt from b group by bid) c where b.id=c.bid
select * from a d,(select bid,count(bid) as tt from b group by bid) c where d.id=c.bid
Create table 表A(ID int,name varchar(10))
insert into 表A select 1,'小张'
union all select 2,'小明'
--union all select 3,'小李'create table 表B(ID int,BID int)
insert into 表B select 1,1
union all select 2,1
union all select 3,2
union all select 4,2
union all select 5,1
union all select 6,1--查询
select ID=A.ID,
Name,
个数=count(*)
from 表A A inner join 表B B
on A.ID=B.BID
group by A.ID,NAME
order by ID--结果ID Name 个数
----------- ---------- -----------
1 小张 4
2 小明 2(2 row(s) affected)--和上一个问题有什么关系吗??
where a.id=b.bid
Name,
个数=count(*)
from 表A A inner join 表B
on A.ID=B.BID
group by A.ID,NAME
order by NAME
where a.id=b.bid先合计count(bid),再联接一下就行了
as
declare @count int
declare @A_userid varchar(10)
declare @A_usename varchar(10)
declare @a varchar(10)
declare @i int
set @i=0
declare A_cursor cursor local for
select userid,usename from A
open A_cursor
fetch next from A_cursor
into @A_userid,@A_usename
while @@fetch_status=0
begin
exec pB @A_userid,@a output
select @count=@a
if @i=0
create table #t(userid varchar(10),username varchar(10),userls int )
--写入
else insert into #t values(@A_userid,@A_usename,@count)
fetch next from A_cursor
into @A_userid,@A_usename
set @i=@i+1
end
select * from #t
--truncate #temp
drop table #t
gocreate proc pB
@A_Filter_userid varchar(10),
@B_count int output as
declare @B_id varchar(10)
declare @B_userid varchar(10)
declare @B_sum int
set @B_sum=0 declare B_cursor cursor local for
select b_id,b_userid from B
open B_cursor
fetch next from B_cursor
into @B_id,@B_userid
while @@fetch_status=0
begin
if @A_Filter_userid=@b_userid
begin
set @B_sum=@B_sum+1 --出现总次数
end
fetch next from B_cursor
into @B_id,@B_userid
end
select @B_count=@B_sum
select @B_count
go
inner join
table_b
where id=bid
order by name desc
--------------------------------------
Create table 表A(ID int,name varchar(10),othercol varchar(3))
insert into 表A select 1,'小张','1st'
union all select 2,'小明','2nd'
union all select 3,'小李','3rd'create table 表B(ID int,BID int)
insert into 表B select 1,1
union all select 2,1
union all select 3,2
union all select 4,2
union all select 5,1
union all select 6,1--查询
select A.*,cnt=isnull(b.cnt,0) from A left join (select bid,cnt=count(*) from B group by bid) b
on a.id=b.bid order by a.name--结果ID Name othercol cnt
----------- ---------- ----------- ----------
1 小张 1st 4
2 小明 2nd 2
3 小李 3rd 0
优点:
1)可以显示A表所有列(当A表不只有二列时)
2)可以显示B表没有的人的次数
left join
(select Bid,count(Bid) co from b
group by Bid) c
on c.Bid= a.ID
select * from a b,(select bid,count(bid) as tt from b group by bid) c where b.id=c.bid