如何在 Sybase ASE 中模拟 GREATEST()? [英] How to simulate GREATEST() in Sybase ASE?

问题描述

大多数数据库都有类似GREATEST 函数，有时会很有用.至少这些数据库没有这样的功能:

Most databases have something like a GREATEST function, which can be useful some times. At least these databases don't have such a function:

• 德比
• SQL Server
• Sybase ASE
• Sybase SQL Anywhere

对于 SQL Server 和 Sybase SQL Anywhere，可以使用子查询和 UNION ALL 模拟该函数，如 这个问题在这里.一个例子:

For SQL Server and Sybase SQL Anywhere, the function can be simulated using subqueries and UNION ALL, as can be seen in this question here. An example:

-- SELECT GREATEST(field1, field2, field3) FROM my_table
SELECT (SELECT MAX(c) FROM 
                     (SELECT my_table.field1 AS c UNION ALL 
                      SELECT my_table.field2      UNION ALL
                      SELECT my_table.field3) T) AS greatest
FROM my_table

但这在 Sybase ASE 中不起作用.显然，子查询无权访问外部查询的 my_table 引用.我得到的错误是

But this doesn't work in Sybase ASE. Apparently, the subqueries don't have access to the outer query's my_table reference. The error I get is

列前缀my_table"与查询中使用的表名或别名不匹配.该表未在 FROM 子句中指定，或者它具有必须使用的相关名称

The column prefix 'my_table' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead

请注意，此问题不会出现在 Sybase SQL Anywhere 中.知道这里出了什么问题以及如何重写查询吗?

Note, this problem does not appear with Sybase SQL Anywhere. Any idea what's wrong here and how I could re-write the query?

我想避免

• 存储函数，因为我可能没有必要的资金来创建它们
• 冗长的CASE 表达式，因为嵌套CASE 表达式所需的所有比较的组合排列的表达式长度至少为O(n^2) 当 n 是 GREATEST
的参数数量

• Stored functions, as I may not have the necessary grants to create them
• Lengthy CASE expressions, as the expression length of the combined permutation of all comparisons needed with nested CASE expressions is at least O(n^2) when n is the number of parameters for GREATEST

推荐答案

据我所知，逻辑(忽略空值)是

As I understand it, the logic (ignoring nulls) is

SELECT CASE 
          WHEN field1 >= field2 
               AND field1 >= field3
             THEN field1
          WHEN field2 >= field3
             THEN field2
          ELSE field3
       END AS greatest
  FROM my_table;

...但只有在所有值都为 null 时才应该返回 null.

...but should only return null when all values are null.

我认为这更像是我想要做的事情(尽管 Sybase ASE 不支持公共表表达式):

I think this is more how I'd like to be able do things (although, Sybase ASE does not support common table expressions):

WITH my_table
     AS 
     (
      SELECT * 
        FROM (
              VALUES ('A', 1, 2, 3), 
                     ('B', 2, 3, 1), 
                     ('C', 3, 1, 2),
                     ('D', NULL, 2, 3), 
                     ('E', NULL, NULL, 3), 
                     ('F', NULL, 3, NULL), 
                     ('G', 1, NULL, 3), 
                     ('H', 1, 3, NULL), 
                     ('X', NULL, NULL, NULL)
             ) AS T (ID, field1, field2, field3)
     ), 
     T1
     AS
     (
      SELECT ID, field1 AS field_n
        FROM my_table
      UNION
      SELECT ID, field2 AS field_n
        FROM my_table
      UNION
      SELECT ID, field3 AS field_n
        FROM my_table
     )        
SELECT ID, MAX(field_n) AS greatest
  FROM T1
 GROUP 
    BY ID;

这篇关于如何在 Sybase ASE 中模拟 GREATEST()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！