sql查询语句，在线等~~~~~~~~，谢谢

Question

CREATE TABLE [T1] ([code_emp_id] [int] IDENTITY (1, 1) NOT NULL ,[softwarecode] nvarchar(30),[username] nvarchar(50))
Insert into[T1] select 'ACURA','Simon Xue'
Insert into[T1] select 'ACURA','Allen Pan'
Insert into[T1] select 'ACURA','Fidel Luo'
Insert into[T1] select 'Alfa','Danfer Liu'
Insert into[T1] select 'Alfa','Fidel Luo'
Insert into[T1] select 'AMFORD','Brandon Gao'
Insert into[T1] select 'AMFORD','Allen Pan'                                           
/*需要的结果
车名(softwareCode)      操作者(userName)
ACURA          Simon Xue,Allen Pan,Fidel Luo
Alfa           Danfer Liu,Fidel Luo
AMFORD         Brandon Gao,Allen Pan
*/
drop table T1

Answer 1

--2005
select 
   softwareCode,
   stuff((select ','+userName
          from [T1] 
          where softwareCode=t.softwareCode for xml path('')),1,1,'')
from [T1] t
group by softwareCode

Answer 2

CREATE TABLE [T1] ([code_emp_id] [int] IDENTITY (1, 1) NOT NULL ,[softwarecode] nvarchar(30),[username] nvarchar(50))
Insert into[T1] select 'ACURA','Simon Xue'
Insert into[T1] select 'ACURA','Allen Pan'
Insert into[T1] select 'ACURA','Fidel Luo'
Insert into[T1] select 'Alfa','Danfer Liu'
Insert into[T1] select 'Alfa','Fidel Luo'
Insert into[T1] select 'AMFORD','Brandon Gao'
Insert into[T1] select 'AMFORD','Allen Pan'         Select distinct T.[softwarecode] [车名(softwareCode)],
stuff((select ','+username from t1 where t1.[softwarecode]=t.[softwarecode] for xml path('')),1,1,'') as [操作者(userName)]
from T1 T                     
车名(softwareCode)               操作者(userName)
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACURA                          Simon Xue,Allen Pan,Fidel Luo
Alfa                           Danfer Liu,Fidel Luo
AMFORD                         Brandon Gao,Allen Pan(3 行受影响)
drop table T1

Answer 3

CREATE FUNCTION dbo.f_str(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SET @r = '' 
    SELECT @r = @r + ',' + [username] FROM [T1] WHERE [code_emp_id]=@id 
    RETURN STUFF(@r, 1, 1, '') 
END 
GO -- 调用函数 
SELECt [code_emp_id], [username] = dbo.f_str([code_emp_id]) FROM [T1] GROUP BY [code_emp_id] 

Answer 4

--2000
CREATE FUNCTION dbo.f_str(@id varchar(20)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @r varchar(8000) 
    SET @r = '' 
    SELECT @r = @r + ',' + [username] FROM [T1] WHERE [softwarecode]=@id 
    RETURN STUFF(@r, 1, 1, '') 
END 
GO -- 调用函数 
SELECt [softwarecode], [username] = dbo.f_str([softwarecode]) FROM [T1] GROUP BY [softwarecode] 
/*
softwarecode                   username
------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACURA                          Simon Xue,Allen Pan,Fidel Luo
Alfa                           Danfer Liu,Fidel Luo
AMFORD                         Brandon Gao,Allen Pan(3 行受
*/
drop function  dbo.f_str
drop table T1

Answer 5

2000
CREATE TABLE [T1] ([code_emp_id] [int] IDENTITY (1, 1) NOT NULL ,[softwarecode] nvarchar(30),[username] nvarchar(50))
Insert into[T1] select 'ACURA','Simon Xue'
Insert into[T1] select 'ACURA','Allen Pan'
Insert into[T1] select 'ACURA','Fidel Luo'
Insert into[T1] select 'Alfa','Danfer Liu'
Insert into[T1] select 'Alfa','Fidel Luo'
Insert into[T1] select 'AMFORD','Brandon Gao'
Insert into[T1] select 'AMFORD','Allen Pan'         if exists(select 1 from sysobjects where id = object_id('getstr'))
drop function dbo.getstr
go
create function getstr(@id nvarchar(30))
returns  nvarchar(100)
as
begin
declare @s as nvarchar(100)
select @s= isnull(@s+',','')+[username] from T1 where [softwarecode]=@id
return @s
end
goSelect distinct T.[softwarecode] [车名(softwareCode)],
dbo.getstr([softwarecode])
from T1 T                     
车名(softwareCode)               
------------------------------ ----------------------------------------------------------------------------------------------------
ACURA                          Simon Xue,Allen Pan,Fidel Luo
Alfa                           Danfer Liu,Fidel Luo
AMFORD                         Brandon Gao,Allen Pan(3 行受影响)
drop table t1

Answer 6

if object_id('t1') is not null drop table t1
CREATE TABLE [T1] ([code_emp_id] [int] IDENTITY (1, 1) NOT NULL ,[softwarecode] nvarchar(30),[username] nvarchar(50))
Insert into[T1] select 'ACURA','Simon Xue'
Insert into[T1] select 'ACURA','Allen Pan'
Insert into[T1] select 'ACURA','Fidel Luo'
Insert into[T1] select 'Alfa','Danfer Liu'
Insert into[T1] select 'Alfa','Fidel Luo'
Insert into[T1] select 'AMFORD','Brandon Gao'
Insert into[T1] select 'AMFORD','Allen Pan'    select  softwarecode 操作者,
车名=stuff((select ','+username from t1 where softwarecode=a.softwarecode  for xml path('')),1,1,'')
from t1 a
group by softwarecode操作者      车名
ACURA Simon Xue,Allen Pan,Fidel Luo
Alfa Danfer Liu,Fidel Luo
AMFORD Brandon Gao,Allen Pan

Answer 7

楼上的都正确。SQL 2005推荐用这个：select 
   softwareCode,
   stuff((select ','+userName
          from [T1] 
          where softwareCode=t.softwareCode for xml path('')),1,1,'')
from [T1] t
group by softwareCode  
                 

Answer 8

--创建处理函数
create function dbo.test(@softwarecode varchar(20))
returns varchar(1000)
as
begin
    declare @a varchar(1000)
    set @a = ''
    select @a = @a + ',' + userName
    from t1
    where softwarecode=@softwarecode
    return stuff(@a, 1, 1, '')
end
go
-- 调用函数select  softwarecode as 车名, dbo.test(softwarecode) as 操作者
from  t1
group by softwarecode

Answer 9

select 
   softwareCode,
   stuff((select ','+userName
          from [T1] 
          where softwareCode=t.softwareCode for xml path('')),1,1,'')
from [T1] t
group by softwareCode