如何在一个查询中最大化(日期)并使用SQL Server中的in功能? [英] How to max(date) and use the in feature in sql server in one query?

问题描述

我有一张这样的桌子

id   color  shade   date
---  ----   -----   -----
1    red    dark    01/01/1990
2    red    light   09/16/2013
3    green  light   08/15/2010
4    green  dark    09/18/2012
5    maroon dark    08/20/2013
6    white  dark    08/31/2013
7    white  light   08/30/2012
8    purple light   08/20/2010

我想输入每种颜色的最新日期.所以我尝试做:

I wanted entries for each color with the latest date. So I tried doing:

select id, color, shade, max(date) from mytable;

这行不通，并给了我错误:

This didn't work and gave me error:

is invalid in the select list because it is not contained in either an aggregate 
function or the GROUP BY clause.

好的，所以我按照错误提示将其添加到了Group By中

Alright, so I added it in the Group By as suggested by the error

select id, color, shade, max(date) from mutable
Group by id, color, shade

这给了我想要的结果.

This gave me the desired results.

问题

现在，我希望只对某些颜色重复与上述相同的查询.例如，我只想看到红色和绿色的.

Now, I wanted the same query as above repeated only for certain colors. For example I only wanted to see red and green ones.

所以我做到了:

select id, color, shade, max(date) from mutable
Where color in ('red', 'green')
Group by id, color, shade;

但是，这不能给我正确的结果数，因为我想它们是按shade分组的.

However, this does not give me the correct number of results because I guess they are being grouped by shade.

获取我想要的结果的最佳方法是什么?基本上，我想从同一事物的两个实例中剔除max date，并且进一步从表中剔除，只希望使用某些颜色.

What is the best way to fetch my desired results? Basically I want max date out of the two instances of same thing and further out of the table only want certain colors.

推荐答案

尝试一下

SELECT t1.* FROM t t1
JOIN 
(
  SELECT color, MAX([date]) [date] FROM t
  WHERE color IN ('red', 'green')
  GROUP BY color
) t2
ON t1.color = t2.color AND t1.date = t2.date
ORDER BY ID

输出

id  color   shade   date
2   red     light   2013-09-16
5   green   dark    2013-08-20

OR

SELECT id, color, shade, [date]
FROM 
(
  SELECT *,
        row_number() OVER (PARTITION BY color ORDER BY [date] DESC) AS sno
  FROM t
  WHERE color IN ('red', 'green')
) tt
WHERE sno = 1
ORDER BY ID

输出

id  color   shade   date
2   red     light   2013-09-16
5   green   dark    2013-08-20

OR

SELECT id, color, shade, [date]
FROM 
(
  SELECT *,
        MAX([date]) OVER (PARTITION BY color) AS maxdate
  FROM t
  WHERE color IN ('red', 'green')
) tt
WHERE [date] = maxdate
ORDER BY ID

输出

id  color   shade   date
2   red     light   2013-09-16
5   green   dark    2013-08-20

这篇关于如何在一个查询中最大化(日期)并使用SQL Server中的in功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！