Oracle SQL：合并多个子查询，无需多次运行原始查询

发布于 2024-08-31 23:47:46 字数 437 浏览 6 评论 0原文

因此，我有一个非常大的数据库，需要处理大约 1% 的数据子集，并将其转储到 Excel 电子表格中以制作图表。理想情况下，我可以选择数据子集，然后对其运行多个选择查询，然后将它们合并在一起。这可能吗？我似乎找不到其他人尝试这样做，并且会大大提高我当前查询的性能。现在我有这样的事情：

SELECT (
     SELECT (
          SELECT(
                long list of requirements
          )
          UNION
          SELECT(
                slightly different long list of requirements
          )
     )
)

如果我可以将两个长需求的共性分组，并在联合的两个选择语句之间有简单的区别，那就太好了。

原文

So I've got a very large database, and need to work on a subset ~1% of the data to dump into an excel spreadsheet to make a graph. Ideally, I could select out the subset of data and then run multiple select queries on that, which are then UNION'ed together. Is this even possible? I can't seem to find anyone else trying to do this and would improve the performance of my current query quite a bit. Right now I have something like this:

SELECT (
     SELECT (
          SELECT(
                long list of requirements
          )
          UNION
          SELECT(
                slightly different long list of requirements
          )
     )
)

and it would be nice if i could group the commonalities of the two long requirements and have simple differences between the two select statements being unioned.

分享到QQ

分享到微博

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

发布评论

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

慈悲佛祖 2024-09-07 23:47:46

如果您使用的是 Oracle 9i 或更高版本，则可以使用子查询因子分解（在 SQL Server 中又称为公用表表达式 (CTE)）。它使用WITH语法：

WITH inline_view_name AS (
   SELECT *
     FROM YOUR_TABLEs)
SELECT 
  FROM inline_view_name a
 WHERE ...
UNION ALL
SELECT 
  FROM inline_view_name b
 WHERE ...

...并且可以像您在示例中看到的那样重用。

If you're using Oracle 9i or later, you can make use of subquery factoring (AKA Common Table Expression (CTE) in SQL Server). It uses the WITH syntax:

WITH inline_view_name AS (
   SELECT *
     FROM YOUR_TABLEs)
SELECT 
  FROM inline_view_name a
 WHERE ...
UNION ALL
SELECT 
  FROM inline_view_name b
 WHERE ...

...and can be reused like you see in the example.

回复收藏 0 原文

~没有更多了~