SQL中Distinct数据的distinct值

Question

在我的表中，有时有两个日期和两个值，但我只需要其中一个。有没有办法根据不同的日期选择不同的值？

示例：

DATADATE                ID
2008-06-30 00:00:00.000 12
2008-03-31 00:00:00.000 12
2007-12-31 00:00:00.000 3
2007-12-31 00:00:00.000 12
2007-09-30 00:00:00.000 3
2007-09-30 00:00:00.000 12
2007-06-30 00:00:00.000 3
2007-06-30 00:00:00.000 12
2007-03-31 00:00:00.000 3
2007-03-31 00:00:00.000 12
2006-12-31 00:00:00.000 3
2006-09-30 00:00:00.000 3
2006-06-30 00:00:00.000 3

我需要得到的是：

DATADATE                ID
2008-06-30 00:00:00.000 12
2008-03-31 00:00:00.000 12
2007-12-31 00:00:00.000 12
2007-09-30 00:00:00.000 12
2007-06-30 00:00:00.000 12
2007-03-31 00:00:00.000 12
2006-12-31 00:00:00.000 3
2006-09-30 00:00:00.000 3
2006-06-30 00:00:00.000 3

任何帮助都非常感谢，谢谢。

Answer 1

您可以使用group by

select  DATADATE
,       max(IDs)
from    YourTable
group by
        DATADATE

Answer 2

如果您使用的是sql server 2005+。然后你可以这样做：

;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATADATE DESC) AS RowNbr,
        Table1.*
    FROM
        Table1
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.RowNbr=1

编辑

在CTE函数中，你可以join或者做任何你不能做的事情来得到你想要的输出。如下所示：

;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY Table1.ID ORDER BY Table2.DATADATE DESC) AS RowNbr,
        Table1.*
    FROM
        Table1
        JOIN Table2
            ON Table1.ID = Table2.ID
)
SELECT
    *
FROM
    CTE

WHERE
    CTE.RowNbr=1