KpiId KpiData YEAR MONTH TableIndex
----------- --------------------------------------- ----------- ----------- -----------
19 905.10 2009 1 1
19 831.20 2009 2 1
19 831.20 2009 3 1
19 1500.00 2009 7 4
19 878.30 2009 7 1
19 4908.25 2013 2 4
数据结构如上所示，现在问题是 该怎么让KPIid year month 重复的只显示tableindex 最大的一个呢
上面就只显示 kpidata 为1500的这一条记录 878.3不显示
这样的sql怎么写呢？请各位指导一下。。。
------解决方案--------------------
或者这样：
select *
from t a
where not exists(select 1 from t
where a.KPIid = t.KPIid and a.[YEAR] = t.[YEAR]
and a.[MONTH] = t.[MONTH]
and a.TableIndex < t.TableIndex)
------解决方案--------------------
SELECT t1.KpiId, t1.[Year], t1.[Month], d.KpiData, d.TableIndex
FROM (SELECT KpiId, [Year], [Month]
FROM T GROUP BY KpiID, [Year], [Month]) t1
CROSS APPLY (SELECT TOP 1 KpiData, TableIndex FROM T WHERE KpiId =t1.KpiId AND [Year]=t1.[Year] AND [Month]=t1.[Month] ORDER BY TableIndex DESC) d
------解决方案--------------------
select
*
from
tb t
where
tableindex=(select max(tableindex) from tb where KpiId =t.KpiId AND [Year]=t.[Year] AND [Month]=t.[Month])