select sum(value) from ( select * from tb A where not exists (select 1 from tb B where A.funid=B.funid and A.value<B.value) )T
CREATE TABLE #tp ( id INT IDENTITY, funid INT, VALUE INT )
INSERT INTO #tp SELECT 1,100 INSERT INTO #tp SELECT 5,200 INSERT INTO #tp SELECT 6,300 INSERT INTO #tp SELECT 1,400 INSERT INTO #tp SELECT 5,600SELECT * FROM #tp t WHERE NOT EXISTS(SELECT NULL FROM #tp WHERE t.funid=funid AND t.[VALUE]<VALUE)id funid VALUE ----------- ----------- ----------- 3 6 300 4 1 400 5 5 600(3 row(s) affected)
SELECT SUMValue=SUM(t.[VALUE]) FROM #tp t WHERE NOT EXISTS(SELECT NULL FROM #tp WHERE t.funid=funid AND t.[VALUE]<VALUE)SUMValue ----------- 1300(1 row(s) affected)
--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(id int, funid int, value int) insert into # select 1, 1, 100 union all select 2, 5, 200 union all select 3, 6, 300 union all select 4, 1, 400 union all select 5, 5, 600-- 相同 funid 的 value 没有重复最大值 select sum(value) from # t where not exists (select 1 from # where funid=t.funid and value>t.value)-- 否则 select sum(value) from (select funid, max(value)value from # group by funid) t/* 1300 */
select sum(t.value) from a t where value = (select max(value) from a where funid = t.funid)select sum(t.value) from a t where not exists (select 1 from a where funid = t.funid and value > t.value)
CREATE TABLE a ( id INT IDENTITY, funid INT, VALUE INT )
INSERT INTO a SELECT 1,100 INSERT INTO a SELECT 5,200 INSERT INTO a SELECT 6,300 INSERT INTO a SELECT 1,400 INSERT INTO a SELECT 5,600select sum(t.value) from a t where value = (select max(value) from a where funid = t.funid)select sum(t.value) from a t where not exists (select 1 from a where funid = t.funid and value > t.value)drop table a/*
select * from tb A where not exists (select 1 from tb B where A.funid=B.funid and A.value<B.value) --总和 select funid,sum(case when value=max(value) then value else 0 end) from tb group by funid
if object_id('tempdb.dbo.#') is not null drop table # create table #(id int, funid int, value int) insert into # select 1, 1, 100 union all select 2, 5, 200 union all select 3, 6, 300 union all select 4, 1, 400 union all select 5, 5, 600 select funid,SUM(value) [总和],MAX(value) [最大值] from # group by funidfunid 总和 最大值 1 500 400 5 800 600 6 300 300
from (
select *
from tb A
where not exists (select 1 from tb B where A.funid=B.funid and A.value<B.value)
)T
(
id INT IDENTITY,
funid INT,
VALUE INT
)
INSERT INTO #tp SELECT 1,100
INSERT INTO #tp SELECT 5,200
INSERT INTO #tp SELECT 6,300
INSERT INTO #tp SELECT 1,400
INSERT INTO #tp SELECT 5,600SELECT * FROM #tp t
WHERE NOT EXISTS(SELECT NULL FROM #tp WHERE t.funid=funid AND t.[VALUE]<VALUE)id funid VALUE
----------- ----------- -----------
3 6 300
4 1 400
5 5 600(3 row(s) affected)
WHERE NOT EXISTS(SELECT NULL FROM #tp WHERE t.funid=funid AND t.[VALUE]<VALUE)SUMValue
-----------
1300(1 row(s) affected)
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, funid int, value int)
insert into #
select 1, 1, 100 union all
select 2, 5, 200 union all
select 3, 6, 300 union all
select 4, 1, 400 union all
select 5, 5, 600-- 相同 funid 的 value 没有重复最大值
select sum(value) from # t where not exists (select 1 from # where funid=t.funid and value>t.value)-- 否则
select sum(value) from (select funid, max(value)value from # group by funid) t/*
1300
*/
(
id INT IDENTITY,
funid INT,
VALUE INT
)
INSERT INTO a SELECT 1,100
INSERT INTO a SELECT 5,200
INSERT INTO a SELECT 6,300
INSERT INTO a SELECT 1,400
INSERT INTO a SELECT 5,600select sum(t.value) from a t where value = (select max(value) from a where funid = t.funid)select sum(t.value) from a t where not exists (select 1 from a where funid = t.funid and value > t.value)drop table a/*
-----------
1300(所影响的行数为 1 行)
-----------
1300(所影响的行数为 1 行)
*/
select * from tb A
where not exists (select 1 from tb B where A.funid=B.funid and A.value<B.value) --总和
select funid,sum(case when value=max(value) then value else 0 end) from tb group by funid
create table #(id int, funid int, value int)
insert into #
select 1, 1, 100 union all
select 2, 5, 200 union all
select 3, 6, 300 union all
select 4, 1, 400 union all
select 5, 5, 600
select funid,SUM(value) [总和],MAX(value) [最大值]
from #
group by funidfunid 总和 最大值
1 500 400
5 800 600
6 300 300