SQL - GROUP BY 和 COALESCE 的丑陋组合

Question

我有一个类似以下数据的表：

[ID]、[State]、[foo]、[DateCreated]、[DateUpdated]

我越工作，我的SQL代码就变得越丑陋。这说明我可能在做错某些事情。我想要的是每个状态的唯一列表，只要该状态下的foo始终相同（如果该状态下所有记录的foo不相同，则我根本不需要该状态）。此外，我希望将DateCreated和DateUpdated合并，并获取该状态下的最大值。

所以，给定这些数据：

[ID], [State], [foo], [DateCreated], [DateUpdated]
1,  MA, data1,  05/29/2012, 06/02/2012
2,  MA, data1,  05/29/2012, 06/03/2012
3,  RI, data2,  05/29/2012, NULL
4,  RI, data3,  05/29/2012, NULL
5,  NH, data4,  05/29/2012, NULL
6,  NH, data4,  05/29/2012, 06/05/2012

I'd like only these results:

[State], [foo], [LastUpdated]
MA, data1,  06/03/2012
NH, data4,  06/05/2012

什么是获取所需内容的最优雅方式？

- Scott K

---

你可以使用视图吗？ - solidau

如果需要的话，我可以创建视图。 - Scott K

我越是在这上面工作，我的SQL语句就变得越丑陋，这说明我可能做错了什么。毕竟，MySQL是一种让你执行笛卡尔积才能找到唯一性的语言... - Monica Heddneck

Answer 1

另一个链接:

http://sqlfiddle.com/#!6/fd219/1

SELECT
  t.State,
  MAX(t.foo),
  MAX( COALESCE( t.DateUpdated, t.DateCreated ))
FROM t
GROUP BY t.State
HAVING COUNT(DISTINCT t.foo) = 1;

- biziclop

---

哦，我喜欢这个比我的答案更好...不错。 - BLSully

@biziclop - 只是好奇，你说SQL Fiddle无法识别美国日期格式是什么意思？你遇到了什么问题？ - Jake Feasel

如果不需要检查NULL值的话，我可能会使用这个函数。 - Scott K

我看不出我的查询失败在哪里，但无论如何 :) - biziclop

@biziclop 啊，是的，日期解析逻辑有点奇怪 - 我正在推迟到浏览器的日期解析引擎，并且它们似乎对传递的给定字符串会做出一些奇怪的事情。有点烦人。至于我如何找到你 - 只是想跟踪人们如何使用该网站！别担心 - 我不仅寻找可能的批评 - 我也会点赞哈哈！ - Jake Feasel

Answer 2

一个简单的带有嵌套查询的Group by语句就足够了：

Select State, coalesce(max_created,max_updated) from (
  Select State, min(foo) as min_foo, max(foo) as max_foo, 
    max(DateCreated) as max_created,
    max(DateUpdated) as max_updated
  From Data
  Group by State)
 Where min_foo = max_foo

- Frank Schmitt

---

我所做的唯一重大更改是交换 COALESCE 的顺序，以使其优先考虑 UPDATED 日期。再次感谢！ - Scott K

对于有类似问题的人，值得注意的是MAX()和MIN()功能忽略NULL。 因此，MIN(foo)需要是MIN(COALESCE(foo,''))或类似的内容。 - Scott K

Answer 3

对于那些仍在使用SQL 2000的人来说，虽然不够优雅，但以下方法可能会有所帮助：

SELECT T1.State, T2.Foo, T1.LastUpdated
FROM (
    SELECT State, MAX([ID]) AS [ID], 
        MAX(COALESCE(DateUpdated, DateCreated)) AS LastUpdated
    FROM YourTable
    GROUP BY State
    HAVING COUNT(DISTINCT Foo) = 1
) T1 
INNER JOIN YourTable T2 ON T1.State = T2.State AND T1.[ID] = T2.[ID]

- LittleBobbyTables - Au Revoir

---

谢谢您为我们这些不幸的人考虑:) 顺便说一下，我喜欢您的用户名。我经常使用那个漫画作为SQL注入的例子。 - Scott K

为什么需要嵌套的SELECT语句？ - biziclop

@Biziclop - 实际上并不是这样的，那只是我当时想出来的。你的编辑基本上就是我内部SELECT中的 max(Foo)，这正是我所追求的。 - LittleBobbyTables - Au Revoir

当foo为空时，我遇到了问题。此外，我还有foo1、foo2等变量，而NULL值真的成为了一个问题:(在这种情况下，Frank的答案效果更好。 - Scott K

@ScottK - 你没有提到Foo是“NULL”，但不用担心，我应该考虑到这一点。 - LittleBobbyTables - Au Revoir

Answer 4

假设您正在使用SQL Server 2005或更高版本：

请尝试以下操作：

WITH Data AS
(
    SELECT  *,
        COALESCE([DateCreated], [DateUpdated]) AS LastUpdated,
        ROW_NUMBER() OVER(PARTITION BY State ORDER BY COALESCE([DateCreated], [DateUpdated]) DESC) Position
      FROM <YOUR-TABLE> a
     WHERE NOT EXISTS
     (
        SELECT  1 
            FROM    <YOUR-TABLE> b
         WHERE  a.State = b.State
            AND a.foo <> b.foo
     )
)
SELECT State, foo, LastUpdated
  FROM Data
 WHERE Positon = 1

- Chandu

---

我忘了提到SQL版本，抱歉。我使用的是SQL 2000。 - Scott K

Answer 5

试试这个：

select state_name,foo,max(dateUpdated) from state where state_name in (select state_name from state group by state_name having count(distinct foo)=1) group by state_name,foo;

- Pooja smithi