将mysql查询抱怨为单个查询

Question

我有三个问题

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

我不知道方法是正确的，或者not.Bad的运气--这不是working.Any的帮助--将不胜感激

Answer 1

您需要在您的CASE..WHEN子句中使用SELECT语句来确定相应的type。尝试：

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