求一条SQL，明早上线给分。

[ssc752000322]

有两个表，users表,usercenter表.users表主键users_id,usercenter表里主键usercenter_id,外键usercenter_userid,对应users表里的主键。users表和usercenter表为1对N的关系。求一SQL实现查出users表中的数据和usercenter表中对应的个数。
如users表
users_id
1
2
3
usercenter表
usercenter_id usercenter_userid
1 1
2 1
3 3
4 3
5 3
我要的结果为
users_id countnum
1 2
2 0
3 3
尽可能的优化哦~~~~
本人给分条件，第一个实现的，最优化的。如果没有实现，帮助最大者给分了哦，一点帮助没有就见者有分，哈哈，抢分了~~。
哦对了，数据库MYSQL。如果不熟MYSQL的，按MSSQL答吧，我会尽量改，

[dawugui]

select m.users_id , isnull(n.countnum,0) countnum from users m
left join
(select usercenter_userid , count(1) countnum from usercenter group by usercenter_userid) n
on m.users_id = n.usercenter_userid
order by m.users_id 

[dawugui]

create table users(users_id int)
insert into users values(1)
insert into users values(2)
insert into users values(3)
create table usercenter
(usercenter_id int,usercenter_userid int)
insert into usercenter values(1, 1)
insert into usercenter values(2, 1)
insert into usercenter values(3, 3)
insert into usercenter values(4, 3)
insert into usercenter values(5, 3)
go

select m.users_id , isnull(n.countnum,0) countnum from users m
left join
(select usercenter_userid , count(1) countnum from usercenter group by usercenter_userid) n
on m.users_id = n.usercenter_userid
order by m.users_id 

drop table users,usercenter

/*
users_id    countnum    
----------- ----------- 
1           2
2           0
3           3

（所影响的行数为 3 行）


*/

[dawugui]

如果需要MYSQL的语句,只需要把这里改了.

isnull(n.countnum,0) countnum

-->

不知道mysql的对应mssql的isnull

[htl258_Tony]

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-26 00:10:36
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:users

IF NOT OBJECT_ID('[users]') IS NULL
	DROP TABLE [users]
GO
CREATE TABLE [users]([users_id] INT)
INSERT [users]
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
GO
--SELECT * FROM [users]

--> 生成测试数据表:usercenter

IF NOT OBJECT_ID('[usercenter]') IS NULL
	DROP TABLE [usercenter]
GO
CREATE TABLE [usercenter]([usercenter_id] INT,[usercenter_userid] INT)
INSERT [usercenter]
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3
GO
--SELECT * FROM [usercenter]

-->SQL查询如下:
select [users_id],(select count(*) from [usercenter] where [usercenter_userid]=t.[users_id]) as countnum from [users] t
/*
users_id    countnum
----------- -----------
1           2
2           0
3           3

(3 行受影响)
*/

[dawugui]

--再提供一种写法.

create table users(users_id int)
insert into users values(1)
insert into users values(2)
insert into users values(3)
create table usercenter
(usercenter_id int,usercenter_userid int)
insert into usercenter values(1, 1)
insert into usercenter values(2, 1)
insert into usercenter values(3, 3)
insert into usercenter values(4, 3)
insert into usercenter values(5, 3)
go

--1
select m.users_id , isnull(n.countnum,0) countnum from users m
left join
(select usercenter_userid , count(1) countnum from usercenter group by usercenter_userid) n
on m.users_id = n.usercenter_userid
order by m.users_id 

--2
select m.users_id , (select count(1) from usercenter where usercenter_userid = m.users_id) countnum from users m

drop table users,usercenter

/*
users_id    countnum    
----------- ----------- 
1           2
2           0
3           3

（所影响的行数为 3 行）


*/

[永生天地]

select users_id, count(usercenter_userid) as countnum
from users left join usercenter
on users_id=usercenter_userid
group by users_id

[htl258_Tony]

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-26 00:10:36
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:users

IF NOT OBJECT_ID('[users]') IS NULL
	DROP TABLE [users]
GO
CREATE TABLE [users]([users_id] INT)
INSERT [users]
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
GO
--SELECT * FROM [users]

--> 生成测试数据表:usercenter

IF NOT OBJECT_ID('[usercenter]') IS NULL
	DROP TABLE [usercenter]
GO
CREATE TABLE [usercenter]([usercenter_id] INT,[usercenter_userid] INT)
INSERT [usercenter]
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3
GO
--SELECT * FROM [usercenter]

-->SQL查询如下:
select a.[users_id],SUM(case when b.usercenter_userid IS null then 0 else 1 end) as countnum 
from [users] as a
	left join [usercenter] as b
		on a.users_id=b.usercenter_userid
group by a.[users_id]
/*
users_id    countnum
----------- -----------
1           2
2           0
3           3

(3 行受影响)
*/

[Sybase数据库恢复]

还没睡觉的高手们， 晚安！

[ssc752000322]

偶也没睡，谢谢二位高手。。。。给分了。合不合理不要见怪，来分了吧。。。。