主页 / coding / 问题 / 79193392
Accepted
GIN
Asked: 2024-11-16 01:24:09 +0800 CST

重写查询

  • 772

我有一个场景，我只想根据 TID 列中的列值从一个组中取出一行。例如，我在表中的关键列是 SRN、GIO 和 FID。现在有另一列 TID，现在在一个组中，当 case 语句中给出的两个 TIDS 存在于组中时，我需要取一个等级为 1（计算）的记录，否则我们可以取另一条记录。还请注意，有些记录不属于此条件，也应该作为输出。给出示例记录，以及下面的预期输出

输入数据

顺应性 吉奥 吉奥 工时信息
ASD123 1 100 218
ASD123 1 100 303
ASD123 1 100 454
ASD123 1 100 678
DEF234 0 6566 2222
DEF234 0 6566 307
DEF234 0 6566 2宫内节育器
WER453 1 8123 308
WER453 1 8123 2wER
WER453 1 8123 56TY

我编写了如下查询，但是由于存在多个案例条件，因此会对每个条件进行全表扫描，这会导致性能下降，因为表中有很多记录。那么有没有更好的方法来重写这个查询。

    sel case when TID in('218','303')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='218' then 1 else 2 END) end COL_3 ,
case when TID in('2222','307')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2222' then 1 else 2 END) end COL_4 ,
case when TID in('2202','308')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2202' then 1 else 2 END) end COL_5 ,
case when TID in('2220','305')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2220' then 1 else 2 END) end COL_6 ,
case when TID in('2211','311')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2211' then 1 else 2 END) end COL_7
from table where
((COL_3 = 1 or COL_4 = 1 OR COL_5=1 OR COL_6=1 OR COL_7=1)
OR
(COL_3 is null and COL_4 is null and COL_5 is null and COL_6 is null and COL_7 is null)
)

预期输出

顺应性 吉奥 吉奥 工时信息
ASD123 1 100 218
ASD123 1 100 454
ASD123 1 100 678
DEF234 0 6566 2222
DEF234 0 6566 2宫内节育器
WER453 1 8123 308
WER453 1 8123 2wER
WER453 1 8123 56TY
sql

3 个回答

  1. Best Answer

    为了重写查询以获得更好的性能并避免多次全表扫描，您可以使用单个排名查询和CASE动态处理所有条件的语句。这样就无需RANK对每个条件执行多个操作，而是将逻辑合并到单个查询中。

    您可以尝试以下操作：

    WITH RankedData AS (
    SELECT
        SRN,
        GIO,
        FID,
        TID,
        RANK() OVER (
            PARTITION BY SRN, GIO, FID
            ORDER BY 
                CASE
                    WHEN TID IN ('218', '303') THEN 
                        CASE WHEN TID = '218' THEN 1 ELSE 2 END
                    WHEN TID IN ('2222', '307') THEN 
                        CASE WHEN TID = '2222' THEN 1 ELSE 2 END
                    WHEN TID IN ('2202', '308') THEN 
                        CASE WHEN TID = '2202' THEN 1 ELSE 2 END
                    WHEN TID IN ('2220', '305') THEN 
                        CASE WHEN TID = '2220' THEN 1 ELSE 2 END
                    WHEN TID IN ('2211', '311') THEN 
                        CASE WHEN TID = '2211' THEN 1 ELSE 2 END
                    ELSE NULL
                END
        ) AS rank_in_group
    FROM your_table
),
FilteredData AS (
    SELECT *
    FROM RankedData
    WHERE rank_in_group = 1
    UNION ALL
    SELECT *
    FROM RankedData
    WHERE rank_in_group IS NULL
)
SELECT SRN, GIO, FID, TID
FROM FilteredData;
    • 0

  2. 它可以组合在一个案例陈述中，然后对所需的 TID 进行排名。

    你可以在这里查看FIDDLE

    WITH RankedData AS (
  SELECT
    SRN,
    GIO,
    FID,
    TID,
    ROW_NUMBER() OVER (PARTITION BY SRN, GIO, FID 
                        ORDER BY CASE WHEN TID IN ('218', '303', '2222', '307', '2202', '308', '2220', '305', '2211', '311') THEN 1 ELSE 2 END) AS rn
  FROM
    input
)
SELECT
  SRN,
  GIO,
  FID,
  TID
FROM
  RankedData
WHERE
  (rn = 1 AND TID IN ('218', '303', '2222', '307', '2202', '308', '2220', '305', '2211', '311'))
  OR (rn > 1 
  AND TID NOT IN ('218', '303', '2222', '307', '2202', '308', '2220', '305', '2211', '311'));
    • 0

  3. 您可以创建一个具有预定义 TID 对和相应顺序的 CTE。

    WITH
  pairs AS 
    ( Select 1 as pair, '218' as tid, 1 as ord Union All
      Select 1, '303',  2 Union All
      Select 2, '2222', 1 Union All
      Select 2, '307',  2 Union All
      Select 3, '2202', 1 Union All
      Select 3, '308',  2 Union All
      Select 4, '2220', 1 Union All
      Select 4, '305',  2 Union All
      Select 5, '2211', 1 Union All
      Select 5, '311',  2 
    )

    ...现在将 Left Join 将 cte 与您的表数据配对，计算排名并过滤为排名 1 或没有匹配对...

    --      M a i n    S Q L : 
SELECT    a.srn, a.gio, a.fid, a.tid
FROM     ( Select    t.*,
                     Coalesce(p.pair, 0) as pair, 
                     Rank() Over(Partition By t.srn, t.gio, t.fid Order By p.ord) as rnk
           From      tbl t
           Left Join pairs p ON( p.tid = t.tid)
         ) a
WHERE     a.rnk = 1 OR a.pair = 0
ORDER BY  a.srn, a.gio, a.fid;

    ...此代码使用 Left Join 而不是 Case 表达式，从而简化了 Where 子句条件......

    结果 ：

    斯伦 吉奥 菲德 时间
    ASD123 1 100 218
    ASD123 1 100 454
    ASD123 1 100 678
    DEF234 0 6566 2222
    DEF234 0 6566 2宫内节育器
    WER453 1 8123 308
    WER453 1 8123 2wER
    WER453 1 8123 56TY

    小提琴

    • 0

相关问题