SQL 标准中的 GREATEST 和 LEAST

发布于 2024-09-24 18:22:27 字数 427 浏览 3 评论 0 原文

我的理解是 GREATEST() 和 LEAST() 不是 SQL 标准的一部分，但很常见。

我想知道，有没有办法克隆 GREATEST 的功能，同时保持 SQL 标准？

SELECT id, GREATEST(1,2,3,4,5,6,7) AS number FROM table

完整查询：

  SELECT SUBSTR(section,1,2) AS campus, 
           AVG(GREATEST(maximum - enrolled, 0)) AS empty 
    FROM sectionrun 
   WHERE coursenumber = '105' AND subject = 'ENGL' 
GROUP BY campus

原文

My understanding is that GREATEST() and LEAST() are not part of the SQL standard, but are very common.

I'm wondering, is there a way to clone the functionality of GREATEST keeping within the SQL standard?

SELECT id, GREATEST(1,2,3,4,5,6,7) AS number FROM table

The fully query:

  SELECT SUBSTR(section,1,2) AS campus, 
           AVG(GREATEST(maximum - enrolled, 0)) AS empty 
    FROM sectionrun 
   WHERE coursenumber = '105' AND subject = 'ENGL' 
GROUP BY campus

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

东风软 2024-10-01 18:22:27

GREATEST(1,2,3,4,5,6,7) AS number

可以成为

(select max(tmp) from (
        select 1 tmp from dual
        union all
        select 2 tmp from dual
        union all
        select 3 tmp from dual
        union all
        select 4 tmp from dual
        union all
        select 5 tmp from dual
        union all
        select 6 tmp from dual
        union all
        select 7 tmp from dual
) ) AS number

GREATEST(1,2,3,4,5,6,7) AS number

can become

(select max(tmp) from (
        select 1 tmp from dual
        union all
        select 2 tmp from dual
        union all
        select 3 tmp from dual
        union all
        select 4 tmp from dual
        union all
        select 5 tmp from dual
        union all
        select 6 tmp from dual
        union all
        select 7 tmp from dual
) ) AS number

回复收藏 0 原文

皓月长歌 2024-10-01 18:22:27

您可以使用 CASE 表达式：

  SELECT SUBSTR(section,1,2) AS campus, 
           AVG(CASE WHEN maximum - enrolled > 0 
                    THEN maximum - enrolled
                    ELSE 0
               END) AS empty 
    FROM sectionrun 
   WHERE coursenumber = '105' AND subject = 'ENGL' 
GROUP BY campus

You can use the CASE expression:

  SELECT SUBSTR(section,1,2) AS campus, 
           AVG(CASE WHEN maximum - enrolled > 0 
                    THEN maximum - enrolled
                    ELSE 0
               END) AS empty 
    FROM sectionrun 
   WHERE coursenumber = '105' AND subject = 'ENGL' 
GROUP BY campus

回复收藏 0 原文