查询以获取日期计数

发布于 2024-11-02 04:57:19 字数 497 浏览 0 评论 0原文

假设我有 3 组记录

key1    start_date1     start_date2    start_date3
701 08-SEP-2009 08-DEC-2009    08-jan-2010
701 08-JUN-2013 08-SEP-2013    08-DEC-2013
701 08-MAR-2017 08-MAR-2018    31-DEC-1899

，所以我需要获取大于 14-apr-2011 的更改日期计数。
即对于特定键 701，更改日期计数为 5

即对于 3 组行，日期大于 14-Apr-2011共有 5 个计数。
我需要获得一个包含两个字段的表输出，

Key1  changedcount
701      5

请帮助我解决这个问题..我为此尝试了超过 2 天:(

原文

Suppose I am having 3 set of records

key1    start_date1     start_date2    start_date3
701 08-SEP-2009 08-DEC-2009    08-jan-2010
701 08-JUN-2013 08-SEP-2013    08-DEC-2013
701 08-MAR-2017 08-MAR-2018    31-DEC-1899

So i need to get a count of changed date greater than 14-apr-2011.
i.e. for particular key 701, there are changed date count as 5

i.e. for 3 set of rows, the dates greater than 14-Apr-2011 are of 5 counts.
I need to get a table output with two fields as

Key1  changedcount
701      5

Please help me regarding this..i was tryin for this for more than 2 days :(

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陌生 2024-11-09 04:57:19

你可以尝试如下操作：

SELECT SUM(nb)
  FROM (SELECT COUNT(*) nb FROM your_table WHERE start_date1 > '14-Apr-2011'
        UNION ALL
        SELECT COUNT(*) nb FROM your_table WHERE start_date2 > '14-Apr-2011'
        UNION ALL
        SELECT COUNT(*) nb FROM your_table WHERE start_date3 > '14-Apr-2011')

you could try something like the following :

SELECT SUM(nb)
  FROM (SELECT COUNT(*) nb FROM your_table WHERE start_date1 > '14-Apr-2011'
        UNION ALL
        SELECT COUNT(*) nb FROM your_table WHERE start_date2 > '14-Apr-2011'
        UNION ALL
        SELECT COUNT(*) nb FROM your_table WHERE start_date3 > '14-Apr-2011')

回复收藏 0 原文

看春风乍起 2024-11-09 04:57:19

SELECT
  key1,
  COUNT(CASE WHEN start_date1 > '14-Apr-2011' THEN 1 ELSE NULL END) +
  COUNT(CASE WHEN start_date2 > '14-Apr-2011' THEN 1 ELSE NULL END) +
  COUNT(CASE WHEN start_date3 > '14-Apr-2011' THEN 1 ELSE NULL END) AS changedcount
GROUP BY key1
FROM atable

SELECT
  key1,
  COUNT(CASE WHEN start_date1 > '14-Apr-2011' THEN 1 ELSE NULL END) +
  COUNT(CASE WHEN start_date2 > '14-Apr-2011' THEN 1 ELSE NULL END) +
  COUNT(CASE WHEN start_date3 > '14-Apr-2011' THEN 1 ELSE NULL END) AS changedcount
GROUP BY key1
FROM atable

回复收藏 0 原文

~没有更多了~