求不重复的字段并返回最新日期的SQL方法

有下面的一段原数据:

KS_Date    KS_MetalName KS_TopLimit KS_BottomLimit KS_Average KS_Change
2011-12-14 00:00:00.000 a 100 10 55 54
2011-12-14 00:00:00.000 b 1 1 2 2
2011-12-14 00:00:00.000 c 1 1 10 1
2011-12-14 00:00:00.000 d 1 1 1 1
2011-12-14 00:00:00.000 e 1 1 1 1
2011-12-14 00:00:00.000 f 1 1 1 1
2011-12-14 00:00:00.000 g 1 1 1 1
2011-12-15 00:00:00.000 a 10 10 10 -45
2011-12-16 00:00:00.000 b 2 10 6 4
2011-12-16 00:00:00.000 c 2653 2001 2327 2317
2011-12-16 00:00:00.000 d 300 10 155 154
2011-12-16 00:00:00.000 e 200 100 150 149
2011-12-16 00:00:00.000 f 500 300 400 399
2011-12-16 00:00:00.000 g 5000 200 2600 2599
2011-12-23 00:00:00.000 a 20 10 15 5
2011-12-24 a 22 3 3 3
           
NULL NULL NULL NULL NULL NULL

1、求最新的日期并且KS_MetalName不重复

SELECT     MAX(KS_Date) AS 最新日期, KS_MetalName
FROM KS_U_SpotPrice
GROUP BY KS_MetalName

缺陷是无法返回多行记录,只能返回最多现行:KS_Date和KS_MetalName不能满足我们的要求

2、现在变通方法如下:

SELECT     KS_Date, KS_MetalName, KS_TopLimit, KS_BottomLimit, KS_Average, KS_Change
FROM KS_U_SpotPrice
WHERE (KS_Date IN
(SELECT MAX(KS_Date) AS AS最新日期
FROM KS_U_SpotPrice AS TempTable
GROUP BY KS_MetalName))
ORDER BY KS_MetalName

突然间发现上面这种方法也不能满足要求 ,但是下面的方法保证没有问题:

SELECT     ID, KS_Date, KS_MetalName, KS_TopLimit, KS_BottomLimit, did, username, userleavel
FROM test AS a
WHERE (KS_Date IN
(SELECT MAX(KS_Date) AS Expr1
FROM test AS b
WHERE (a.KS_MetalName = KS_MetalName)))
ORDER BY KS_MetalName


3、如果要是求今天最新的日期的返回值方法如下:

select KS_Date,KS_MetalName,KS_TopLimit,KS_BottomLimit,KS_Average,KS_Change
from KS_U_SpotPrice
where (DATEDIFF("d",KS_Date,GETDATE())=0)
order by KS_MetalName ASC



学习交流群:364976091
上一篇:VS2010技巧:如何在js文件中使用jQuery智能感知


下一篇:Redis可以做哪些事?