求SQL啊，急急急～～～

[dream1231]

table1:userid,usersize(value=1,2,3)
table2:userid,region;
写一个sql语句找出哪个地区对应的用户的usersize=3的数量最多的

就是找出一个地区，这个地区有最多用户是usersize=3的

各位大虾帮帮手啊，小弟感激不尽。。。

[百年树人]

select top 1 t2.region
from table1 t1,table2 t2
where t1.userid=t2.userid
and t1.usersize=3
group by t2.region
order by count(1) desc

[daisy8675]

楼上素抢分狂么

[Bamboo28]

总算做完了。
困死了，帮不了你哦。
好好加油！

[jiangshun]

select top 1 region,数量=count(1)
from table1 t1 A join table2 t2 B
on A.userid=B.userid
where A.usersize=3
group by region
order by count(1) desc

[Zoezs]

select top 1 t2.region,count(1)
	from table1 t1,table2 t2
		where t1.userid=t2.userid
			and t1.usersize=3
				group by t2.region
				order by count(1) desc

[水族杰纶]

[Quote=引用 2 楼 daisy8675 的回复:]
楼上素抢分狂么
[/Quote]

[dawugui]

[Quote=引用楼主 dream1231 的回复:]
table1:userid,usersize(value=1,2,3)
table2:userid,region;
写一个sql语句找出哪个地区对应的用户的usersize=3的数量最多的

就是找出一个地区，这个地区有最多用户是usersize=3的

各位大虾帮帮手啊，小弟感激不尽。。。
[/Quote]

select top 1 n.region , count(1) cnt from table1 m , table2 n where m.userid = n.userid and m.usersize = 3 group by n.region order by cnt desc

[SQL77]

select 
    MAX( t2.region)
from 
	table1 t1,table2 t2
where 
	t1.userid=t2.userid
and	
	t1.usersize=3
group by 
	t2.region

[--小F--]

select
   top 1 region,count(1) as num
from 
   table1 t1  
join table2 t2 on 
   t1.userid=t2.userid
where
   t1.usersize=3
group by
   region
order by
   count(1) desc

[feixianxxx]

with cte as
(
select userid,usersize,COUNT(*) as 个数
from table1 
group by userid,usersize) 
select region 
from table2 join cte a on table2.userid=a.userid
where usersize=3 and not exists(select * from cte where a.userid=userid and 个数>a.个数 )

[--小F--]

[Quote=引用 4 楼 jiangshun 的回复:]
SQL codeselecttop1 region,数量=count(1)from table1 t1 Ajoin table2 t2 Bon A.userid=B.useridwhere A.usersize=3groupby regionorderbycount(1)desc
[/Quote]
我在拼命 只是你不知道

[dream1231]

[Quote=引用 9 楼 sql77 的回复:]
SQL codeselectMAX( t2.region)from
table1 t1,table2 t2where
t1.userid=t2.useridand
t1.usersize=3groupby
t2.region
[/Quote]

MAX可以这样用的吗？

[dawugui]

[Quote=引用 13 楼 dream1231 的回复:]
引用 9 楼 sql77 的回复:
SQL codeselectMAX( t2.region)from
    table1 t1,table2 t2where
    t1.userid=t2.useridand   
    t1.usersize=3groupby
    t2.region


MAX可以这样用的吗？
[/Quote]不能.

[--小F--]

[Quote=引用 13 楼 dream1231 的回复:]
引用 9 楼 sql77 的回复:
SQL codeselectMAX( t2.region)from
    table1 t1,table2 t2where
    t1.userid=t2.useridand   
    t1.usersize=3groupby
    t2.region


MAX可以这样用的吗？
[/Quote]

这个还需要嵌套一层

[urdoom]

declare @tb1 table (userid int,usersize int)
declare @tb2 table (userid int, region char(3))

insert into @tb1
select 1,1 union all
select 2,3 union all
select 3,3 union all
select 4,1 union all
select 5,2 union all
select 6,3 union all
select 7,2 union all
select 8,3 union all
select 9,3

insert into @tb2
select 1,'aaa' union all
select 2,'aaa' union all
select 3,'aaa' union all
select 4,'bbb' union all
select 5,'bbb' union all
select 6,'bbb' union all
select 7,'ccc' union all
select 8,'ccc' union all
select 9,'ccc'

select num, region from (
	select count(userid) as num,
		   region,
		   dense_rank() over(order by count(userid) desc) as rank_no
	from @tb2
	where userid in (
		   select userid
		   from @tb1 
		   where usersize = 3)
	group by region 
	) aaa
where aaa.rank_no = 1

num         region
----------- ------
2           aaa
2           ccc

(2 行処理されました)

还要考虑到有并列第一的情况
不能用top 1