---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-09 10:49:55 -- 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]([name] varchar(4),[classname] varchar(6)) insert [tb] select '小明','铅笔' union all select '小明','作业纸' union all select '小红','钢笔' union all select '小红','铅笔' union all select '小明','小刀' --------------开始查询--------------------------select [name], [classname]=stuff((select ','+[classname] from tb t where [name]=tb.[name] for xml path('')), 1, 1, '') from tb group by [name] ----------------结果---------------------------- /* name classname ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 小红 钢笔,铅笔 小明 铅笔,作业纸,小刀(2 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-09 10:49:55 -- 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]([name] varchar(4),[classname] varchar(6)) insert [tb] select '小明','铅笔' union all select '小明','作业纸' union all select '小红','钢笔' union all select '小红','铅笔' union all select '小明','小刀' --------------开始查询--------------------------select [name], [classname]=stuff((select ','+[classname] from tb t where [name]=tb.[name] for xml path('')), 1, 1, '') from tb group by [name] order by 1 desc ----------------结果---------------------------- /* name classname ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 小明 铅笔,作业纸,小刀 小红 钢笔,铅笔(2 行受影响) */
create table A ( stuName varchar(10), stuClass varchar(10), stuScroe float, )insert into A (stuName,stuClass,stuScroe)values('张三','数学',80) insert into A (stuName,stuClass,stuScroe)values('张三','语文',70) insert into A (stuName,stuClass,stuScroe)values('张三','英语',60) insert into A (stuName,stuClass,stuScroe)values('李四','数学',90) insert into A (stuName,stuClass,stuScroe)values('李四','语文',80) insert into A (stuName,stuClass,stuScroe)values('王五','数学',95) insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)select stuName,数学,语文,英语,总计 from (select *,sum(stuScroe)over(partition by stuName) 总计 from a) a pivot(max(stuScroe) for stuClass in(数学,语文,英语))b参考这个吧
if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(4),[classname] varchar(6)) insert [tb] select '小明','铅笔' union all select '小明','作业纸' union all select '小红','钢笔' union all select '小红','铅笔' union all select '小明','小刀'CREATE FUNCTION DBO.GETclassname(@name varchar(4)) RETURNS VARCHAR(50) AS BEGIN DECLARE @classname NVARCHAR(20) SELECT @classname=ISNULL(@classname+',','')+classname FROM TB WHERE NAME=@NAME RETURN @classname ENDSELECT DISTINCT NAME,DBO.GETclassname(NAME)AS classname FROM TBNAME classname ---- -------------------------------------------------- 小红 钢笔,铅笔 小明 铅笔,作业纸,小刀(所影响的行数为 2 行)
我现在用的是sql2000,我在测试~
if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(4),[classname] varchar(6)) insert [tb] select '小明','铅笔' union all select '小明','作业纸' union all select '小红','钢笔' union all select '小红','铅笔' union all select '小明','小刀'CREATE FUNCTION DBO.GETclassname(@name varchar(4)) RETURNS VARCHAR(50) AS BEGIN DECLARE @classname NVARCHAR(20) SELECT @classname=ISNULL(@classname+',','')+classname FROM TB WHERE NAME=@NAME RETURN @classname END CREATE VIEW NEWTB AS SELECT DISTINCT NAME,DBO.GETclassname(NAME)AS classname FROM TBSELECT * FROM NEWTB?? 是这意思没?
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-09 10:49:55
-- 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]([name] varchar(4),[classname] varchar(6))
insert [tb]
select '小明','铅笔' union all
select '小明','作业纸' union all
select '小红','钢笔' union all
select '小红','铅笔' union all
select '小明','小刀'
--------------开始查询--------------------------select [name], [classname]=stuff((select ','+[classname] from tb t where [name]=tb.[name] for xml path('')), 1, 1, '')
from tb
group by [name]
----------------结果----------------------------
/* name classname
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
小红 钢笔,铅笔
小明 铅笔,作业纸,小刀(2 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-09 10:49:55
-- 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]([name] varchar(4),[classname] varchar(6))
insert [tb]
select '小明','铅笔' union all
select '小明','作业纸' union all
select '小红','钢笔' union all
select '小红','铅笔' union all
select '小明','小刀'
--------------开始查询--------------------------select [name], [classname]=stuff((select ','+[classname] from tb t where [name]=tb.[name] for xml path('')), 1, 1, '')
from tb
group by [name]
order by 1 desc
----------------结果----------------------------
/* name classname
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
小明 铅笔,作业纸,小刀
小红 钢笔,铅笔(2 行受影响)
*/
create table A
(
stuName varchar(10),
stuClass varchar(10),
stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)select stuName,数学,语文,英语,总计
from (select *,sum(stuScroe)over(partition by stuName) 总计 from a) a
pivot(max(stuScroe) for stuClass in(数学,语文,英语))b参考这个吧
go
create table [tb]([name] varchar(4),[classname] varchar(6))
insert [tb]
select '小明','铅笔' union all
select '小明','作业纸' union all
select '小红','钢笔' union all
select '小红','铅笔' union all
select '小明','小刀'CREATE FUNCTION DBO.GETclassname(@name varchar(4))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @classname NVARCHAR(20)
SELECT @classname=ISNULL(@classname+',','')+classname FROM TB WHERE NAME=@NAME
RETURN @classname
ENDSELECT DISTINCT NAME,DBO.GETclassname(NAME)AS classname FROM TBNAME classname
---- --------------------------------------------------
小红 钢笔,铅笔
小明 铅笔,作业纸,小刀(所影响的行数为 2 行)
go
create table [tb]([name] varchar(4),[classname] varchar(6))
insert [tb]
select '小明','铅笔' union all
select '小明','作业纸' union all
select '小红','钢笔' union all
select '小红','铅笔' union all
select '小明','小刀'CREATE FUNCTION DBO.GETclassname(@name varchar(4))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @classname NVARCHAR(20)
SELECT @classname=ISNULL(@classname+',','')+classname FROM TB WHERE NAME=@NAME
RETURN @classname
END
CREATE VIEW NEWTB
AS
SELECT DISTINCT NAME,DBO.GETclassname(NAME)AS classname FROM TBSELECT * FROM NEWTB??
是这意思没?