Oracle SQL-查找表中最大匹配日期的行的计数

发布于 2025-02-11 06:11:47 字数 288 浏览 2 评论 0原文

我正在尝试使用查询来从行返回计数，以使行的日期与表中该列的最大日期匹配。

Oracle SQL：版本11.2：

以下语法似乎是正确的（对我而言），并且编译和运行。但是，它不仅返回最大计数，而是像“ haivng”子句一样返回几个计数。

Select ourDate, Count(1) as OUR_COUNT
from schema1.table1
group by ourDate
 HAVING ourDate = max(ourDate) ;

请问如何解决？

原文

I am trying to use a query to return the count from rows such that the date of the rows matches the maximum date for that column in the table.

Oracle SQL: version 11.2:

The following syntax would seem to be correct (to me), and it compiles and runs. However, instead of returning JUST the count for the maximum, it returns several counts more or less like the "HAIVNG" clause wasn't there.

Select ourDate, Count(1) as OUR_COUNT
from schema1.table1
group by ourDate
 HAVING ourDate = max(ourDate) ;

How can this be fixed, please?

分享到QQ

分享到微博

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

发布评论

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

梦里南柯 2025-02-18 06:11:47

您可以使用：

SELECT MAX(ourDate) AS ourDate,
       COUNT(*) KEEP (DENSE_RANK LAST ORDER BY ourDate) AS ourCount
FROM   schema1.table1

OR：

SELECT ourDate,
       COUNT(*) AS our_count
FROM   (
  SELECT ourDate,
         RANK() OVER (ORDER BY ourDate DESC) AS rnk
  FROM   schema1.table1
)
WHERE  rnk = 1
GROUP BY ourDate

对于示例数据：

CREATE TABLE table1 (ourDate) AS
SELECT SYSDATE FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT SYSDATE - 1 FROM DUAL;

两者都输出：

ourdate our_count
2022-06-28 13:35:01 5

ourdate	our_count
2022-06-28 13:35:01	5

db＆lt; “>在这里

You can use:

SELECT MAX(ourDate) AS ourDate,
       COUNT(*) KEEP (DENSE_RANK LAST ORDER BY ourDate) AS ourCount
FROM   schema1.table1

or:

SELECT ourDate,
       COUNT(*) AS our_count
FROM   (
  SELECT ourDate,
         RANK() OVER (ORDER BY ourDate DESC) AS rnk
  FROM   schema1.table1
)
WHERE  rnk = 1
GROUP BY ourDate

Which, for the sample data:

CREATE TABLE table1 (ourDate) AS
SELECT SYSDATE FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT SYSDATE - 1 FROM DUAL;

Both output:

OURDATE OUR_COUNT
2022-06-28 13:35:01 5

OURDATE	OUR_COUNT
2022-06-28 13:35:01	5

db<>fiddle here

回复收藏 0 原文

无敌元气妹 2025-02-18 06:11:47

我不知道我是否了解您想要什么。尝试以下操作：

Select x.ourDate, Count(1) as OUR_COUNT
from schema1.table1 x
 where x.ourDate = (select max(y.ourDate) from schema1.table1 y)
group by x.ourDate

I don't know if I understand what you want. Try this:

Select x.ourDate, Count(1) as OUR_COUNT
from schema1.table1 x
 where x.ourDate = (select max(y.ourDate) from schema1.table1 y)
group by x.ourDate

回复收藏 0 原文

星光不落少年眉 2025-02-18 06:11:47

一种选项是使用获取最大日期的子查询：

select ourdate, count(*)
from table1
where ourdate = (select max(ourdate) 
                 from table1)
group by ourdate;

或者，更多 Modern 方法（如果您的数据库版本支持它；虽然11G不支持它）：

select ourdate, count(*)
from table1
group by ourdate
order by ourdate desc
fetch first 1 rows only;

One option is to use a subquery which fetches maximum date:

select ourdate, count(*)
from table1
where ourdate = (select max(ourdate) 
                 from table1)
group by ourdate;

Or, a more modern approach (if your database version supports it; 11g doesn't, though):

select ourdate, count(*)
from table1
group by ourdate
order by ourdate desc
fetch first 1 rows only;

回复收藏 0 原文

伤痕我心 2025-02-18 06:11:47

You can use this SQL query:

select MAX(ourDate),COUNT(1) as OUR_COUNT
from schema1.table1
where ourDate = (select MAX(ourDate) from schema1.table1)
group by ourDate;

You can use this SQL query:

select MAX(ourDate),COUNT(1) as OUR_COUNT
from schema1.table1
where ourDate = (select MAX(ourDate) from schema1.table1)
group by ourDate;

回复收藏 0 原文

~没有更多了~