HAVING SUM 求助

[sunyesy]

CREATE TABLE [dbo].[TEMPTEST](
	[T01] [nchar](10) NULL,
	[T02] [numeric](18, 0) NULL
) ON [PRIMARY]


INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('A',32)
INSERT INTO TEMPTEST VALUES ('A',41)
INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('A',14)
INSERT INTO TEMPTEST VALUES ('A',27)

SELECT * FROM TEMPTEST 

想得出这样的结果
条件：SUM(T02)=11 的数据
即：数据表中的T02列求和=某一个数值 
怎么写SQL,想到Having ..
//---------------
T01 T02
A    1
A    10

[Ginnnnnnnn]

SELECT T01,SUM(T02) FROM TEMPTEST GROUP BY T01 HAVING SUM(T02) = 11

[sunyesy]

引用 1 楼 KanzakiOrange 的回复:

SELECT T01,SUM(T02) FROM TEMPTEST GROUP BY T01 HAVING SUM(T02) = 11

开始这样写，但结果出不来 SUM(T02) = 11 好像不起作用

[Ginnnnnnnn]

根据你的测试数据给出你想要的结果吧，不然你这个我猜不出

[spiritofdragon]

如果你想要的是明细

T01 T02
A    1
A    10

那么我这么理解是你想要的么？

CREATE TABLE [dbo].[TEMPTEST](
    [T01] [nchar](10) NULL,
    [T02] [numeric](18, 0) NULL
) ON [PRIMARY]
INSERT INTO TEMPTEST VALUES ('A',1)
INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('B',41)
INSERT INTO TEMPTEST VALUES ('B',10)
INSERT INTO TEMPTEST VALUES ('B',14)
INSERT INTO TEMPTEST VALUES ('C',27)
SELECT * FROM (SELECT *,SUM(T02)over(partition by T01) s FROM TEMPTEST)T WHERE s=11

[道素]

DECLARE  @total int=11
select t.T01,case when isnull(prev_t.LastTotal,0)+t.T02<=@total then t.T02 else @total-isnull(prev_t.LastTotal,0) end as T02
from TEMPTEST t
OUTER APPLY (select sum(tt.T02) as LastTotal from TEMPTEST as tt where tt.ID<t.id and tt.T01=t.T01) prev_t
where isnull(prev_t.LastTotal,0)<@total

11时： A 10 A 1 111时： A 10 A 32 A 41 A 10 A 14 A 4

[道素]

你的意思是不是按照顺序分摊一个数值啊，查看的分摊情况？ 按下面如果是11用两行，如果是111返回六行

CREATE TABLE [dbo].[TEMPTEST](
    ID int IDENTITY (1,1) not null,
    [T01] [nchar](10) NULL,
    [T02] [numeric](18, 0) NULL
) ON [PRIMARY]


INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('A',32)
INSERT INTO TEMPTEST VALUES ('A',41)
INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('A',14)
INSERT INTO TEMPTEST VALUES ('A',27)


DECLARE  @total int=11
select prev_t.*,t.*,case when isnull(prev_t.LastTotal,0)+t.T02<=@total then t.T02 else @total-isnull(prev_t.LastTotal,0) end
from TEMPTEST t
OUTER APPLY (select sum(tt.T02) as LastTotal from TEMPTEST as tt where tt.ID<t.id) prev_t
where isnull(prev_t.LastTotal,0)<@total

[sunyesy]

引用 4 楼 spiritofdragon 的回复:

如果你想要的是明细

T01 T02
A    1
A    10

那么我这么理解是你想要的么？

CREATE TABLE [dbo].[TEMPTEST](
    [T01] [nchar](10) NULL,
    [T02] [numeric](18, 0) NULL
) ON [PRIMARY]
INSERT INTO TEMPTEST VALUES ('A',1)
INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('B',41)
INSERT INTO TEMPTEST VALUES ('B',10)
INSERT INTO TEMPTEST VALUES ('B',14)
INSERT INTO TEMPTEST VALUES ('C',27)
SELECT * FROM (SELECT *,SUM(T02)over(partition by T01) s FROM TEMPTEST)T WHERE s=11

嗯嗯，谢谢，就是这样，非常感谢，原来是这样啊，还能把SELECT 当中SUM的字段当做一个条件，灰常的感谢！

[sunyesy]

引用 5 楼 ch21st 的回复:

你的意思是不是按照顺序分摊一个数值啊，查看的分摊情况？ 按下面如果是11用两行，如果是111返回六行

CREATE TABLE [dbo].[TEMPTEST](
    ID int IDENTITY (1,1) not null,
    [T01] [nchar](10) NULL,
    [T02] [numeric](18, 0) NULL
) ON [PRIMARY]


INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('A',32)
INSERT INTO TEMPTEST VALUES ('A',41)
INSERT INTO TEMPTEST VALUES ('A',10)
INSERT INTO TEMPTEST VALUES ('A',14)
INSERT INTO TEMPTEST VALUES ('A',27)


DECLARE  @total int=11
select prev_t.*,t.*,case when isnull(prev_t.LastTotal,0)+t.T02<=@total then t.T02 else @total-isnull(prev_t.LastTotal,0) end
from TEMPTEST t
OUTER APPLY (select sum(tt.T02) as LastTotal from TEMPTEST as tt where tt.ID<t.id) prev_t
where isnull(prev_t.LastTotal,0)<@total

谢谢，让我大开眼界~~学到了好多~~

[sunyesy]

引用 6 楼 ch21st 的回复:

DECLARE  @total int=11
select t.T01,case when isnull(prev_t.LastTotal,0)+t.T02<=@total then t.T02 else @total-isnull(prev_t.LastTotal,0) end as T02
from TEMPTEST t
OUTER APPLY (select sum(tt.T02) as LastTotal from TEMPTEST as tt where tt.ID<t.id and tt.T01=t.T01) prev_t
where isnull(prev_t.LastTotal,0)<@total

11时： A 10 A 1 111时： A 10 A 32 A 41 A 10 A 14 A 4

再次回来感谢！ 这么强大~~