棘手的 SQL 问题

Question

我有一个像这样的表...

Key  Seq     Val 
A     1      123
A     4      129 
A     9      123
A     10     105 
B     3      100
B     6      101
B     12     102

我想找到值（在本例中为 123）之前（在本例中为 A,1）和之后（在本例中为 A,9）相同的情况（如 A,4） ）。 seq严格增加，但可能有间隙。 有什么建议吗？

Answer 1

虽然我只在 sql server 2005 中对此进行了测试（因为我没有 2000 实例），但在用真实的表替换 @t 后，这应该仍然可以在该平台上工作。

select k, seq, val
from (
    select k, seq, val,
          (select top 1 val from @t aux where aux.k = main.k and aux.seq < main.seq order by seq desc) as prev_val,
          (select top 1 val from @t aux where aux.k = main.k and aux.seq > main.seq order by seq asc) as next_val
    from @t main
) x
where prev_val = next_val

如果您在 k, seq 上有一个索引，那么性能应该不会太差，因为相关子查询是简单的索引扫描。

遗憾的是，我认为 SQL Server 路线图上不支持 lag 和 lead 函数。

[如果有人感兴趣，我的意思是在某些数据库中你可以写：

select key, seq, val
from (
    select key, seq, val,
           lag(val) over(partition by key order by seq) as prev_val,
           lead(val) over(partition by key order by seq) as next_val
    from t
    ) x
where prev_val = next_val;

如果你想查看前两个或更多值，这肯定会发挥作用，因为你可以写 lag(val, 2)< /code> 向后查看 2 行等。查找紧邻的上一个或下一个值是一个更简单的情况，select top 1 ... 可以很好地处理。 ]

Answer 2

我不希望这会跨越数千行：

SELECT
    * /* TODO - pick columns */
FROM
    Table t1
        inner join
    Table t2
        on
            t1.Key = t2.Key and
            t1.Seq < t2.Seq
        inner join
    Table t3
        on
            t1.Key = t3.Key and
            t1.Seq > t3.Seq and
            t2.Val = t3.Val
        left join
    Table t4
        on
            t1.Key = t4.Key and
            t1.Seq < t4.Seq and
            t4.Seq < t2.Seq
        left join
    Table t5
        on
            t1.Key = t5.Key and
            t1.Seq > t5.Seq and
            t5.Seq > t3.Seq
WHERE
     t4.Key is null and t5.Key is null

基本上，表的前 3 个实例将表本身连接起来，根据您的定义找到围绕“有趣”行的两行。后续连接（t4 和 t5）确保 t2 和 t3 搜索找到的行最接近 t1 行。

Answer 3

编辑：我在你说 SQL Server 2000 之前写了这篇文章。这适用于 SQL Server 2005 或更高版本，所以它不会帮助你，但我将把它留在这里供后代使用:)

我正在使用 CTE 向表中添加顺序（不间断）排序，然后连接两次以获取上一个和接下来的几行。

declare @t table (k char(1), seq int, val int)

insert into @t values ('A', 1, 100)
insert into @t values ('A', 4, 101)
insert into @t values ('A', 9, 100)
insert into @t values ('A', 10, 105)
insert into @t values ('B', 3, 100)
insert into @t values ('B', 6, 101)
insert into @t values ('B', 12, 102)

; with q as (
    select *, row_number() over (partition by k order by seq) [rownum] from @t
)     
select * 
from q
join q q1 on q1.rownum=q.rownum-1 and q.k=q1.k
join q q2 on q2.rownum=q.rownum+1 and q.k=q2.k
where q1.val=q2.val

Answer 4

如果您不需要 seq 字段，则此方法有效：

;with cte as
(
select COUNT( 1 ) as cnt, val, [key] from tbl 
group by val, [key]
)
select * from cte where cnt > 1

如果您这样做：

;with cte as
(
select COUNT( 1 ) as cnt, val, [key] from tbl 
group by val, [key]
)
select tbl.* from tbl inner join cte on cte.cnt > 1 and cte.[Key] = tbl.[Key] and cte.Val = tbl.Val

编辑：一种不会为您提供 seq 的 tmptbl 方法：

CREATE TABLE #tmptbl (
    cnt int,
    [key] nchar(10),
    Val nchar(10)
 )

insert into #tmptbl
select COUNT( 1 ) as cnt, [key], Val from tbl 
group by tbl.Val, tbl.[key]
select * from #tmptbl where cnt > 1

drop table #tmptbl

根据字段的类型，这可能很容易更改为给您 seq出色地。

Answer 5

假设表的名称是“Table”，这里是普通的 sql。

SELECT 
Key, 
Seq 
from Table A
WHERE EXISTS
 (SELECT 1 FROM Table B, Table C 
   WHERE B.Key = A.Key
     AND C.Key = A.Key
     AND B.Seq = (SELECT MAX(Seq) FROM Table D WHERE D.Key = A.Key AND D.Seq < A.Seq) --This ensures that B retrieves previous row
     AND C.Seq = (SELECT MIN(Seq) FROM Table E WHERE E.Key = A.Key AND E.Seq > A.Seq) --This ensures that C retrieves next row
     AND B.Val = C.Val
 )