---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-20 14:17:37 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([ar_id] int,[ar_priority] int,[ar_date] datetime,[emp_id] int,[re] nvarchar(4)) insert [huang] select 4,4,'2014-05-19',40,'aa' union all select 5,4,'2014-05-19',40,'bb' union all select 6,3,'2014-05-19',40,'cc' union all select 8,4,'2014-05-20',40,'gg' union all select 9,0,'2014-05-20',42,'ee' union all select 10,3,'2014-05-19',43,'ff' --------------生成数据-------------------------- SELECT * FROM huang a WHERE EXISTS (SELECT 1 FROM ( select emp_id,ar_date ,MAX(ar_priority )ar_priority from [huang] GROUP BY emp_id,ar_date )b WHERE a.emp_id=b.emp_id AND a.ar_date=b.ar_date AND a.ar_priority=b.ar_priority) ----------------结果---------------------------- /* ar_id ar_priority ar_date emp_id re ----------- ----------- ----------------------- ----------- ------ 4 4 2014-05-19 00:00:00.000 40 aa 5 4 2014-05-19 00:00:00.000 40 bb 8 4 2014-05-20 00:00:00.000 40 gg 9 0 2014-05-20 00:00:00.000 42 ee 10 3 2014-05-19 00:00:00.000 43 ff */
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-20 14:17:37 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([ar_id] int,[ar_priority] int,[ar_date] datetime,[emp_id] int,[re] nvarchar(4)) insert [huang] select 4,4,'2014-05-19',40,'aa' union all select 5,4,'2014-05-19',40,'bb' union all select 6,3,'2014-05-19',40,'cc' union all select 8,4,'2014-05-20',40,'gg' union all select 9,0,'2014-05-20',42,'ee' union all select 10,3,'2014-05-19',43,'ff' --------------生成数据--------------------------SELECT * FROM huang c WHERE EXISTS (SELECT 1 FROM ( SELECT MAX([ar_id]) [ar_id] , ar_priority , ar_date , emp_id FROM huang a WHERE EXISTS ( SELECT 1 FROM ( SELECT emp_id , ar_date , MAX(ar_priority) ar_priority FROM [huang] GROUP BY emp_id , ar_date ) b WHERE a.emp_id = b.emp_id AND a.ar_date = b.ar_date AND a.ar_priority = b.ar_priority ) GROUP BY ar_priority , ar_date , emp_id )d WHERE c.ar_id=d.ar_id)
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-20 14:17:37
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ar_id] int,[ar_priority] int,[ar_date] datetime,[emp_id] int,[re] nvarchar(4))
insert [huang]
select 4,4,'2014-05-19',40,'aa' union all
select 5,4,'2014-05-19',40,'bb' union all
select 6,3,'2014-05-19',40,'cc' union all
select 8,4,'2014-05-20',40,'gg' union all
select 9,0,'2014-05-20',42,'ee' union all
select 10,3,'2014-05-19',43,'ff'
--------------生成数据--------------------------
SELECT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM (
select emp_id,ar_date ,MAX(ar_priority )ar_priority
from [huang]
GROUP BY emp_id,ar_date )b WHERE a.emp_id=b.emp_id AND a.ar_date=b.ar_date AND a.ar_priority=b.ar_priority)
----------------结果----------------------------
/*
ar_id ar_priority ar_date emp_id re
----------- ----------- ----------------------- ----------- ------
4 4 2014-05-19 00:00:00.000 40 aa
5 4 2014-05-19 00:00:00.000 40 bb
8 4 2014-05-20 00:00:00.000 40 gg
9 0 2014-05-20 00:00:00.000 42 ee
10 3 2014-05-19 00:00:00.000 43 ff
*/
同一個emp_id的同一天出現2條了,同一個emp_id的同一天有多條重複的就要ar_id 最大的
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-20 14:17:37
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ar_id] int,[ar_priority] int,[ar_date] datetime,[emp_id] int,[re] nvarchar(4))
insert [huang]
select 4,4,'2014-05-19',40,'aa' union all
select 5,4,'2014-05-19',40,'bb' union all
select 6,3,'2014-05-19',40,'cc' union all
select 8,4,'2014-05-20',40,'gg' union all
select 9,0,'2014-05-20',42,'ee' union all
select 10,3,'2014-05-19',43,'ff'
--------------生成数据--------------------------SELECT *
FROM huang c
WHERE EXISTS (SELECT 1 FROM (
SELECT MAX([ar_id]) [ar_id] ,
ar_priority ,
ar_date ,
emp_id
FROM huang a
WHERE EXISTS ( SELECT 1
FROM ( SELECT emp_id ,
ar_date ,
MAX(ar_priority) ar_priority
FROM [huang]
GROUP BY emp_id ,
ar_date
) b
WHERE a.emp_id = b.emp_id
AND a.ar_date = b.ar_date
AND a.ar_priority = b.ar_priority )
GROUP BY ar_priority ,
ar_date ,
emp_id )d WHERE c.ar_id=d.ar_id)
----------------结果----------------------------
/*
ar_id ar_priority ar_date emp_id re
----------- ----------- ----------------------- ----------- ------
5 4 2014-05-19 00:00:00.000 40 bb
8 4 2014-05-20 00:00:00.000 40 gg
9 0 2014-05-20 00:00:00.000 42 ee
10 3 2014-05-19 00:00:00.000 43 ff
*/