sql - 如何在异常(exception)情况下进行 GROUP BY？

Question

这是我的简化代码:

SELECT 
   a.user_id as User_ID,
   min(b.a_day) as Date_from,
   max(b.a_day) as Date_to,
   c.code as ID 
FROM a, b, c 
WHERE 
   a_day > (day, -15, getdate())
GROUP BY 
   a.user_id,
   c.code

查询给出以下输出:

User ID date_from   date_to     id    
1234567 2016-06-13  2016-06-13  B
1234567 2016-06-17  2016-06-17  A
12345672016-06-18  2016-06-18   A
1234567 2016-06-19  2016-06-19  A
1234567 2016-06-20  2016-06-20  A
1234567 2016-06-21  2016-06-21  C
1234567 2016-06-22  2016-06-22  C
1234567 2016-06-23  2016-06-23  D

我需要这样的东西:

User ID date_from   date_to     id
1234567 2016-06-13  2016-06-13  B
1234567 2016-06-17  2016-06-20  A
1234567 2016-06-21  2016-06-22  C
1234567 2016-06-23  2016-06-23  D

当我将 min() 和 max() 函数与 group by 结合使用时，它可以很好地聚合所有记录，但我必须日复一日地仅聚合具有相同 ID 的日期。

有什么想法吗？

提前致谢。

Answer 1

您可以通过在 GROUP BY 子句中使用 CASE EXPRESSION 来进行条件分组:

SELECT 
   a.user_id as User_ID,
   min(b.a_day) as Date_from,
   max(b.a_day) as Date_to,
   c.code as ID 
FROM a, b, c 
WHERE 
   a_day > (day, -15, getdate())
GROUP BY 
   a.user_id,
   c.code,
   CASE WHEN c.code in ('B','D') THEN b.a_day ELSE 1 END

这会将其生成为 GROUP BY 子句:

c.code = 'B' -> a.user_id,c.code,b.a_day
c.code <> 'B' -> a.user_id,c.code,1