mysql - 将 mysql 查询提示为单个查询

Question

我有 3 个问题

SELECT * FROM `admin_sprints` 
where CURDATE() < `sprint_start_date` and 
      CURDATE() <`sprint_end_date` - retuen type  as future

SELECT * FROM `admin_sprints` 
where CURDATE() > `sprint_start_date` and 
      CURDATE() >`sprint_end_date` - retuen type  as past

SELECT * FROM `admin_sprints` 
where CURDATE() between `sprint_start_date` and 
     `sprint_end_date` - retuen type  as current

我尝试将其合并为单个查询

所以我试过了

SELECT 
    *
FROM
    admin_sprints
ORDER BY (CASE
    WHEN sprint_start_date` and CURDATE() <`sprint_end_date`  THEN future
    ELSE admin_sprints` where CURDATE() > `sprint_start_date` and CURDATE() >`sprint_end_date then over
  ELSE current
END); 

admin_sprints
---------------|------------|-----------------|-----------------------
sprint_id      |sprint_name  |sprint_start_date|     sprint_end_date  |
1                Sprint1        2018-11-01          2018-11-13
2                sprint2          2018-11-14         2018-11-23
3                sprint 3        2018-11-24          2018-11-130

预期输出

sprint_id   sprint_name       type
    1        Sprint1            over
    2        Sprint2            Active
    3         Sprint3           future

我不知道方法是否正确。不幸的是它不起作用。任何帮助将不胜感激

Answer 1

您需要在SELECT 子句中使用CASE..WHEN 语句来相应地确定类型。尝试:

SELECT 
    *,
    CASE WHEN sprint_start_date < CURDATE() AND 
              sprint_end_date < CURDATE() 
           THEN 'over'
         WHEN CURDATE() BETWEEN sprint_start_date AND 
                                sprint_end_date 
           THEN 'active'
         ELSE 'future'
    END AS type
FROM
    admin_sprints