TEST表
ID TYPE VALUE
01 A 50
02 B 40
03 C 20
04 A 30
05 C 13
select TYPE,SUM(VALUE) from TEST group by TYPE有没有一种办法只通过TYPE来分组,然后求出每个VALUE,上面的语句是通过聚合函数来求和,
比如说A的TYPE的VALUE为80, 但是我想求的是80是如何来的,求的是如何找出30,50这两个数
ID TYPE VALUE
01 A 50
02 B 40
03 C 20
04 A 30
05 C 13
select TYPE,SUM(VALUE) from TEST group by TYPE有没有一种办法只通过TYPE来分组,然后求出每个VALUE,上面的语句是通过聚合函数来求和,
比如说A的TYPE的VALUE为80, 但是我想求的是80是如何来的,求的是如何找出30,50这两个数
create table tb(ID varchar(20),[TYPE] varchar(20),[VALUE] int)
insert into tb
select '01','A',50 union all
select '02','B',40 union all
select '03','C',20 union all
select '04','A',30 union all
select '05','C',13
goselect [TYPE],sum([VALUE]) [VALUE],
stuff((select distinct ','+ID from tb where [TYPE] = t.[TYPE] for xml path('')),1,1,'') as [ID来源]
from tb t
group by [TYPE]drop table tb
/**********TYPE VALUE ID来源
-------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A 80 01,04
B 40 02
C 33 03,05(3 行受影响)
select TYPE,MAX(VALUE),MIN(VALUE)from TEST group by TYPE但是有两个以上的话就没发求了,等高人指点迷津,小弟不胜感激.
stuff((select ' '+ltrim(VALUE) from TEST a where a.TYPE=TEST.TYPE for xml path('')),1,1,'')
from TEST group by TYPE
create table tb(ID varchar(10),[TYPE] varchar(20),[VALUE] int)
insert into tb
select '01','A',50 union all
select '02','B',40 union all
select '03','C',20 union all
select '04','A',30 union all
select '05','C',13
goselect [TYPE],sum([VALUE]) [VALUE],
stuff((select distinct ','+ID from tb where [TYPE] = t.[TYPE] for xml path('')),1,1,'') as [ID来源],
ltrim(sum([VALUE]))+'='
+stuff((select distinct '+'+ltrim([VALUE]) from tb where [TYPE] = t.[TYPE] for xml path('')),1,1,'') [结果]
from tb t
group by [TYPE]drop table tb
/************TYPE VALUE ID来源 结果
-------------------- ----------- ----------------------------------------- -------------------------------------------------
A 80 01,04 80=30+50
B 40 02 40=40
C 33 03,05 33=13+20(3 行受影响)
create table TEST表(ID varchar(2),TYPE varchar(1),VALUE int)
insert into TEST表
select '01','A',50 union all
select '02','B',40 union all
select '03','C',20 union all
select '04','A',30 union all
select '05','C',13
go
create function dbo.f_str_test(@TYPE nvarchar(20))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + '+' + ltrim(value) from TEST表 where TYPE=@TYPE
return stuff(@r, 1, 1, '')
end
go
-- 调用函数
SELECt TYPE,[sum]=sum(value), zuhe=dbo.f_str_test(TYPE)
FROM TEST表 GROUP BY TYPE
/*
TYPE sum zuhe
---- ----------- ---------
A 80 50+30
B 40 40
C 33 20+13
*/
/*
TYPE sum a1 a2
---- ----------- ---------
A 80 50 30
B 40 40
C 33 20 13
*/
我想要这样的
insert into tb
select '01','A',50 union all
select '02','B',40 union all
select '03','C',20 union all
select '04','A',30 union all
select '05','C',13
go
select *,[sum]=(select SUM([VALUE]) from tb where [TYPE]=a.[TYPE])
from tb a/*
ID TYPE VALUE sum
-------------------- -------------------- ----------- -----------
01 A 50 80
02 B 40 40
03 C 20 33
04 A 30 80
05 C 13 33
通过TYPE分组,让VALUE 行转列~~
insert into tb
select '01','A',50 union all
select '02','B',40 union all
select '03','C',20 union all
select '04','A',30 union all
select '05','C',13
go
select [TYPE],[sum]=SUM([VALUE]),a1=min([VALUE]),a2=MAX([VALUE])
from tb group by [TYPE]/*
TYPE sum a1 a2
-------------------- ----------- ----------- -----------
A 80 30 50
B 40 40 40
C 33 13 20
create table tb(ID varchar(10),[TYPE] varchar(20),[VALUE] int)
insert into tb
select '01','A',50 union all
select '02','B',40 union all
select '03','C',20 union all
select '04','A',30 union all
select '05','C',13
godeclare @sql varchar(max)select [TYPE],[VALUE],
(select sum([VALUE]) from tb where [TYPE] = t.[TYPE]) csum,
rid=row_number() over (partition by [TYPE] order by getdate())
into #tb
from tb tset @sql = 'select [TYPE],csum'
select @sql = @sql + ',max(case rid when ' + ltrim(rid) + ' then ltrim([VALUE]) else '''' end) [a' + ltrim(rid) + ']'
from (select distinct rid from #tb)t
select @sql = @sql + ' from #tb group by [TYPE],csum'
exec(@sql)drop table tb,#tb
/***********TYPE csum a1 a2
-------------------- ----------- ------------ ------------
C 33 20 13
B 40 40
A 80 50 30(3 行受影响)
大哥,A的TYPE有3个以上你怎么求啊??