SQL - GROUP BY和COALESCE的丑陋结合 [英] SQL - Ugly combination of GROUP BY and COALESCE

问题描述

我有一个表格，其数据类似于以下内容：
$ b $ h3 [ID]，[State]，[foo]，[DateCreated]，[DateUpdated] h3>

我工作的时间越长，我的SQL越丑，这告诉我我可能做错了什么。我想要的是每个州的唯一清单，只要foo对于该州永远是相同的（如果foo对于该州所有记录来说都不相同，我根本不希望该州） EM>。此外，我想要COALESCE 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 
  

我只想要这些结果：

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

什么是最优雅的方式来获得我后？

解决方案

使用嵌套查询进行简单分组就足够了：

 选择状态，合并（max_created ，max_updated）from（
选择状态，min（foo）为min_foo，max（foo）为max_foo，
 max（DateCreate d）as max_created，
 max（DateUpdated）as max_updated 
 From Data 
 Group by State）
其中min_foo = max_foo 
  

I have a table with data similar to the following:

[ID], [State], [foo], [DateCreated], [DateUpdated]

The longer I work on this, the uglier my SQL is getting, which tells me I'm probably doing something wrong. What I want is a unique list of each State so long as foo is always the same for that State (if foo is not the same for all records in that State, I don't want that State at all). Also, I want to COALESCE DateCreated and DateUpdated and want the maximum value for that State.

So given this data:

[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

What's the most elegant way to get what I'm after?

解决方案

A simple Group by with nested queries should suffice:

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

这篇关于SQL - GROUP BY和COALESCE的丑陋结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！