group by (ORACLE 9i) 中的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16946503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subquery in group by (ORACLE 9i)
提问by Zzz
I have a query that looks similar to the one below (albeit more complicated). When running it I get the following error: ORA-22818: Subquery expressions not allowed herein my group by statement.
我有一个类似于下面的查询（尽管更复杂）。运行它时，我收到以下错误：ORA-22818: Subquery expressions not allowed here在我的 group by 语句中。
What is the best way for me to get around this issue?
解决这个问题的最佳方法是什么？
SELECT table1.ID
NVL(fget_office(fget_last_catc_id_by_date((SELECT MAX(table3.date) FROM table3 INNER JOIN table1 ON table1.ID = table3.id),table1.NUM), fget_max_split_line_no('FILL',(SELECT MAX table3.tc_id) FROM table3 INNER JOIN table1 ON table1.ID = table3.ID INNER JOIN table4 ON table3.tc_id = table3.tc_id))), table1.distribution) "OFFICE", --eper.DISTRIBUTION "OFFICE",
table1.name
FROM table1
LEFT JOIN table2
ON table1.ID = table2.ID
WHERE table1.company in ('CP01', 'CP02')
GROUP BY table1.ID,
NVL(fget_office(fget_last_catc_id_by_date((SELECT MAX(table3.date) FROM table3 INNER JOIN table1 ON table1.ID = table3.id),table1.NUM), fget_max_split_line_no('FILL',(SELECT MAX table3.tc_id) FROM table3 INNER JOIN table1 ON table1.ID = table3.ID INNER JOIN table4 ON table3.tc_id = table3.tc_id))), table1.distribution),
table1.name
回答by Ed Gibbs
Your code sample looks like you're using GROUP BYjust to pull distinct rows. In that case, try this:
您的代码示例看起来GROUP BY只是用于提取不同的行。在这种情况下，试试这个：
SELECT DISTINCT
table1.ID
NVL(fget_office(fget_last_catc_id_by_date((SELECT MAX(table3.date) FROM table3 INNER JOIN table1 ON table1.ID = table3.id),table1.NUM), fget_max_split_line_no('FILL',(SELECT MAX table3.tc_id) FROM table3 INNER JOIN table1 ON table1.ID = table3.ID INNER JOIN table4 ON table3.tc_id = table3.tc_id))), table1.distribution) "OFFICE", --eper.DISTRIBUTION "OFFICE",
table1.name
FROM table1
LEFT JOIN table2
ON table1.ID = table2.ID
WHERE table1.company in ('CP01', 'CP02')
In case you really are doing aggregation in your "real" query, a quick workaround would be to use a Common Table Expression (CTE), which is supported in Oracle 9i. This example assumes you're summing a column named some_value:
如果您真的在“真实”查询中进行聚合，一个快速的解决方法是使用 Oracle 9i 支持的通用表表达式 (CTE)。此示例假设您正在对名为 的列求和some_value：
WITH x AS (
SELECT table1.ID
NVL(fget_office(fget_last_catc_id_by_date((SELECT MAX(table3.date) FROM table3 INNER JOIN table1 ON table1.ID = table3.id),table1.NUM), fget_max_split_line_no('FILL',(SELECT MAX table3.tc_id) FROM table3 INNER JOIN table1 ON table1.ID = table3.ID INNER JOIN table4 ON table3.tc_id = table3.tc_id))), table1.distribution) "OFFICE", --eper.DISTRIBUTION "OFFICE",
table1.name,
some_value
FROM table1
LEFT JOIN table2
ON table1.ID = table2.ID
WHERE table1.company in ('CP01', 'CP02')
)
SELECT ID, OFFICE, name, SUM(some_value)
FROM x
GROUP BY ID, Office, name
回答by David Aldridge
It looks to me like the results of those functions are directly or indirectly determined by the values of table1.
在我看来，这些函数的结果直接或间接由 table1 的值决定。
If so, you can perform the distinct operation on a simple set of data from table1 and table2 and apply the functions afterwards. This would reduce the number of calls to the functions and improve efficiency.
如果是这样，您可以对来自 table1 和 table2 的一组简单数据执行不同的操作，然后应用这些函数。这将减少对函数的调用次数并提高效率。
with cte1 as (
select
table1.id
table1.num
table1.distribution,
table1.name
from
table1 left join table2 on (table1.id = table2.id)
where
table1.company in ('CP01', 'CP02'))
select
cte1.id,
coalesce(
fget_office(
fget_last_catc_id_by_date(
(select max(table3.date)
from table3 inner join cte1 on cte1.id = table3.id),
cte1.num),
fget_max_split_line_no(
'FILL',
(select max(table3.tc_id)
from table3 inner join cte1 on cte1.id = table3.id
inner join table4 on table3.tc_id = table3.tc_id))),
table1.distribution) office
cte1.name
from cte1
/
You might as well get used to using Coalesce() instead of Nvl() -- it's ANSI compliant, more flexible, and features short-circuit evaluation so it's handy of your codebase has a lot of PL/SQL functions that get called in SQL.
您不妨习惯于使用 Coalesce() 而不是 Nvl() —— 它符合 ANSI，更灵活，并具有短路评估功能，因此它很方便您的代码库有很多在 SQL 中调用的 PL/SQL 函数.
