难：应该有人问过的，有难度的字符串拼接查询！！！！

[zcwmxn]

有数据：
pub_users表
-----------------------
userno username password
000000 aaa b
000001 bbb b
000002 ccc b

SYS_UsersToRoles表
------------------------
userno roleno
000002 00001
000002 00002
000001 00001
000001 00004

期望得到结果
-----------
userno username password roleno
000000 aaa b __
000001 bbb b 00001,00004
000002 ccc b 00001,00002


---自己写的差距比较大的脚本------------
SELECT
u.userno, logname,urole.roleno
FROM
PUB_USERS as u
left join dbo.SYS_UsersToRoles as Urole
on Urole.userno = u.userno

==========================================
测试数据脚本：
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[pub_users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE pub_users
(
userno pub_userno PRIMARY KEY CLUSTERED ,

logname pub_username NOT NULL default(''),
password varchar(80) NOT NULL default(''),

)
GO

IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[SYS_UsersToRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE SYS_UsersToRoles
(
userNO pub_userno not null,
roleNO char(5) NOT NULL,
)
GO

insert into pub_users (userno,logname,password)
values('000000','aaa','b')
GO
insert into pub_users (userno,logname,password)
values ('000001','bbb','b')
GO
insert into pub_users (userno,logname,password)
values ('000002','ccc','b')
GO

insert into dbo.SYS_UsersToRoles (userno,roleNO)
values ('000002','00001')
insert into dbo.SYS_UsersToRoles (userno,roleNO)
values ('000002','00002')
GO

[bill024]

楼上的应该改一点：
create table pub_users(userno varchar(10),username varchar(10),password varchar(20))
insert pub_users select '000000','aaa','b'
union all select '000001','bbb','b'
union all select '000002','ccc','b'

create table UsersToRoles(userno varchar(10),roleno varchar(10))
insert UsersToRoles select '000002','00001'
union all select '000002','00002'
union all select '000001','00001'
union all select '000001','00004'

create function dbo.f_strJoin(@userno varchar(100))
returns varchar(500)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+','+roleno from UsersToRoles where userno=@userno
set @s=stuff(@s,1,1,'')
return @s
end
go

select A.userno , A.username,A.password,isnull(B.roleno,'--') as roleno
from pub_users A
left join (select userno ,dbo.f_strJoin(userno) as roleno from UsersToRoles group by userno) B
on A.userno=B.userno

userno username password roleno
---------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
000000 aaa b --
000001 bbb b 00001,00004
000002 ccc b 00001,00002

（所影响的行数为 3 行）

[砸死牛顿的苹果]

恩 我一般也用自定义函数

[playwarcraft]

--是SQL2000嗎?這樣的字串拼接,一般需要寫function
GO
create function fn_test(@userno varchar(20))
returns varchar(100)
AS
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+roleno from SYS_UsersToRoles where userno=@userno
if len(@str)>1
set @str=stuff(@str,1,1,'')
else
set @str='--'

return @str
end

GO

select A.userno , A.username,A.password,B.roleno
from pub_users A
left join (select userno ,dbo.fn_test(userno) as roleno from SYS_UsersToRoles group by userno) B
on A.userno=B.userno