再求一高难度的sql语句?
表 A
ID Name
1 小张
2 小明
表 B
ID BID
1 1
2 1
3 2
4 2
5 1
6 1
表A(ID)和表B的(BID)关联,求表A的用户在表B中的记录总数,并按照用户姓名进行排列
显示:
1 小张 4
2 小明 2
问题点数：20、回复次数：13Top
1 楼zlp321002（Life Is Good,Let's Shine）回复于 2005-11-03 20:45:12 得分 0
--
select ID,
Name,
个数=count(*)
from 表A A inner join 表B
on A.ID=B.BID
group by ID,NAME
order by NAMETop
2 楼zlp321002（Life Is Good,Let's Shine）回复于 2005-11-03 20:48:42 得分 0
--测试环境
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)
--和上一个问题有什么关系吗？？
Top
3 楼zxkid（如果你有勇气迈出第一步，未来又能糟糕成什么样呢？）回复于 2005-11-03 20:49:58 得分 0
select A.*,b.count from A, (select bid,cnt=count(*) from B group by bid) b
where a.id=b.bidTop
4 楼xueguang（xg）回复于 2005-11-03 20:50:36 得分 0
select A.ID,
Name,
个数=count(*)
from 表A A inner join 表B
on A.ID=B.BID
group by A.ID,NAME
order by NAMETop
5 楼zxkid（如果你有勇气迈出第一步，未来又能糟糕成什么样呢？）回复于 2005-11-03 20:52:33 得分 0
select A.*,b.cnt from A, (select bid,cnt=count(*) from B group by bid) b
where a.id=b.bid
先合计count(bid),再联接一下就行了Top
6 楼hzy8（hzy8）回复于 2005-11-04 14:28:14 得分 0
大家能说说那种方法的优点和缺点吗？Top
7 楼lsp69（浪子神剑）回复于 2005-11-04 15:42:14 得分 0
66Top
8 楼lsp69（浪子神剑）回复于 2005-11-04 17:28:30 得分 0
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
go
create 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
Top
9 楼huang2005（不懂）回复于 2005-11-04 18:47:47 得分 0
select countz(*) from table_a
inner join
table_b
where id=bid
order by name descTop
10 楼zxkid（如果你有勇气迈出第一步，未来又能糟糕成什么样呢？）回复于 2005-11-04 19:06:40 得分 0
大家能说说那种方法的优点和缺点吗？
--------------------------------------
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表没有的人的次数Top
11 楼wenwencao（温文）回复于 2005-11-05 09:44:37 得分 0
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
Top
12 楼liu9403（江湖）回复于 2005-11-05 13:23:16 得分 0
select * from a b,(select bid,count(bid) as tt from b group by bid) c where b.id=c.bid
Top
13 楼liu9403（江湖）回复于 2005-11-05 13:24:02 得分 0
select * from a d,(select bid,count(bid) as tt from b group by bid) c where d.id=c.bidTop
