如何使用sql选择差异？

Question

我编写了一个 SQL 查询来检索数据，如下所示：

SELECT (MAX (b.filledqty) - MAX (a.filledqty)) AS filledtoday   
FROM clientordermas a, 
     clientordermas b  
WHERE a.clordid = 'w9110126'    
  AND b.clordid = 'w9110126'    
  AND (SELECT max(a.price) 
       FROM clientordermas a 
       WHERE a.clordid = 'w9110126') < 1000;

对于给定的 clodid，表中有 3 条记录，价格值为 800、900 1200。

所以，我需要的是得到1200和900之间的差，即300。

但是，上面的语句总是返回0。我应该得到的是 MAX (b.filledqty) 返回 1200 和 MAX (a.filledqty) 返回 900。

但这并没有发生。这不是我面临的确切问题，而是它的简化版本。

有人可以帮忙吗？

Answer 1

假设 1000 是一个神奇的值，确实应该进行硬编码：

SELECT MAX(filledqty) - MAX(CASE WHEN price < 1000 THEN filledqty ELSE NULL END)
  FROM clientordermas
  WHERE clordid='w9110126';

您可能希望将 ELSE NULL 替换为 ELSE 0，具体取决于您想要的结果（如果没有小于值）超过 1000。

编辑意识到我部分地误读了这个问题，所以我答案的第二部分是不相关的。在适当的情况下将上述内容更改为参考价格。

Answer 2

检查 max() < 的子句是什么业务规则？ 1000在做什么？您要查找 1000 以上的最大值减去 1000 以下的最大值吗？

如果是这种情况，那么：

select a.price - b.price
from (select clorid, max(price) as price from  clientordermas a WHERE a.clordid = 'w9110126' and price > 1000) a,
join (select clorid, max(price) as price from  clientordermas a WHERE a.clordid = 'w9110126' and price < 1000) b
on a.clorid = b.clorid;

虽然您需要扩展业务规则，但我认为可以处理 clorid 的值不超过 1000 的情况，或者它的值不低于 1000 的情况。

Answer 3

我猜你在 a 和 b 中有相同的记录。所以 1200 - 1200 的结果等于 0。

SELECT (MAX (b.filledqty) - MAX (a.filledqty)) AS filledtoday   
FROM clientordermas a, 
     clientordermas b  
WHERE a.clordid = 'w9110126'    
  AND b.clordid = 'w9110126'    
  AND a.price < 1000;

这对你来说有什么不同吗？

Answer 4

一种方法是（选择前 2 个不同的填充量之间的差异）：

SELECT ( ( SELECT max(c.filledqty) 
             FROM clientordermas c 
             WHERE c.clordid = 'w9110126'
         )
         - MAX (a.filledqty)
       ) AS filledtoday   
  FROM clientordermas a 
  WHERE a.clordid = 'w9110126'    
    AND a.filledqty <
        ( SELECT max(b.filledqty) 
            FROM clientordermas b 
            WHERE b.filledqty= 'w9110126'
        )
;

但是 Dave Costa 为您提供了一种在 Oracle 中效果更好的方法。

Answer 5

我认为你的 SQL 概念是错误的。

实际发生的情况是，您得到一个 3x3 = 9 行交叉联接表，其中包含重复的字段。

您的最后一个 where 子句是固定结果。所以这意味着 MAX(b.filledqty) - MAX(a.filledqty) 是在这个 9 行表上进行操作的。当然，两个最大值是相同的（即 1200）。结果为零。

如果您想要找到最大值和最小值之间的差异，请尝试如下操作：

SELECT TOP 1 (b.filledqty - a.filledqty) AS filledtoday
FROM clientordermas a INNER JOIN clientordermas b ON (a.clordid=b.clordid)
WHERE a.clordid = '...'
ORDER BY a.filledqty, b.filledqty DESC

此查询首先执行内部自连接，复制必要的列，然后对一列升序排序，另一列降序排序。第一个结果组合将具有最大值和最小值。

如果您想查找最大值和第二个最大值之间的差异，请尝试以下操作：

SELECT TOP 1 (b.filledqty - a.filledqty) AS filledtoday
FROM clientordermas a INNER JOIN clientordermas b ON (a.clordid=b.clordid)
WHERE a.filledqty <> b.filledqty    (*this filters out max-max combo*)
WHERE a.clordid = '...'
ORDER BY a.filledqty DESC, b.filledqty DESC

结果集中的顶行包含最大值和第二个最大值 - 第二个最大值是取与最大值不同的最高值。

如果你想找到最大值和最高值之间的差值 < 1000，试试这个：

SELECT TOP 1 (b.filledqty - a.filledqty) AS filledtoday
FROM clientordermas a INNER JOIN clientordermas b ON (a.clordid=b.clordid)
WHERE a.filledqty <> b.filledqty AND b.filledqty < 1000
WHERE a.clordid = '...'
ORDER BY a.filledqty DESC, b.filledqty DESC