主页 / dba / 问题 / 325638
Accepted
Elikill58
Asked: 2023-04-05 21:06:44 +0800 CST

如何从主查询的子查询返回内容列表？

  • 772

我有一个具有子查询的创建。作为示例，这是一个与我自己的相似的查询。就我个人而言，我在表上有更多的表和更多的参数（例如，子查询有 left join）。我为这个问题简化了它。

SELECT
   Content,
   CASE 
      WHEN S.User IN (SELECT UserId FROM Users WHERE Deleted = false) AND S.Permission Like '%ADMIN%' THEN 'Admin'
      ELSE 'Basic'
   END AS UserType,
   CASE 
      WHEN S.User IN (SELECT UserId FROM Users WHERE Deleted = false) THEN 'Here'
      ELSE 'Not'
   END AS Exist
FROM (
   SELECT *
   FROM MyTable
   WHERE Deleted = false
) as S;

我想将SELECT UserId FROM Users WHERE Deleted = false查询放入主表中，因为我多次使用结果。在示例中，我使用了内容 2 次。它仅用于优化。希望它能真正帮助表演。

我试过这样的事情：

SELECT
   Content,
   CASE 
      WHEN S.User IN (AvailableUsers) THEN 'Here'
      ELSE 'Not'
   END AS Exist,
   CASE 
      WHEN S.User IN (AvailableUsers) AND S.Permission Like '%ADMIN%' THEN 'Admin'
      ELSE 'Basic'
   END AS UserType
FROM (
   SELECT *, (SELECT UserId FROM Users WHERE Deleted = false) as AvailableUsers
   FROM MyTable
   WHERE Deleted = false
) as S;

但我明白了Subquery returns more than 1 row。我怎样才能让它工作并只运行一次查询？

PS：我没有实际数据，因为即使是我，在检查查询时，也没有准确地寻找数据。

mariadb

1 个回答

  1. Best Answer

    我找到了一个方法。我正在检查 的查询MyTable，存储为变量并使用它。我这样做：

    SELECT
   Content,
   CASE 
      WHEN S.UserAvailable = 1 THEN 'Here'
      ELSE 'Not'
   END AS Exist,
   CASE 
      WHEN S.UserAvailable = 1 AND S.Permission Like '%ADMIN%' THEN 'Admin'
      ELSE 'Basic'
   END AS UserType
FROM (
   SELECT *,
      CASE
         WHEN User IN (SELECT UserId FROM Users WHERE Deleted = false) THEN 1
         ELSE 0
      END AS UserAvailable
   FROM MyTable
   WHERE Deleted = false
) as S;

    然后它起作用了，我只提出了一次请求

    • 0

相关问题