SQL Server 使用lag()窗口函数扩展限制SQL查询结果

xsuvu9jc  于 2023-01-12  发布在  其他
关注(0)|答案(2)|浏览(166)

我有一个表,表中的列idscoreparent_idscore排序,我问了here中的一个场景。
与上一个问题的不同之处在于parent_id可能会显示在多个行上,而不是必要的序列行上。
| 身份证|刻痕|父代标识|
| - ------|- ------|- ------|
| 小行星5859|十个|小行星5859|
| 小行星2157|九|小行星5859|
| 小行星21064|八个|小行星21064|
| 小行星5|七|小行星5|
| 小行星34384599|六个|小行星5|
| 小行星1675761|五个|小行星5859|
| 小行星3465729|四个|小行星3465729|
| 小行星401202|三个|小行星401203|
| 小行星1817458|第二章|小行星1817458|
我希望使用相同的顺序查询此表中的所有列,但将结果限制为至少5行,以满足等于5的唯一parent_id编号。因此,parent_id仅包含5个ID:5859、21064154、51992、3465729、401203
预期结果如下:
| 身份证|刻痕|父代标识|
| - ------|- ------|- ------|
| 小行星5859|十个|小行星5859|
| 小行星2157|九|小行星5859|
| 小行星21064|八个|小行星21064|
| 小行星5|七|小行星5|
| 小行星34384599|六个|小行星5|
| 小行星1675761|五个|小行星5859|
| 小行星3465729|四个|小行星3465729|
| 小行星401202|三个|小行星401203|
使用lag的解决方案仅适用于具有相同parent_id值的下一行。如果使用Java,我们可以使用SET来存储parent_id,并对唯一的parent_id进行计数,但如何用SQL编写呢?

select id, score, parent_id
from (
  select *, Sum(diff) over(order by score desc)seq
  from (
     select *, 
       case when Lag(parent_id) over(order by score desc) = parent_id then 0 else 1 end diff
    from t
  )t
)d
where seq <= 5
order by score desc;
gjmwrych

gjmwrych1#

也许这对您有用,这是一个现有查询的重构,但也是使用 exists 来代替 lag 的一种方法,它可以满足非连续行的需要,试试看吧:

select id, score, parent_id
from (
  select *, Sum(keep) over (order by score desc) seq
  from (
    select *, 
      case when exists (
        select * from t t2 
        where t2.parent_id = t.parent_id and t2.score > t.score
      ) then 0 else 1 end keep
      from t
  )t
)s
where seq <= 5
order by score desc;
lndjwyie

lndjwyie2#

可以考虑的一种方法是:

SELECT t1.*
FROM t t1
WHERE
 (SELECT count(distinct parent_id)
 FROM t t2
 WHERE t2.score >= t1.score) <= 5

您可以看到a Fiddle hereWHERE子句只是计算score〉=当前行的不同parent_id的数量。

相关问题