我有一个具有子查询的创建。作为示例,这是一个与我自己的相似的查询。就我个人而言,我在表上有更多的表和更多的参数(例如,子查询有 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:我没有实际数据,因为即使是我,在检查查询时,也没有准确地寻找数据。