按不同值分组

Question

经过一些帮助MSChart-强制从Origin绘制折线图]后，我设法将以下MSSQL查询放在一起，以用于折线图。

WITH AllDays
AS
(
    SELECT CAST('20120101' as datetime) AS days
    UNION ALL
    SELECT DATEADD(dd, 1, days)
    FROM AllDays
    WHERE DATEADD(dd, 1, days) < cast('20120201' as datetime)
)
SELECT
    MIN(ad.days) AS Date,
    ISNULL((SELECT SUM(value) FROM jobs WHERE dateinvoiced >= CAST('20120101'
    as datetime) AND dateinvoiced <= ad.days)/100,0) AS Value
FROM AllDays AS ad
LEFT JOIN jobs AS j
ON( ad.days = j.dateinvoiced )

GROUP BY ad.days

但是，我有一个问题。该查询返回类似以下内容的内容：

Date                    | Value

2012-01-01 00:00:00.000 |     0 
2012-01-02 00:00:00.000 |     0
2012-01-03 00:00:00.000 |  1234
2012-01-04 00:00:00.000 |  1234
2012-01-05 00:00:00.000 |  1234
2012-01-06 00:00:00.000 | 57312
2012-01-07 00:00:00.000 | 57312
2012-01-08 00:00:00.000 | 90812

这意味着我每天都在阅读，而实际上每个不同的值仅需要一行。在每种情况下，我都希望它第一次出现，因此对于上面的示例，我希望查询返回：

Date                    | Value

2012-01-01 00:00:00.000 |     0
2012-01-03 00:00:00.000 |  1234
2012-01-06 00:00:00.000 | 57312
2012-01-08 00:00:00.000 | 90812

我看了看似是基于类似前提的几个问题，但是所有答复都是针对特定情况而量身定制的（有些甚至使用LIKE来挑选请求的结果），并且似乎不适合此情况。当然，它们也很难适应我已经拥有的查询。

有任何想法吗？提前致谢。

编辑：对不起躲闪的桌子，我的第一次尝试是更糟糕。

Answer 1

试试这个。您已经完成了困难的部分。您只需要分组Value并获得与之关联的第一个（即MIN）Date：

WITH AllDays AS (
    SELECT CAST('20120101' as datetime) AS days
    UNION ALL
    SELECT DATEADD(dd, 1, days)
    FROM AllDays
    WHERE DATEADD(dd, 1, days) < cast('20120201' as datetime)
), V AS (
  SELECT
    MIN(ad.days) AS Date,
    ISNULL((SELECT SUM(value) FROM jobs WHERE dateinvoiced >= CAST('20120101'
    as datetime) AND dateinvoiced <= ad.days)/100,0) AS Value
  FROM AllDays AS ad
  LEFT JOIN jobs AS j ON (ad.days = j.dateinvoiced)
  GROUP BY ad.days
)
SELECT MIN(Date) Date, Value
FROM V
GROUP BY Value;