求一简单sql!

[Even713]

有以下两个表：

users:
userid,username
1,'小红'
2,'小明'
3,'小李'

score:
id,userid,subject,score
1,1,'语文',80
2,1,'数学',98
3,1,'英语',60
4,1,'地理',70
5,2,'生物',78
6,2,'语文',65
7,3,'历史',66

用怎样的sql语句得到以下这样一个表：

userid,username,subject_score
1,'小红','语文：80 数学：98 英语：60 地理：70'
2,'小明','生物：78 语文：65'
3,'小李','历史：66'

谢谢大家了！

[华夏小卒]

---------------------------------------------
--> Author : js_szy							
--> Target : ★★★						
--> Date   : 2009-12-21 11:36:42
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: [users]
if object_id('[users]') is not null drop table [users]
go
create table [users] (userid int,username varchar(4))
insert into [users]
select 1,'小红' union all
select 2,'小明' union all
select 3,'小李'
---------------------------------------------
--> Author : js_szy							
--> Target : ★★★						
--> Date   : 2009-12-21 11:36:42
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: [score]
if object_id('[score]') is not null drop table [score]
go
create table [score] (id int,userid int,subject varchar(4),score int)
insert into [score]
select 1,1,'语文',80 union all
select 2,1,'数学',98 union all
select 3,1,'英语',60 union all
select 4,1,'地理',70 union all
select 5,2,'生物',78 union all
select 6,2,'语文',65 union all
select 7,3,'历史',66



if object_id('f_str') is not null drop function f_str
go
create function f_str(@id int)
returns varchar(100)
as
begin 
	declare @s varchar(100)
	
	select @s=isnull(@s+' ','')+subject+':'+ltrim(score) from score where userid=@id
	return @s
end
go 

select a.userid,a.username,subject_score=dbo.f_str(a.userid)
from [users] a,
[score] b
where a.userid=b.userid
group by a.userid,a.username



userid      username subject_score
----------- -------- ----------------------------------------------------------------------------------------------------
1           小红       语文:80 数学:98 英语:60 地理:70
2           小明       生物:78 语文:65
3           小李       历史:66

(3 行受影响)

[dawugui]

create table users(userid int,username varchar(10))
insert into users values(1,'小红') 
insert into users values(2,'小明') 
insert into users values(3,'小李') 
create table score(id int,userid int,subject varchar(10),score int)
insert into score values(1,1,'语文',80) 
insert into score values(2,1,'数学',98) 
insert into score values(3,1,'英语',60) 
insert into score values(4,1,'地理',70) 
insert into score values(5,2,'生物',78) 
insert into score values(6,2,'语文',65) 
insert into score values(7,3,'历史',66)
go

--如果你的每个学生最大四门课程，则用如下静态SQL。
select m.userid ,m.username,
  max(case n.px when 1 then n.subject else '' end) 'subject1',
  max(case n.px when 1 then cast(n.score as varchar) else '' end) 'score1',
  max(case n.px when 2 then n.subject else '' end) 'subject2',
  max(case n.px when 2 then cast(n.score as varchar) else '' end) 'score2',
  max(case n.px when 3 then n.subject else '' end) 'subject3',
  max(case n.px when 3 then cast(n.score as varchar) else '' end) 'score3',
  max(case n.px when 4 then n.subject else '' end) 'subject4',
  max(case n.px when 4 then cast(n.score as varchar) else '' end) 'score4'
from users m,
(select * , px = (select count(1) from [score] where userid = t.userid and id < t.id) + 1 from [score] t) n
where m.userid = n.userid
group by m.userid ,m.username
order by m.userid ,m.username
/*
userid      username   subject1   score1                         subject2   score2                         subject3   score3                         subject4   score4                         
----------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 
1           小红         语文         80                             数学         98                             英语         60                             地理         70
2           小明         生物         78                             语文         65                                                                                  
3           小李         历史         66                                                                                                                            

（所影响的行数为 3 行）
*/

