ar_id ar_priority ar_date emp_id remark
4 4 2014-05-19 40 aa
5 4 2014-05-19 40 bb
6 3 2014-05-19 40 cc
8 4 2014-05-20 40 gg
9 0 2014-05-20 42 ee
10 3 2014-05-19 43 ff
賽選emp_id和ar_date相同的 找出 ar_priority 最大的 如果有多條就找ar_id最大的記錄
數據多，字段多 求大神給牛X點的sql
結果：
ar_id ar_priority ar_date emp_id remark
5 4 2014-05-19 40 bb
8 4 2014-05-20 40 gg
9 0 2014-05-20 42 ee
10 3 2014-05-19 43 ff
測試數據：
SELECT '4' ar_id,'4' ar_priority,'2014-05-19' ar_date,'40' emp_id,'aa' remark UNION ALL
SELECT '5','4','2014-05-19','40','bb' remark union ALL
SELECT '6','3','2014-05-19','40','cc' remark union ALL
SELECT '8','4','2014-05-20','40','gg' remark union ALL
SELECT '9','0','2014-05-20','42','ee' remark union ALL
SELECT '10','3','2014-05-19','43','ff' remark
------解决方案--------------------
----------------------------------------------------------------
-- 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,[remark] 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