主页 / dba / 问题 / 76191
Accepted
Jakub Turcovsky
Asked: 2014-09-11 04:01:48 +0800 CST

如何重写子查询以仅返回 1 行

  • 772

我需要以某种方式检查查询结果是否是NULLMySQL 返回错误Subquery returns more than 1 row。请问我应该如何重写它才能正常工作？

SELECT DISTINCT user.id, user.username, user.name, user.surname, user.avatar,
(SELECT IF(ISNULL(
  (SELECT attendant.fk_user AS u
  FROM attendant
  WHERE fk_event = 1

  UNION

  SELECT organizer.fk_user AS u
  FROM organizer
  WHERE fk_event = 1

  UNION

  SELECT invitation.fk_user_to AS u
  FROM invitation
  WHERE fk_event = 1)), NULL, 1)) AS related
  ...
mysql mysql-5

3 个回答

  1. 将您的子查询选择更改为 count(*) 例如

    select o.OrderID, o.OrderAmount
from Orders o
where (select count(id) from Disputes d where d.CustomerID = o.CustomerID) <= 0
    • 2

  2. 或者更简单一点的是使用“WHERE EXISTS”。

    SELECT DISTINCT store_type FROM stores
WHERE EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type);

    有关详细信息，请参阅： https ://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

    对于子查询使用

    FROM (subquery1 union subqueryN) AS 相关

    • 1
  3. Best Answer

    经过几个小时的尝试和谷歌搜索，我已经解决了它。这篇关于stackoverflow的帖子很有帮助。而不是IFand ISNULLthere 应该CASE WHEN EXISTS像这样使用：

    SELECT DISTINCT user.id, user.username, user.name, user.surname, user.avatar,
CASE WHEN EXISTS
  (SELECT attendant.fk_user AS u
  FROM attendant
  WHERE fk_event = 1
  AND fk_user = `user`.id

  UNION

  SELECT organizer.fk_user AS u
  FROM organizer
  WHERE fk_event = 1
  AND fk_user = `user`.id

  UNION

  SELECT invitation.fk_user_to AS u
  FROM invitation
  WHERE fk_event = 1
  AND fk_user_to = `user`.id) THEN 1 ELSE 0 END AS related
  ...
    • 0

相关问题