相同记录取最大值并且只显示一条 相关sql

[hncslsy]

取code 相同的记录中 values 最大的记录 ,如果 values 值有相同的 取 id 大的一条


id code values
1 a 2
2 a 3
3 a 3
4 b 1
5 b 4
6 b 4


显示结果

id code values
3 a 3
6 b 4

[liangCK]

SELECT * FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE code = A.code AND [values] > A.[values])
    AND NOT EXISTS(SELECT * FROM tb 
                    WHERE code = A.code AND [values]=A.[values] AND id > A.id)

[liangCK]

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-04 20:32:39
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,code VARCHAR(1),[values] INT)
INSERT INTO @T
SELECT 1,'a',2 UNION ALL
SELECT 2,'a',3 UNION ALL
SELECT 3,'a',3 UNION ALL
SELECT 4,'b',1 UNION ALL
SELECT 5,'b',4 UNION ALL
SELECT 6,'b',4 UNION ALL
SELECT 7,'c',1

--SQL查询如下:

--1
SELECT * FROM @T AS A
WHERE NOT EXISTS(SELECT * FROM @T WHERE code = A.code AND [values] > A.[values])
    AND NOT EXISTS(SELECT * FROM @T 
                    WHERE code = A.code AND [values]=A.[values] AND id > A.id);

--2
SELECT id,code,[values]
FROM (SELECT rowid=ROW_NUMBER() OVER(PARTITION BY code ORDER BY [values] DESC,id DESC),*
      FROM @T) AS T
WHERE rowid = 1;


/*
id          code values
----------- ---- -----------
3           a    3
6           b    4
7           c    1

(3 row(s) affected)

id          code values
----------- ---- -----------
3           a    3
6           b    4
7           c    1

(3 row(s) affected)
*/

[-狙击手-]

..

[百年树人]

select 
  *
from
  tb t
where
  not exists(select 1 fromm tb where code=t.code and (value>t.value or value=t.value and id>t.id))

[华夏小卒]

declare @tb table(id int, code varchar(10), [values] int)
insert @tb select
1,    'a'      ,  2 union all select 
2 ,   'a'     ,   3 union all select 
3  ,  'a'    ,    3  union all select
4   , 'b'   ,     1  union all select
5    ,'b'  ,      4  union all select
6    ,'b' ,       4 


select* from @tb t
where not exists (select * from @tb where code=t.code and ([values]>t.[values] or [values]=t.[values] and id>t.id))

id          code       values
----------- ---------- -----------
3           a          3
6           b          4

(2 行受影响)

[--小F--]

都写好了 那就学习了

[soft_wsx]

来晚了！撤吧！

[lihan6415151528]

重复数据一般用not exists 查询

[happinessdepig]

正好要解决这个问题,学习了!

[guguda2008]

懒得写了。前几楼正解

[zhengzeng]

来迟了。呵呵

[renzhe02]

2楼方法好！

[影帆]

学习了！！

[linpansheng]

学习这个，不错

[叶子]

declare @table table (id int,code varchar(1),[values] int)
insert into @table
select 1,'a',2 union all
select 2,'a',3 union all
select 3,'a',3 union all
select 4,'b',1 union all
select 5,'b',4 union all
select 6,'b',4

select max(id) as id,code,max([values]) as [values] 
from @table group by code

/*
id          code values
----------- ---- -----------
3           a    3
6           b    4
*/
--这样不行吗？你不是就要取最大的吗？
--重复也是取最大的，貌似和重复没有什么关系。