按日期查询多个记录

时间：2022-12-07 22:48:19

I've got few tables like this:

我有几张这样的桌子:

Color

id      Color_Name
---    -------
 1      RED
 2      GREEN

Color_Shades

id      ColorId    ShadeId    date_created
---     -------    -------   --------------
 1       1          55        03/15/2013
 2       1          43        02/01/2012
 3       2          13        05/15/2011
 4       2          15        06/11/2009

I'm trying to get a list of all distinct colors with their latest date.

我正在尝试用他们最新的日期获得所有不同颜色的列表。

I tried

SELECT a.Color_Name, b.date_created FROM Color a, Color_Shades b
WHERE a.id = b.ColorId

but this is giving me mixed results.

但这给我的结果好坏参半。

My desired results are:

我想要的结果是:

 Color_Name   date_created
----------    ---------------
 RED              03/15/2013
 GREEN            05/15/2011

1 个解决方案

#1

3  

You are near to what you need. You just need to aggregate those columns using MAX to get theor latest date.

你接近你需要的东西。您只需使用MAX汇总这些列以获取最新日期。

SELECT  a.Color_name, MAX(b.date_created) date_created
FROM    Color a
        INNER JOIN Color_shades b
            ON a.id = b.colorID
GROUP   BY a.Color_Name

#1

3  

You are near to what you need. You just need to aggregate those columns using MAX to get theor latest date.

你接近你需要的东西。您只需使用MAX汇总这些列以获取最新日期。

SELECT  a.Color_name, MAX(b.date_created) date_created
FROM    Color a
        INNER JOIN Color_shades b
            ON a.id = b.colorID
GROUP   BY a.Color_Name
标签：

相关文章