获取不同信息的最新信息解决办法

获取不同信息的最新信息
table 格式：
id nametype step comments
1 design 1 afasdfa
2 code 1 wewewe
3 design 2 irerer

我想查找出来的样式：
nametype step comments
design 2 irerer
code 1 wewewe

也就是当nametype 为‘design’ 且step为最大时查询出来，相同的nametype 根据step取最新的数据
[解决办法]
select * from table a
where not exists( select id from table b on a.nametype=b.nametype
and a.step<b.step )
[解决办法]
select *
from table a
where exists (select 1 from (select nametype ,max(step)step from table where nametype ='design' group by nametype ) b where a.nametype=b.nametype and a.step=b.step)
and nametype ='design'
[解决办法]

引用:

table 格式：
id nametype step comments
1 design 1 afasdfa
2 code 1 wewewe
3 design 2 irerer

我想查找出来的样式：
nametype step comments
design 2 irerer
code 1 wewewe

也就是当nametype 为‘design’ 且step为最大时查询出来，相同的nametype 根据step取最新的数据

code貌似不满足你的需求哦
[解决办法]
你是不是这个意思？

select [id],[nametype], [step] from(select *,ROW_NUMBER () over (partition by nametype order by step desc) as row from [Table])t where t.row=1

--idnametypestep
--2code1
--3design2

[解决办法]

引用:

----------------------------------------------------------------
-- Author  :DBA_Huangzj()
-- Date    :2014-01-15 10:27:37
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--Dec 28 2012 20:23:12 
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 &lt;X64&gt; (Build 9200: )
--
----------------------------------------------------------------
--&gt; 测试数据：[Table]
if object_id('[Table]') is not null drop table [Table]
go 
create table [Table]([id] int,[nametype] varchar(6),[step] int,[comments] varchar(7))
insert [Table]
select 1,'design',1,'afasdfa' union all
select 2,'code',1,'wewewe' union all
select 3,'design',2,'irerer'
--------------开始查询--------------------------

SELECT  *
FROM    [Table] a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    nametype ,
                                    MAX(step) step
                          FROM      [Table]
                         -- WHERE     nametype = 'design' 
 
                          GROUP BY  nametype
                        ) b
                 WHERE  a.nametype = b.nametype
                        AND a.step = b.step )
        --AND nametype = 'design'
----------------结果----------------------------
/* 
id          nametype step        comments
----------- -------- ----------- --------
2           code     1           wewewe
3           design   2           irerer
*/

1111111111111

获取不同信息的最新信息解决办法

热点推荐