--如果你的每个学生最大课程数不定，则用如下动态SQL。
declare @sql varchar(8000)
set @sql = 'select m.userid ,m.username '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then subject else '''' end) [subject' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then cast(score as varchar) else '''' end) [score' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from [score] where userid = t.userid and id < t.id) + 1 from [score] t) p) as a
set @sql = @sql + 'from users m,
(select * , px = (select count(1) from [score] where userid = t.userid and id < t.id) + 1 from [score] t) n where m.userid = n.userid group by m.userid ,m.username order by m.userid ,m.username'
exec(@sql) 
/*
userid      username   subject1   score1                         subject2   score2                         subject3   score3                         subject4   score4                         
----------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 
1           小红         语文         80                             数学         98                             英语         60                             地理         70
2           小明         生物         78                             语文         65                                                                                  
3           小李         历史         66                                                                                                                            

（所影响的行数为 3 行）
*/

drop table  users ,score

[guguda2008]

写个函数的

IF OBJECT_ID('users') IS NOT NULL DROP TABLE users
IF OBJECT_ID('score') IS NOT NULL DROP TABLE score
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
GO 
CREATE TABLE USERS(USERID INT,USERNAME VARCHAR(10))
INSERT INTO USERS
SELECT 1,'小红' UNION ALL
SELECT 2,'小明' UNION ALL
SELECT 3,'小李' 
CREATE TABLE SCORE(ID INT,USERID INT,SUBJECT VARCHAR(10),SCORE INT)
INSERT INTO SCORE
SELECT 1,1,'语文',80 UNION ALL
SELECT 2,1,'数学',98 UNION ALL
SELECT 3,1,'英语',60 UNION ALL
SELECT 4,1,'地理',70 UNION ALL
SELECT 5,2,'生物',78 UNION ALL
SELECT 6,2,'语文',65 UNION ALL
SELECT 7,3,'历史',66 
GO
CREATE FUNCTION FUN_MU(@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+' ','')+SUBJECT+':'+CONVERT(VARCHAR(10),SCORE) 
FROM SCORE WHERE USERID=@ID
RETURN @STR
END
GO
SELECT USERID,USERNAME,DBO.FUN_MU(USERID) 'subject_score ' FROM USERS
/*
USERID      USERNAME   subject_score 
----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           小红         语文:80 数学:98 英语:60 地理:70
2           小明         生物:78 语文:65
3           小李         历史:66
*/

[chuifengde]

CREATE TABLE users (userid int,username VARCHAR(20)) 
insert users select 1,'小红' 
union all select 2,'小明' 
union all select 3,'小李' 
go
CREATE TABLE score(id int,userid int,SUBJECT VARCHAR(20),score INT) 
insert score select 1,1,'语文',80 
union all select 2,1,'数学',98 
union all select 3,1,'英语',60 
union all select 4,1,'地理',70 
union all select 5,2,'生物',78 
union all select 6,2,'语文',65 
union all select 7,3,'历史',66 
go
create FUNCTION getR(@userId INT)
RETURNS VARCHAR(1000)
AS 
BEGIN
	DECLARE @sql VARCHAR(1000)
	
	SELECT @sql=ISNULL(@sql+' ','')+ SUBJECT+':'+LTRIM(score) 
	FROM score 
	WHERE userId=@userId 
	ORDER BY id 
	
	RETURN @sql 
END 
go
SELECT a.UserId,username,dbo.getR(a.userid) subject_Score
FROM users a LEFT JOIN score b
ON a.userid=b.userid
GROUP BY a.userid,a.username


--result
/*UserId      username             subject_Score                  
----------- -------------------- ------------------------------ 
1           小红                   语文:80 数学:98 英语:60 地理:70
2           小明                   生物:78 语文:65
3           小李                   历史:66

（所影响的行数为 3 行）

*/

[stefanhf]

小弟刚学数据库，请多支持

[dawugui]

