有没有更有效的方法将来自多个select语句的第一行汇总到单个结果中? [英] Is there a more efficient way to aggregate the top row from multiple select statements into a single result?

问题描述

好吧，我在各种数据库中都有经验，但是没有专门针对Oracle的经验，所以仍然想尽一切办法处理事情(即缺少"Top"使我发疯！)无论如何，这就是我们正在尝试的方法做...

Ok, I have experience in various databases, but not Oracle specifically, so still trying to wrap my head around how it handles things (i.e. the lack of 'Top' drives me mad!) Anyway, here's what we're trying to do...

注意:我们正在使用Oracle11.我了解到Oracle 12已添加了一个新功能(即FETCH NEXT n ROWS ONLY)，但不幸的是我们不能使用它.

Note: We're using Oracle 11. I understand a new feature was added to Oracle 12 (i.e. FETCH NEXT n ROWS ONLY) but we can't use that unfortunately.

首先，我们有一个articles表(从技术上讲是一个视图，但在这里我将其简化).这是相关的字段...

To start, we have an articles table (technically it's a view, but I'm simplifying it here). Here are the relevant fields...

• ID(整数，主键)
• 标题(字符串)
• 正文(字符串)
• 收藏夹("Y"或"N")
• 已精选("Y"或"N")
• 发布日期(日期)

可以将多篇文章标记为收藏和/或精选.

Multiple articles can be marked as favorites and/or featured.

我们要返回的是具有以下顺序的结果集...

What we want to return is a result set that has the following, in order...

1. 最近发布的带有IsFeatured = 'Y'的文章.
2. 最近发布的带有IsFavorite = 'Y'的文章不是#1中的行(如果最新的特征也是最喜欢的，则避免重复，而是选择 next 最喜欢的行)如果有的话
3. 最近发表的三篇不是#1或#2的文章

1. The most-recently published article with IsFeatured = 'Y', if any.
2. The most-recently published article with IsFavorite = 'Y' that's not the row from #1 (this avoids duplicates if the most recent featured is also the most recent favorited and instead selects the next favorited row) if any
3. The three most-recently published articles that are not #1 or #2, if any

这是到目前为止我要提出的内容(在此处进行了剪切/粘贴/编辑，因此可能会有一些错别字)，但这对我来说感觉太笨拙"了……就像我在浪费很多不必要的东西一样处理和迭代.

Here's what I've come up with so far (cut/pasted/edited here so there may be some typos), but this just feels so 'clunky' to me... like I'm wasting a lot of unnecessary processing and iterations.

WITH mostRecentlyFeatured as (
    Select * from (
        Select 2 as MAJOR_SORT, A.*
        From Articles A
        where IsFeatured = 'Y'
        order by DatePublished DESC
    )
    where ROWNUM = 1
),
mostRecentlyFavorited as (
    Select * from (
        Select 1 as MAJOR_SORT, A.*
        From Articles A
        minus Select * From mostRecentlyFeatured
        where IsFavorite = 'Y'
        order by DatePublished DESC
    )
    where ROWNUM = 1
),
topThreeOthers as (
    Select * from (
        select 0 as MAJOR_SORT, A.*
        from Articles
        minus
        SELECT * from mostRecentlyFeatured
        minus
        SELECT * from mostRecentlyFavorited
        order by DatePublished desc
    )
    where ROWNUM <= 3
),
finalRows as (
    Select * from mostRecentlyFeatured
    union all
    Select * from mostRecentlyFavorited
    union all
    select * from topThreeOthers
)
Select * from finalRows
Order By MAJOR_SORT    DESC,
         DatePublished DESC;

这并不是最不常见的查询，因此我无法想象没有更好的方法可以执行此操作，但是我只是没有看到它.有吗?

This isn't the most uncommon of queries so I can't imagine there isn't a better way to do this, but I'm just not yet seeing it. So is there?

推荐答案

缺少上衣"让我发疯了

the lack of 'Top' drives me mad

由于您使用的是Oracle 11g，因此不支持TOP-n.因此，ROWNUM是唯一的方法.请参阅 ROWNUM如何在分页查询中工作.

Since you are on Oracle 11g, TOP-n is not supported. So ROWNUM is the only way to go. See How ROWNUM works in pagination query.

例如，使用ROWNUM的OFFSET为4，FETCH接下来的4:

For example, an OFFSET of 4 and FETCH next 4 using ROWNUM:

SQL> SELECT val
     FROM   (SELECT val, rownum AS rnum
            FROM   (SELECT val
                    FROM   order_test
                    ORDER BY val)
            WHERE rownum <= 8)
     WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

从病房的Oracle 12c起，您可以使用顶部- n行限制功能.由于您尚未提供任何示例数据，因此这里是一个简单的演示:

From Oracle 12c on ward, you could use the Top-n row limiting feature. Since you haven't provided any sample data, here is a simple demo:

SQL> select * from order_test order by val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

前4行:

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  FETCH FIRST 4 ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

后4行(看偏移量):

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

最后，接下来的4行与OFFSET 8行:

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  OFFSET 8 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         5
         5
         6
         6

使用上述方法，您可以得到最新发布的标有"IsFeatured"的文章，如下所示:

Using the above approach, you could get the most-recently published article marked with 'IsFeatured' as follows:

SELECT * FROM articles
WHERE isfeatured = true
ORDER BY datepublished DESC
FETCH FIRST ONE ROW ONLY;

这篇关于有没有更有效的方法将来自多个select语句的第一行汇总到单个结果中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！