，Sql语句筛选-MSSQL教程-爱易网页

，Sql语句筛选

日期：2014-05-17　浏览次数：20509 次

求助，Sql语句筛选
如下数据，我想筛选出当machine这个字段相同时只要term这个时间字段最晚的那笔就行了
term mold type machine aaa
2012-09-25 08:00:00 EI1202015S A INA038 1INNWTNBBT01
2012-09-25 08:30:00 EI1202015S A INA038 1INNWTNBBT11
2012-09-25 08:00:00 EI1202020S A INB032 1INNWTNBBB02
2012-09-25 08:00:00 EI1202021S A INB033 1INNWTNBBB02
2012-09-25 08:00:00 EI1112366S A INB034 1ISDAKRABB01
2012-09-25 08:40:00 EI1202035S A INB034 1ISDAKRABB11

以上数据我要的结果就是如下的，把machine字段重复的去掉时间早的，留下时间晚的。
term mold type machine aaa
2012-09-25 08:30:00 EI1202015S A INA038 1INNWTNBBT11
2012-09-25 08:00:00 EI1202020S A INB032 1INNWTNBBB02
2012-09-25 08:00:00 EI1202021S A INB033 1INNWTNBBB02
2012-09-25 08:40:00 EI1202035S A INB034 1ISDAKRABB11

------解决方案--------------------

SQL code

select * from tb t
where not exists(select 1 from tb where machine=t.machine and term>t.term)

------解决方案--------------------

SQL codeselect 
  *
from
  tb t
where 
  term=(select max(term) from tb where machine=t.machine)

------解决方案--------------------
SQL code
declare @T TABLE
([term] datetime,[mold] varchar(10),
[type] varchar(1),[machine] varchar(6),
[aaa] varchar(12))
insert @T
select '2012-09-25 08:00:00','EI1202015S','A','INA038','1INNWTNBBT01' union all
select '2012-09-25 08:30:00','EI1202015S','A','INA038','1INNWTNBBT11' union all
select '2012-09-25 08:00:00','EI1202020S','A','INB032','1INNWTNBBB02' union all
select '2012-09-25 08:00:00','EI1202021S','A','INB033','1INNWTNBBB02' union all
select '2012-09-25 08:00:00','EI1112366S','A','INB034','1ISDAKRABB01' union all
select '2012-09-25 08:40:00','EI1202035S','A','INB034','1ISDAKRABB11'

select * from @T t WHERE [term]=
(SELECT MAX([term]) FROM @T WHERE [machine]=t.[machine])
/*
term                    mold       type machine aaa
----------------------- ---------- ---- ------- ------------
2012-09-25 08:40:00.000 EI1202035S A    INB034  1ISDAKRABB11
2012-09-25 08:00:00.000 EI1202021S A    INB033  1INNWTNBBB02
2012-09-25 08:00:00.000 EI1202020S A    INB032  1INNWTNBBB02
2012-09-25 08:30:00.000 EI1202015S A    INA038  1INNWTNBBT11
*/

------解决方案--------------------
SQL code--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([term] DATETIME,[mold] VARCHAR(10),[type] VARCHAR(1),[machine] VARCHAR(6),[aaa] VARCHAR(12))
INSERT [tb]
SELECT '2012-09-25 08:00:00','EI1202015S','A','INA038','1INNWTNBBT01' UNION ALL
SELECT '2012-09-25 08:30:00','EI1202015S','A','INA038','1INNWTNBBT11' UNION ALL
SELECT '2012-09-25 08:00:00','EI1202020S','A','INB032','1INNWTNBBB02' UNION ALL
SELECT '2012-09-25 08:00:00','EI1202021S','A','INB033','1INNWTNBBB02' UNION ALL
SELECT '2012-09-25 08:00:00','EI1112366S','A','INB034','1ISDAKRABB01' UNION ALL
SELECT '2012-09-25 08:40:00','EI1202035S','A','INB034','1ISDAKRABB11'
--------------开始查询--------------------------

SELECT * FROM [tb] AS t WHERE [term]=(SELECT MAX([term]) FROM tb WHERE [machine]=t.[machine])
----------------结果----------------------------
/* 
term    mold    type    machine    aaa
2012-09-25 08:40:00.000    EI1202035S    A    INB034    1ISDAKRABB11
2012-09-25 08:00:00.000    EI1202021S    A    INB033    1INNWTNBBB02
2012-09-25 08:00:00.000    EI1202020S    A    INB032    1INNWTNBBB02
2012-09-25 08:30:00.000    EI1202015S    A    INA038    1INNWTNBBT11
*/

------解决方案--------------------

SQL code--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

，Sql语句筛选

相关资料更多>

推荐阅读更多>