时间戳

首页 > 解决方案 > 正确分组此查询以获得最小值？

sql - 正确分组此查询以获得最小值？

问题描述

桌车：

id  serial  size    batch
--------------------------
1   x99     Large    NULL
2   x99     Small    Q
3   x99     Med     NULL
4   x99     Large    K

与表代码分配连接两次：

Id  serial  size    batch   code    precedence
-----------------------------------------------
1   x99     Large   NULL    5000    1
2   x99     NULL      K     3000    2
3   x99     NULL      Q     2000    3
4   x99     NULL    NULL    500     4

使用以下内容：

select 
    c.*, 
    coalesce(ca1.code, ca2.code) as code, 
    min(coalesce(ca1.precedence, ca2.precedence)) as precedence
from
    cars c 
left join
    codeassignment ca1 on ca1.serial = c.serial 
                       and (ca1.size = c.size or ca1.batch = c.batch) 
left join
    codeassignment ca2 on ca2.serial = c.serial 
                       and ca1.size is null and ca2.size is null 
                       and ca1.batch is null and ca2.batch is null
group by 
    c.id, c.serial, c.size, c.batch, ca1.code, ca2.code

但它会导致 'Large' 和 batch 'K' 的匹配出现两次：

id  serial  size    batch   code    precedence
----------------------------------------------
1   x99     Large   A       5000    1
2   x99     Small   Q       2000    3
3   x99     Med     P       500     4
4   x99     Large   K       3000    2
4   x99     Large   K       5000    1

我想选择以上所有行，但对于最后两行，只有优先级最低的行（这就是我尝试的原因min(precedence)），但我认为 group by 正确执行此操作是错误的。所以基本上 where serial, sizeand batchare the same 应该只有一行。

这是一个小提琴：

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=47700d67170a7beb39bfc523ffdfbcfc

标签： sqlsql-server

解决方案

问题是您在查询中按“太多东西”进行分组，因此您最终会得到不想要它们的离散行。解决此问题的一种方法是将查询分为两个阶段，一个是获取每个 id、序列、大小和批次的最小优先级，然后是获取丢失的数据。像这样的东西：

WITH x AS (
    SELECT 
        c.id, 
        c.serial, 
        c.size, 
        c.batch, 
        MIN(ISNULL(ca1.precedence, ca2.precedence)) AS precedence
    FROM 
        cars c 
        LEFT JOIN codeassignment ca1
            ON ca1.serial = c.serial AND (ca1.size = c.size OR ca1.batch = c.batch) 
        LEFT JOIN codeassignment ca2 ON ca2.serial = c.serial AND ca1.size is null and ca2.size is null and ca1.batch is null and ca2.batch is null
     GROUP BY 
        c.id, 
        c.serial, 
        c.size, 
        c.batch)
SELECT
    x.*,
    ISNULL(ca1.code, ca2.code) AS code
FROM
    x
    INNER JOIN cars c ON c.id = x.id AND c.serial = x.serial AND c.size = x.size AND c.batch = x.batch
    LEFT JOIN codeassignment ca1
     ON ca1.serial = c.serial and (ca1.size = c.size or ca1.batch = c.batch) AND ca1.precedence = x.precedence
    LEFT JOIN codeassignment ca2
     ON ca2.serial = c.serial and
     ca1.size is null and ca2.size is null and ca1.batch is null and ca2.batch is null AND ca2.precedence = x.precedence;

由于某种原因，我无法使用它，但我可以在 SQL Server 2019 上本地运行它以获得：

id  serial  size    batch   precedence  code
1   x99     Large   A       1           5000
2   x99     Small   Q       3           2000
3   x99     Med     P       4           500
4   x99     Large   K       1           5000

推荐阅读