两列 SQL 计数查询，第一列具有静态标签

发布于 2024-11-08 23:08:43 字数 1227 浏览 0 评论 0原文

我正在使用 Oracle 10g，并且有一个名为 downloads 的表，它基本上如下所示：

ID     DOWNLOAD_DATE
1      2011-02-10
2      2011-03-10
3      2011-04-10
4      2011-05-10
5      2011-01-11
6      2011-04-22
7      2011-02-18
8      2011-03-14
9      2011-02-01
10     2011-01-31

等等。

我想执行一个返回以下内容的查询：

Downloads today
Downloads yesterday
Downloads from start date until today
Downloads on start date
Downloads on day after start date

我想在两列中打印结果。在第一列中，我想放置一个静态标签，如上面的标签。第二列是计数。

我尝试过这样的事情：

select count(id) from downloads where download_date = '2007-08-06'
union
select count(id) from downloads where download_date = '2007-08-08'

但这有两个问题。第一，它只打印一列，即计数。第二，如果给定日期（或日期范围）没有数据，则不会打印任何内容。我需要打印一个零。

然后我尝试了这样的操作：

select download_date, count(download_date) from downloads where download_date = '2007-08-06'
group by download_date
union
select download_date, count(download_date) from downloads where download_date > '2007-08-08'
group by download_date

这会返回两列，但第一列不是静态的，它是数据库中的一个字段（download_date）。更重要的是，它返回多行用于选择日期范围（其中 download_date > '2007-08-08'）的查询，而我只想要一行及其计数。

我该怎么做？

非常感谢，鲍勃

原文

I am using Oracle 10g and I have a table called downloads which basically looks like this:

ID     DOWNLOAD_DATE
1      2011-02-10
2      2011-03-10
3      2011-04-10
4      2011-05-10
5      2011-01-11
6      2011-04-22
7      2011-02-18
8      2011-03-14
9      2011-02-01
10     2011-01-31

etc.

I would like to execute a query that returns the following:

Downloads today
Downloads yesterday
Downloads from start date until today
Downloads on start date
Downloads on day after start date

I would like to print the results in two columns. In the first column I'd like to put a static label, like the ones above. And in the second column the count.

I've tried something like this:

select count(id) from downloads where download_date = '2007-08-06'
union
select count(id) from downloads where download_date = '2007-08-08'

But this has 2 problems. One, it only prints one column, the count. Two, if there is no data for a given date (or range of dates), nothing is printed. I need a zero to be printed.

So then I tried something like this:

select download_date, count(download_date) from downloads where download_date = '2007-08-06'
group by download_date
union
select download_date, count(download_date) from downloads where download_date > '2007-08-08'
group by download_date

This returns two columns but the first column isn't static, it's a field in the database (download_date). And more importantly, it returns multiple rows for query that selects a range of dates (where download_date > '2007-08-08'), whereas I just want one row, with its count.

How can I do this?

Many thanks,
Bob

分享到QQ

分享到微博

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

发布评论

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

当爱已成负担 2024-11-15 23:08:43

首先。为此，请使用Union All。 Union 将删除双重记录，在这种情况下这可能不是您想要的。这并不重要，但 union all 也更快。；）

其次，我不明白为什么你不能只放入静态标签，如下所示：

select 'Look, static' from dual

然后，你可以编写如下查询：

select 
  'Downloads today' as Caption, 
  count(*) as DownloadCount 
from 
  Downloads where trunc(download_date) = trunc(sysdate)
union all
select 
  'Downloads yesterday'
  count(*)
from 
  Downloads where trunc(download_date) = trunc(sysdate) - 1

如果没有数据，则 Count will print 0 。

First of all. Use Union All for this. Union will strip double records, which is probably not what you want in this case. It won't matter much, but union all is faster too. ;)

Second, I don't understand why you can't just put in a static label, like this:

select 'Look, static' from dual

Then, you could write a query like this:

select 
  'Downloads today' as Caption, 
  count(*) as DownloadCount 
from 
  Downloads where trunc(download_date) = trunc(sysdate)
union all
select 
  'Downloads yesterday'
  count(*)
from 
  Downloads where trunc(download_date) = trunc(sysdate) - 1

Count will print 0 if there is no data.

回复收藏 0 原文