(My)SQL:如果子查询不是空集，则返回子查询 [英] (My)SQL: If subquery is not an empty set, return subquery

问题描述

这个问题专门针对 MySQL，但我试图以适用标准 SQL 的方式提问.

This question is directed specifically toward MySQL, but I'm trying to ask it in such a way that standard SQL is applicable.

上下文:我试图通过以下方式确定结束日期:如果在输入的开始日期之后存在另一个开始日期，则使用现有的开始日期作为结束日期；否则，结束日期应为输入的开始日期后 30 天.

Context: I am trying to determine an end date in the following way: if there exists another start date after the entered start date, use the existing start date as the end date; otherwise, the end date should be 30 days after the entered start date.

我尝试过的解决方案类似于以下内容:

The solution I've tried is similar to the following:

SELECT
  IF(
    EXISTS(  
      SELECT
        DISTINCT start_date
      FROM table
      WHERE ? < start_date AND
            identifier = ?
      ORDER BY start_date
      LIMIT 1
    ), (
    SELECT
      DISTINCT start_date
    FROM table
    WHERE ? < start_date AND
          identifier = ?
    ORDER BY start_date
    LIMIT 1),
    DATE_ADD(?, INTERVAL 30 DAY)
  ) AS end_date

我的解决方案有效，但我希望有一个更优雅、不重复的解决方案.

My solution works, but I was hoping there were a more elegant, non-repetitive solution.

通用解决方案将是这样一种解决方案——如果存在子查询——则返回子查询的值；否则，可以返回其他东西.

The generic solution would be one which—if a subquery exists—returns the values from the subquery; otherwise, something else can be returned.

推荐答案

针对您自己的回答，我建议使用:

In response to your own answer I'd suggest to use:

SELECT
  COALESCE((
    SELECT MIN(start_date)
    FROM TABLE
    WHERE start_date > ? 
    AND   identifier = ?), (
    SELECT
      DATE_ADD(?, INTERVAL 30 DAY)
    )) AS end_date 

恕我直言，似乎更容易理解.尽管它看起来不同，但它在幕后几乎做同样的事情.

Seems easier to understand IMHO. And even though it looks different, it pretty much does the same things behind the scenes.

这篇关于(My)SQL:如果子查询不是空集，则返回子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！