表T
FLID ZD CODE
1 500 1
2 30 1
1 700 2
2 70 2
查询结果显示 (flid =1 as flid1 ZD) (flid=2 as flid2 ZD) CODE
500 30 1
700 70 2
就是这个结构 真头疼。
FLID ZD CODE
1 500 1
2 30 1
1 700 2
2 70 2
查询结果显示 (flid =1 as flid1 ZD) (flid=2 as flid2 ZD) CODE
500 30 1
700 70 2
就是这个结构 真头疼。
原著:邹建
改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
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')
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/--2、另外一种函数.
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 f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
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 distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
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 * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc(2 行受影响)
*/--SQL2005中的方法2
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')
goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc(2 row(s) affected)*/drop table tb
--2000中使用如下的函数,2005的方法见上.
create table tb(fCode varchar(10) , fName varchar(10) ,fQty int,fRe varchar(10))
insert into tb values('001' , '红色' , 500 , '大码')
insert into tb values('001' , '红色' , 300 , '小码')
go
--创建一个合并的函数
create function f_hb(@fCode varchar(10),@fName varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '+' + cast(fRe as varchar) from tb where fCode=@fCode and fName = @fName
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct fCode ,fName , sum(fQty) fQty, dbo.f_hb(fCode ,fName) as fRee from tb group by fCode ,fNamedrop table tb
drop function dbo.f_hb/*
fCode fName fQty fRee
---------- ---------- ----------- ----------
001 红色 800 大码+小码(所影响的行数为 1 行)
-->Date :2009-09-07 10:43:19
if not object_id('t') is null
drop table t
Go
Create table t([FLID] int,[ZD] int,[CODE] int)
Insert t
select 1,500,1 union all
select 2,30,1 union all
select 1,700,2 union all
select 2,70,2
Go
select sum(case when [FLID]=1 then [ZD] else 0 end)flid1,
sum(case when [FLID]=2 then [ZD] else 0 end)flid2,
[CODE]
from t group by [CODE]
/*
flid1 flid2 CODE
----------- ----------- -----------
500 30 1
700 70 2(2 個資料列受到影響)*/
from t m , t n
where m.FLID = 1 and n.FLID = 2 and m.code = n.code
order by m.code
from t m , t n
where m.FLID = 1 and n.FLID = 2 and m.code = n.code
order by m.code
create table test(fild int,zd int,code int)
insert test
select 1,500,1 union all
select 2,30,1 union all
select 1,700,2 union all
select 2,70,2
goselect sum(case when fild=1 then zd else 0 end)flid1,
sum(case when fild=2 then zd else 0 end)flid2,
code
from test group by codeflid1 flid2 code
----------- ----------- -----------
500 30 1
700 70 2(所影响的行数为 2 行)
a.ZD AS flid1_ZD,
b.ZD as flid2_ZD,
a.code
from
(select zd,code from t where flid=1) a
join
(select zd,code from t where fild=2) b
on
a.code=b.code
insert into t values(1 , 500 , 1 )
insert into t values(2 , 30 , 1 )
insert into t values(1 , 700 , 2 )
insert into t values(2 , 70 , 2 )select m.ZD ZD1 , n.ZD ZD2 , m.code
from t m , t n
where m.FLID = 1 and n.FLID = 2 and m.code = n.code
order by m.codedrop table t/*
ZD1 ZD2 code
----------- ----------- -----------
500 30 1
700 70 2(所影响的行数为 2 行)
*/
select sum(case when [FLID]=1 then [ZD] else 0 end)flid1,
sum(case when [FLID]=2 then [ZD] else 0 end)flid2,
[CODE]
from t group by [CODE]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([FLID] int,[ZD] int,[CODE] int)
insert [T1]
select 1,500,1 union all
select 2,30,1 union all
select 1,700,2 union all
select 2,70,2select * from [T1]
--sql2005
select [1] as flid1 , [2] as flid2 ,CODE from t1
pivot
(
max(zd) for flid in([1],[2])
) p---sql2000select flid1=max(case when flid=1 then zd else 0 end),flid2=max(case when flid=2 then zd else 0 end),[CODE]
from t1 group by [CODE]/*
flid1 flid2 CODE
----------- ----------- -----------
500 30 1
700 70 2(2 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-07 10:47:22
-- 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]([FLID] int,[ZD] int,[CODE] int)
insert [tb]
select 1,500,1 union all
select 2,30,1 union all
select 1,700,2 union all
select 2,70,2
--------------开始查询--------------------------
select
[(flid =1 as flid1 ZD)]=sum(case FLID when 1 then ZD else 0 end),
[(flid=2 as flid2 ZD)]=sum(case FLID when 2 then ZD else 0 end),
code
from
tb
group by
code
----------------结果----------------------------
/*(flid =1 as flid1 ZD) (flid=2 as flid2 ZD) code
--------------------- -------------------- -----------
500 30 1
700 70 2(2 行受影响)
*/
-->Date :2009-09-07 10:43:19
if not object_id('t') is null
drop table t
Go
Create table t([FLID] int,[ZD] int,[CODE] int)
Insert t
select 1,500,1 union all
select 2,30,1 union all
select 1,700,2 union all
select 2,70,2
Go
declare @s varchar(4000)
set @s='select '
select @s=@s+'sum(case FLID when'''+ltrim([FLID])+'''then [ZD] else 0 end)[FLID'+ltrim([FLID])+'],'
from (select distinct [FLID] from t)t
set @s=@s+'[CODE] from t group by [CODE]'
exec( @s)
/*
FLID1 FLID2 CODE
----------- ----------- -----------
500 30 1
700 70 2(2 個資料列受到影響)*/