create table users(userid int,username varchar(10))
insert into users values(1,'小红') 
insert into users values(2,'小明') 
insert into users values(3,'小李') 
create table score(id int,userid int,subject varchar(10),score int)
insert into score values(1,1,'语文',80) 
insert into score values(2,1,'数学',98) 
insert into score values(3,1,'英语',60) 
insert into score values(4,1,'地理',70) 
insert into score values(5,2,'生物',78) 
insert into score values(6,2,'语文',65) 
insert into score values(7,3,'历史',66)
go

create function dbo.f_str(@userid int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(subject as varchar) + ':' + cast(score as varchar) from score where userid = @userid
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select m.* ,n.subject_score from users m,
(select userid , subject_score  = dbo.f_str(userid) from score group by userid) n
where m.userid = n.userid order by m.userid 

drop function dbo.f_str
drop table  users ,score

/*
userid      username   subject_score                                                                                        
----------- ---------- ---------------------------------------------------------------------------------------------------- 
1           小红         语文:80,数学:98,英语:60,地理:70
2           小明         生物:78,语文:65
3           小李         历史:66

（所影响的行数为 3 行）
*/

--其他方法见下:

/*
标题：按某字段合并字符串之一(简单合并)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-06
地点：广东深圳

描述：将如下形式的数据按id字段合并value字段。
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     value
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

create function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(value as varchar) from tb where id = @id
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select id , value = dbo.f_str(id) from tb group by id

drop function dbo.f_str
drop table tb


--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by id

drop table tb


--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
    if @id = @id_old
       select @s = @s + ',' + cast(@value as varchar)
    else
      begin
        insert @t values(@id_old , stuff(@s,1,1,''))
        select @s = ',' + cast(@value as varchar) , @id_old = @id
      end
    fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursor

select * from @t
drop table tb

[smntbk]

-create function
if object_id('getStr') is not null drop function getStr
go
create function getStr(@id INT)
returns varchar(200)
as
begin
declare @str varchar(200);
set @str = '';
select @str = @str + subject + ': '+ Ltrim(score) + ' ' from score where userid = @id
return @str
end

--invoke
select userid,username,dbo.getStr(userid) as subject_score from users

--result
/*
userid username subject_score
----------- ---------------- ---------------------------------------------------
1 小红 语文: 80 数学: 98 英语: 60 地理: 70
2 小明 生物: 78 语文: 65
3 小李 历史: 66
*/

[smntbk]

--create function
if object_id('getStr') is not null drop function getStr
go
create function getStr(@id INT)
returns varchar(200)
as
begin
declare @str varchar(200);
set @str = '';
select @str = @str + subject + ': '+ Ltrim(score) + ' ' from score where userid = @id
return @str
end

--invoke
select userid,username,dbo.getStr(userid) as subject_score from users

--result
/*
userid      username        subject_score
----------- ---------------- ---------------------------------------------------
1          小红              语文: 80 数学: 98 英语: 60 地理: 70
2          小明              生物: 78 语文: 65
3          小李              历史: 66
*/

[syw_java]

又是个行列转换，学习

[--小F--]

/****************************************************************************************************************************************************** 
合并分拆表数据 

整理人：中国风(Roy) 

日期:2008.06.06 
******************************************************************************************************************************************************/ 

--> --> (Roy)生成測試數據 

if not object_id('Tab') is null 
    drop table Tab 
Go 
Create table Tab([Col1] int,[Col2] nvarchar(1)) 
Insert Tab 
select 1,N'a' union all 
select 1,N'b' union all 
select 1,N'c' union all 
select 2,N'd' union all 
select 2,N'e' union all 
select 3,N'f' 
Go 

合并表: 

SQL2000用函数: 

go 
if object_id('F_Str') is not null 
    drop function F_Str 
go 
create function F_Str(@Col1 int) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 
    return @S 
end 
go 
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab 

go 

SQL2005用XML: 

方法1: 

select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 
from 
    (select distinct COl1 from Tab) a 
Cross apply 
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 

方法2: 

select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 
from 
    (select distinct COl1 from Tab) a 
cross apply 
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 
                .query(' <Tab> 
                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 
                {concat("",string(/Tab[last()]/@COl2))} 
                </Tab>') 
                )b 

SQL05用CTE: 

;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 
,Roy2 as 
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 


生成结果: 
/* 
Col1        COl2 
----------- ------------ 
1          a,b,c 
2          d,e 
3          f 

(3 行受影响) 
*/ 


--参考
拆分表:

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go

--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','


SQL2005用Xml:

select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b




SQL05用CTE:

;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)

