删除 Oracle 中额外的子查询，选择值数组
我正在选择一些聚合数据并根据日期和特定字段进行分组。我想显示该字段中的所有值以及这些值的计数，即使当天没有与该字段匹配的数据。例如，
Date MyField Count
2009-09-25 A 2
2009-09-25 B 0
2009-09-24 A 1
2009-09-24 B 1
我当前必须执行此操作的 Oracle SQL 类似于以下内容：
SELECT today,
mytable.myfield,
COUNT(
CASE WHEN fields.myfield = mytable.myfield AND
date >= today AND
date < tomorrow
THEN 1
END
)
FROM (
SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 30
),
(
/* This is the part that seems inefficient */
SELECT DISTINCT myfield
FROM mytable
WHERE myfield IN ('A', 'B')
) fields,
mytable
GROUP BY today, mytable.myfield
ORDER BY today DESC, mytable.myfield ASC
我关心的是我确切地知道要为 myfield 显示哪些值，并且使用 SELECT 似乎效率低下访问 查询。我想知道是否有某种方法可以在该子查询中执行类似的操作：mytable 的
SELECT ('A', 'B') AS myfield
FROM DUAL
我使用的是旧版本的 Oracle，其中 WITH 子句不起作用。
I'm SELECTing some aggregate data and grouping on the date and a particular field. I want to display all values in that field and a count for those values even if there was no data matching that field on that day. E.g.
Date MyField Count
2009-09-25 A 2
2009-09-25 B 0
2009-09-24 A 1
2009-09-24 B 1
The Oracle SQL I currently have to do this is akin to the following:
SELECT today,
mytable.myfield,
COUNT(
CASE WHEN fields.myfield = mytable.myfield AND
date >= today AND
date < tomorrow
THEN 1
END
)
FROM (
SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 30
),
(
/* This is the part that seems inefficient */
SELECT DISTINCT myfield
FROM mytable
WHERE myfield IN ('A', 'B')
) fields,
mytable
GROUP BY today, mytable.myfield
ORDER BY today DESC, mytable.myfield ASC
My concern is that I know exactly which values I want to display for myfield, and it seems inefficient to have a SELECT query that accesses mytable. I was wondering if there's some way I could do something like this in that sub-query:
SELECT ('A', 'B') AS myfield
FROM DUAL
I'm using an older version of Oracle where WITH clauses do not work.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您必须将它们作为不同的行，而不是不同的列。因此，您最终会得到
在这种情况下，只要
mytable中存在包含字段“A”和“B”的行，查询就应该是等效的。如果没有，那么您的子查询将返回原始子查询不会返回的行。You would have to get them as different rows, not different columns. So you'll end up with
In that case, the query should be equivalent as long as there are rows in
mytablewith fields 'A' and 'B'. If ever there aren't, then your subquery will return rows that the original subquery would not.为什么不升级 Oracle 版本？ with 子句首先添加到 Oracle 9.2 (2002) 中。您还在使用 Oracle 8 吗？
Why don't you upgrade your Oracle Version? The with-clause is added first to Oracle 9.2 (2002). Are you still using Oracle 8?
您在 FIELDS 子查询和 MYTABLE 之间没有联接，因此您的结果集将包含过去 30 天 MYFIELD 的每个值的一行。
但是，为什么不放弃子查询并仅在 MYTABLE.MYFIELD 上进行筛选，而不是添加该联接呢？另外，如果您担心性能，您应该将日期限制在
WHERE子句中，否则您将处理 MYTABLE 中的每一行。编辑
我已经针对一些测试数据运行了您的原始查询和修改后的查询。您只需相信我的话，这两个结果集实际上是相同的 - 或者您自己尝试一下:)
您的查询返回：
我的查询返回：
You don't have a join between the FIELDS sub-query and MYTABLE, so your resultset will contain a row for every value of MYFIELD for the last thirty days.
However, rather than adding that join, why not ditch the sub-query and just filter on MYTABLE.MYFIELD? Also, if you are concerned about performance you should bound the date in a
WHEREclause, otherwise you will process every row in MYTABLE.edit
I have run your original query and my revised one against some test data. You will just have to take my word for it that the two resulsets were in fact identical - or try it yourself :)
Your query returns:
My query returns: