sql查询问题

Question

有一个表如下:
学号       课程名
1          语文
1          数学
1          物理
2          语文
2          数学
3          语文
3          物理
我想根据条件搜索,得到的是
1   语文,数学,物理
2    语文,数学
3   语文,物理如何通过一条语句得到上面的值?

Answer 1

CREATE  FUNCTION GET_STRING(@a INT)
RETURNS VARCHAR(50)
AS 
  BEGIN 
       DECLARE @CLASS VARCHAR(50)
       SELECT @CLASS=ISNULL(@CLASS+',','')+a FROM t WHERE 学号=@A
       RETURN @CLASS
  END]自己修改

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-29 13:46:06
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([学号] int,[课程名] varchar(4))
insert [tb]
select 1,'语文' union all
select 1,'数学' union all
select 1,'物理' union all
select 2,'语文' union all
select 2,'数学' union all
select 3,'语文' union all
select 3,'物理'
--------------开始查询--------------------------
select 学号, [课程名]=stuff((select ','+[课程名] from tb t where 学号=tb.学号 for xml path('')), 1, 1, '') 
from tb 
group by 学号
----------------结果----------------------------
/* 学号          课程名
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           语文,数学,物理
2           语文,数学
3           语文,物理(3 行受影响)
*/

Answer 3

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (学号 int,课程名 nvarchar(4))
insert into [tb]
select 1,N'语文' union all
select 1,N'数学' union all
select 1,N'物理' union all
select 2,N'语文' union all
select 2,N'数学' union all
select 3,N'语文' union all
select 3,N'物理'
SELECT 学号,
       课程名=STUFF((SELECT ','+课程名 FROM TB WHERE 学号=T.学号 FOR XML PATH('')),1,1,'')
FROM TB T
GROUP BY 学号
/*
学号          课程名
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           语文,数学,物理
2           语文,数学
3           语文,物理(3 個資料列受到影響)*/

Answer 4

--2000
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-29 13:46:06
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([学号] int,[课程名] varchar(4))
insert [tb]
select 1,'语文' union all
select 1,'数学' union all
select 1,'物理' union all
select 2,'语文' union all
select 2,'数学' union all
select 3,'语文' union all
select 3,'物理'
--------------开始查询--------------------------
CREATE FUNCTION dbo.f_strUnite(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + 课程名 FROM tb WHERE 学号=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECT 学号, 课程名 = dbo.f_strUnite(学号) FROM tb GROUP BY 学号
drop table tb 
drop function dbo.f_strUnite 
go
----------------结果----------------------------
/* 学号          课程名
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           语文,数学,物理
2           语文,数学
3           语文,物理(3 行受影响)
*/

Answer 5

------------------------------------------------------------------------
-- Author : HappyFlyStone 
-- Date   : 2009-12-29 13:47:30
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--       Apr 14 2006 01:12:25 
--       Copyright (c) 1988-2005 Microsoft Corporation
--       Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--      
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL 
    DROP TABLE [ta]
Go
CREATE TABLE ta([学号] INT,[课程名] NVARCHAR(2))
Go
INSERT INTO ta
   SELECT 1,'语文' UNION ALL
   SELECT 1,'数学' UNION ALL
   SELECT 1,'物理' UNION ALL
   SELECT 2,'语文' UNION ALL
   SELECT 2,'数学' UNION ALL
   SELECT 3,'语文' UNION ALL
   SELECT 3,'物理' 
GO
--Start
select 
学号, 
[values]=stuff((select ','+ltrim([课程名])
from ta t 
where 学号=ta.学号 for xml path('')), 1, 1, '') 
from ta 
group by [学号] --Result:
/*
学号          values
----------- -------------------
1           语文,数学,物理
2           语文,数学
3           语文,物理(3 行受影响)
*/
--End 

Answer 6

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([学号] int,[课程名] varchar(4))
insert [tb]
select 1,'语文' union all
select 1,'数学' union all
select 1,'物理' union all
select 2,'语文' union all
select 2,'数学' union all
select 3,'语文' union all
select 3,'物理'ALTER  FUNCTION GET_STRING(@学号 INT)
RETURNS VARCHAR(50)
AS 
  BEGIN 
       DECLARE @CLASS VARCHAR(50)
       SELECT @CLASS=ISNULL(@CLASS+',','')+课程名 FROM tB WHERE 学号=@学号
       RETURN @CLASS
  ENDSELECT 学号,DBO.GET_STRING(学号)AS 课程名 FROM TB GROUP BY 学号学号          课程名                                                
----------- -------------------------------------------------- 
1           语文,数学,物理
2           语文,数学
3           语文,物理（所影响的行数为 3 行）

Answer 7

use a function in your query