Pl sql...查找出现次数

发布于 2024-11-02 04:19:15 字数 926 浏览 1 评论 0原文

假设我有第 2 条记录，其中包含日期

table1 和 2 组记录

key1 startdate1  startdate2 startdate3
100  2349         2456       2345
100  3456         3878       2872

日期为二进制格式。我需要获取日期更改的计数，这意味着日期在某个特定日期之后更改的次数即，假设 2011 年 4 月 14 日之后，日期更改的次数（对于这两条记录相对于特定的 key_1）

说明

假设我有 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 |
+------+-------------+-------------+-------------+

我需要计算更改日期的次数自 2011 年 4 月 14 日起。例如，对于键 701，有 5 个大于 2011 年 4 月 14 日的更改日期。我需要获得包含两个字段的表输出：

+------+---------------+
| Key1 | changedcount  |
+------+---------------+
| 701  | 5             |
+------+---------------+

原文

Suppose I am having a 2 st of records with date

table1 with 2 set of records

key1 startdate1  startdate2 startdate3
100  2349         2456       2345
100  3456         3878       2872

The date is in binary format. I need to get the count of date change that means, number of times the date has changed after one particular date
ie, suppose after April 14th 2011, the number of times the date changes (for these two records with respect to particular key_1)

Clarification

Suppose I have three 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 |
+------+-------------+-------------+-------------+

I need to count the number of changed dates greater than 14-apr-2011. For example, for key 701, there are 5 changed dates greater than 14-Apr-2011. I need to get a table output with two fields:

+------+---------------+
| Key1 | changedcount  |
+------+---------------+
| 701  | 5             |
+------+---------------+

分享到QQ

分享到微博

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

发布评论

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

很酷又爱笑 2024-11-09 04:19:16

您可以尝试一下：

select sum(d1)+sum(d2)+sum(d3) , key
 from
(
select
  t.key,
  (select count(*) from test t1 where t1.key = t.key and start_date1>to_date('14-Apr-2011','DD-mon-yyyy')) d1,
  (select count(*) from test t2 where t2.key = t.key  and start_date2>to_date('14-Apr-2011','DD-mon-yyyy')) d2,
  (select count(*) from test t3 where t3.key = t.key  and start_date3>to_date('14-Apr-2011','DD-mon-yyyy')) d3
from  test t
group by key
)
group by key

如果您使用的是 11g 或更高版本，您也可以尝试 ORACLE PIVOT() 函数。我目前没有安装 11g，但如果您需要一个示例，我可以给您一个具有此功能的示例。

问候，
亚历克斯

You may try this:

select sum(d1)+sum(d2)+sum(d3) , key
 from
(
select
  t.key,
  (select count(*) from test t1 where t1.key = t.key and start_date1>to_date('14-Apr-2011','DD-mon-yyyy')) d1,
  (select count(*) from test t2 where t2.key = t.key  and start_date2>to_date('14-Apr-2011','DD-mon-yyyy')) d2,
  (select count(*) from test t3 where t3.key = t.key  and start_date3>to_date('14-Apr-2011','DD-mon-yyyy')) d3
from  test t
group by key
)
group by key

You could also try ORACLE PIVOT() function if you are using 11g or above. I dont have 11g installed at the moment but if you need an example i can give you one with this function.

Regards,
Alex

回复收藏 0 原文

酒绊 2024-11-09 04:19:16

现在您提出了更明确的问题：

select key1, count(distinct start_date)
from (
    select key1, start_date1 as start_date from myTable
    UNION ALL
    select key1, start_date2 as start_date from myTable
    UNION ALL
    select key1, start_date3 as start_date from myTable
) normalFormTable N
where start_date > myGivenDate
group by key

Now that you given more clear question:

select key1, count(distinct start_date)
from (
    select key1, start_date1 as start_date from myTable
    UNION ALL
    select key1, start_date2 as start_date from myTable
    UNION ALL
    select key1, start_date3 as start_date from myTable
) normalFormTable N
where start_date > myGivenDate
group by key

回复收藏 0 原文

指尖凝香 2024-11-09 04:19:15

尝试规范化你的表：

WITH
  normalized_data AS
  (
    SELECT key1, start_date1 AS start_date FROM MYTABLE
  UNION ALL
    SELECT key1, start_date2 AS start_date FROM MYTABLE
  UNION ALL
    SELECT key1, start_date3 AS start_date FROM MYTABLE
  )

SELECT
  key1,
  COUNT(DISTINCT start_date) AS changedcount
FROM
  normalized_data
WHERE
  start_date > your_start_date_here
GROUP BY
  key1

Try normalizing your table:

WITH
  normalized_data AS
  (
    SELECT key1, start_date1 AS start_date FROM MYTABLE
  UNION ALL
    SELECT key1, start_date2 AS start_date FROM MYTABLE
  UNION ALL
    SELECT key1, start_date3 AS start_date FROM MYTABLE
  )

SELECT
  key1,
  COUNT(DISTINCT start_date) AS changedcount
FROM
  normalized_data
WHERE
  start_date > your_start_date_here
GROUP BY
  key1

回复收藏 0 原文

~没有更多了~