MySql CASE 执行查询返回操作数应包含 1 列

Question

以下代码返回操作数应包含 1 列。

SELECT 
CASE WHEN
(SELECT COUNT(1) FROM `student` WHERE `join_date` > '2017-03-21 09:00:00') > 0
THEN
(SELECT * FROM `student` >= CAST(CAST('2017-03-21 09:00:00' AS DATE) AS DATETIME)
END

但以下有效。为什么？

SELECT 
CASE WHEN
(SELECT COUNT(1) FROM `student` WHERE `join_date` > '2017-03-21 00:00:00') > 0
THEN
(SELECT `foo`)
ELSE
(SELECT `bar`)
END

如果我想执行检查并根据检查结果执行2个不同的查询，该怎么办？

我想达到以下结果(在sql中工作正常)

IF (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000
   EXECUTE (SELECT * FROM table1)
ELSE
   EXECUTE (SELECT * FROM table2)

Answer 1

如果你强制你的子选择器只返回一行，那么第一个选择也会起作用

  SELECT 
  CASE WHEN
  (SELECT COUNT(1) FROM `student` WHERE `join_date` > '2017-03-21 00:00:00') > 0
  THEN
  (SELECT * FROM `student` order by your_column limit 1)
  ELSE
  (SELECT * FROM `teacher` order by your_column limit 1)
  END

您还应该在您需要的列(在名为 your_column 的示例中)上添加适当的 order by 以获得有效的第一行