再问个简单的问题，在线等

weiyibdk 2011-07-01 03:37:56

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这两个数

...全文

91 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

weiyibdk 2011-07-01

打赏
举报

[Quote=引用 14 楼 zy112429 的回复:]
SQL code
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 u……
[/Quote]

大哥，A的TYPE有3个以上你怎么求啊？？

AcHerat 2011-07-01

打赏
举报



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

go



declare @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 t



set @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 行受影响)

qgqch2008 2011-07-01

打赏
举报

[Quote=引用 13 楼 weiyibdk 的回复:]

引用 11 楼 jamespanjunwei 的回复:
这不就是行转列然后累加吗

好像是哦。。

通过TYPE分组，让VALUE 行转列~~
[/Quote]行轉列。。。

GoAwayZ 2011-07-01

打赏
举报

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

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

weiyibdk 2011-07-01

打赏
举报

[Quote=引用 11 楼 jamespanjunwei 的回复:]
这不就是行转列然后累加吗
[/Quote]

好像是哦。。

通过TYPE分组，让VALUE 行转列~~

GoAwayZ 2011-07-01

打赏
举报

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

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

老潘 2011-07-01

打赏
举报

这不就是行转列然后累加吗

weiyibdk 2011-07-01

打赏
举报

大家的答案好像跟我的问题有些出入
/*
TYPE sum a1 a2
---- ----------- ---------
A 80 50 30
B 40 40
C 33 20 13
*/
我想要这样的

叶子 2011-07-01

打赏
举报



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

*/

AcHerat 2011-07-01

打赏
举报



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

go



select [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 行受影响)

cd731107 2011-07-01

打赏
举报

select TYPE,SUM(VALUE),

stuff((select ' '+ltrim(VALUE) from TEST a where a.TYPE=TEST.TYPE for xml path('')),1,1,'')

from TEST group by TYPE

weiyibdk 2011-07-01

打赏
举报

如果TYPE A的个数只有两个的话，我可以用MAX和MIN来实现

select TYPE,MAX(VALUE),MIN（VALUE）from TEST group by TYPE

但是有两个以上的话就没发求了，

等高人指点迷津，小弟不胜感激.

AcHerat 2011-07-01

打赏
举报



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

go



select [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 行受影响)