sql - 如何在一个查询中最大化(日期)和使用 sql server 中的 in 功能？

Question

我有这样一张 table

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

我想要每个颜色的最新日期的条目。所以我尝试这样做:

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

这没有用并给了我错误:

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

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

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

这给了我想要的结果。

问题

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

所以我做了:

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

但是，这并没有给我正确数量的结果，因为我猜它们是按 shade 分组的。

获取我想要的结果的最佳方式是什么？基本上，我希望从同一事物的两个实例中获取 max date，而在表格之外，我只需要某些颜色。

Answer 1

在标准 SQL 中，以下任何查询都可以解决问题:

选项 1

SELECT t1.* FROM t t1
LEFT JOIN t t2
ON t1.color = t2.color AND t1.shade = t2.shade AND t1.date < t2.date
WHERE t2.date IS NULL

选项 2

SELECT t1.* FROM t t1
JOIN (
  SELECT color, shade, max(date) date FROM t
  GROUP BY color, shade
) t2
ON t1.color = t2.color AND t1.shade = t2.shade AND t1.date = t2.date

在您的示例数据中，应该返回所有结果，因此对其进行操作没有多大意义。我把它稍微改成这样:

| ID | COLOR | SHADE |       DATE |
|----|-------|-------|------------|
|  1 |   red |  dark | 1990-01-01 |
|  2 |   red |  dark | 2013-09-16 |
|  3 | green | light | 2010-08-15 |
|  4 | green |  dark | 2012-09-18 |
|  5 | green |  dark | 2013-08-20 |
|  6 | white |  dark | 2013-08-31 |
|  7 | white | light | 2012-08-30 |
|  8 | white | light | 2010-08-20 |

查询的输出将是:

| ID | COLOR | SHADE |       DATE |
|----|-------|-------|------------|
|  2 |   red |  dark | 2013-09-16 |
|  3 | green | light | 2010-08-15 |
|  5 | green |  dark | 2013-08-20 |
|  6 | white |  dark | 2013-08-31 |
|  7 | white | light | 2012-08-30 |

确保应用适当的顺序。

fiddle here .