生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

[gxjwqm]

IF OBJECT_ID('users') IS NOT NULL DROP TABLE users
IF OBJECT_ID('score') IS NOT NULL DROP TABLE score
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
GO 
CREATE TABLE USERS(USERID INT,USERNAME VARCHAR(10))
INSERT INTO USERS
SELECT 1,'小红' UNION ALL
SELECT 2,'小明' UNION ALL
SELECT 3,'小李' 
CREATE TABLE SCORE(ID INT,USERID INT,SUBJECT VARCHAR(10),SCORE INT)
INSERT INTO SCORE
SELECT 1,1,'语文',80 UNION ALL
SELECT 2,1,'数学',98 UNION ALL
SELECT 3,1,'英语',60 UNION ALL
SELECT 4,1,'地理',70 UNION ALL
SELECT 5,2,'生物',78 UNION ALL
SELECT 6,2,'语文',65 UNION ALL
SELECT 7,3,'历史',66 
GO
CREATE FUNCTION FUN_MU(@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+' ','')+SUBJECT+':'+CONVERT(VARCHAR(10),SCORE) 
FROM SCORE WHERE USERID=@ID
RETURN @STR
END
GO
SELECT USERID,USERNAME,DBO.FUN_MU(USERID) 'subject_score ' FROM USERS
/*
USERID      USERNAME   subject_score 
----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           小红         语文:80 数学:98 英语:60 地理:70
2           小明         生物:78 语文:65
3           小李         历史:66
*/

[suzhende508]

select * from users u,score s where u.userid=s.userid

[懒虫虫]

真的是不简单！！

[profit_2009]

学习了啊。真是不错！

[profit_2009]

学习了

[ALLOHA1984]

CREATE TABLE PerfDataTable(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, AgentName TEXT NOT NULL, IPAddress TEXT NOT NULL, Port INTEGER NOT NULL, AgentTableName TEXT NOT NULL, StartTime TEXT NOT NULL, EndTime TEXT NOT NULL);
INSERT INTO PerfDataTable(AgentName,IPAddress,Port,AgentTableName,StartTime,EndTime) VALUES('Win32','10.70.107.77','5000','_10_70_107_77_5000','0','0');
CREATE TABLE _10_70_107_77_5000(CounterId TEXT PRIMARY KEY NOT NULL, CounterName TEXT NOT NULL, CounterDesc TEXT, CompressMode INTEGER NOT NULL, CounterDataTableName TEXT NOT NULL);

[ALLOHA1984]

CREATE TABLE _10_70_107_77_5000_4C34(time INTEGER PRIMARY KEY NOT NULL, value REAL NOT NULL);
CREATE TABLE _10_70_107_77_5000_4C36(time INTEGER PRIMARY KEY NOT NULL, value REAL NOT NULL);

[ALLOHA1984]

INSERT INTO _10_70_107_77_5000_4C24(time,value) VALUES('1268653396','461737984.000000'); INSERT INTO _10_70_107_77_5000_4C30(time,value) VALUES('1268653396','2016112640.000000'); INSERT INTO _10_70_107_77_5000_4C32(time,value) VALUES('1268653396','4.900000'); INSERT INTO _10_70_107_77_5000_4C34(time,value) VALUES('1268653396','122.300000'); INSERT INTO _10_70_107_77_5000_4C36(time,value) VALUES('1268653396','310.300000'); INSERT INTO _10_70_107_77_5000_4C38(time,value) VALUES('1268653396','504.700000'); INSERT INTO _10_70_107_77_5000_4C40(time,value) VALUES('1268653396','19.000000');

[ro19play]

相当不错啊，又懂了一点

[张炜的技术博客]

学了