按不同值分组 [英] Grouping by Distinct Values

问题描述

经过一些帮助，我有了( MSChart -强制从原点绘制折线图)，设法将以下MSSQL查询汇总到折线图中以供使用.

I have, after some help (MSChart - Forcing a line chart to draw from the Origin), managed to put together the following MSSQL query for use in a line chart.

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

但是，我有一个问题.该查询返回如下内容:

However, I have a problem. This query returns something like the below:

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

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

This means that I get a reading for every single day, whereas in fact I only require one row for each distinct Value. I want the first time it appears, in every case, so for the above example I would want my query to return:

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来挑选请求的结果)，而且似乎并没有适合这个.当然，它们也很难适应我已经拥有的查询.

I've looked through several questions that seem to be based on a similar premise, but all of the responses were very tailored to the specific situation (some even use LIKE to pick out requested results), and didn't seem to suit this. They also, of course, would be difficult to fit into the query I already have.

有什么想法吗?预先感谢.

Any ideas? Thanks in advance.

对不起，躲闪的桌子，我的第一次尝试要糟糕得多.

sorry for dodgy tables, my first attempt was much worse.

推荐答案

尝试一下.您已经完成了困难的部分.您只需要在Value上分组并获取与之关联的第一个(即MIN)Date:

Try this. You've done the hard part already. You just need to group on Value and get the first (i.e. MIN) Date associated with it:

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;

这篇关于按不同值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！