英文:
Use inner select value in outer select
问题
这是我的MySQL查询：
BEGIN
SELECT
sID vID,
sSSN vSSN,
CONCAT_WS('; ',
IF(COUNT(DISTINCT pCheck) = 0, "Employee had no paycheck data during the month", NULL),
IF(COUNT(DISTINCT sDate) > 1, "Represents multiple benefit records", NULL)
) comments,
sLocation vLocation
FROM groups
WHERE (sSSN, sID) NOT IN (
SELECT pSSN, pID, pCheck
FROM vGroups
WHERE pID = pvID
AND pDate >= pvStartDate
)
AND sID = pID
GROUP BY sSSN, sID;
END$$
你可以在外部SELECT语句中使用pCheck，我已经将它包括在内部的子查询中，这应该可以解决你的问题。
英文:
Here's a nice, easy question for you all to get more reputation on.
Here's my mysql query:
BEGIN
SELECT
sID vID,
sSSN vSSN,
CONCAT_WS('; ',
IF(COUNT(DISTINCT pCheck) = 0, "Employee had no paycheck data during the month", NULL),
IF(COUNT(DISTINCT sDate) > 1, "Represents multiple benefit records", NULL)
) comments,
sLocation vLocation
FROM groups
WHERE (sSSN, sID) NOT IN (
SELECT pSSN, pID, pCheck
FROM vGroups
WHERE pID = pvID
AND pDate >= pvStartDate
)
AND sID = pID
GROUP BY sSSN, sID;
END$$
Why do I get an error when trying to use pCheck in the outer SELECT statement? Is it because it's only being defined further down?
I was thinking perhaps an AS after the inner SELECT would fix this but I got an error when I tried it, and either way I only want pCheck, not pSSN or pID.
Please, all you nice knowledgeable people out there, is there a way I can use pCheck where I need it?
答案1
得分: 0
在你的查询中没有外部或内部选择查询...
你从表格 groups 中有一个查询，并且只能使用它的字段。
你的内部查询属于 WHERE 子句，所以如果你想在查询中使用 vGroups 的字段，你应该将 groups 与 vGroups 进行连接，然后使用它的字段，例如
SELECT
sID vID,
sSSN vSSN,
CONCAT_WS(';',
IF(COUNT(DISTINCT pCheck) = 0, "员工在该月没有薪资数据", NULL),
IF(COUNT(DISTINCT sDate) > 1, "代表多个福利记录", NULL)
) comments,
sLocation vLocation,
vGroups.pCheck
FROM groups
LEFT JOIN vGroups ON groups.sID = vGroups.pID
WHERE (sSSN, sID) NOT IN (
SELECT pSSN, pID, pCheck
FROM vGroups
WHERE pID = pvID
AND pDate >= pvStartDate
)
英文:
In your query there is no outer or inner select query...
You have one query from table groups, and can use only its fields
Your inner query belongs to WHERE clause, so if you want to use vGroups fields in your query you should join groups with vGroups and then use its fields, for example
SELECT
sID vID,
sSSN vSSN,
CONCAT_WS('; ',
IF(COUNT(DISTINCT pCheck) = 0, "Employee had no paycheck data during the month", NULL),
IF(COUNT(DISTINCT sDate) > 1, "Represents multiple benefit records", NULL)
) comments,
sLocation vLocation,
vGroups.pCheck
FROM groups
LEFT JOIN vGroups ON groups.sID = vGroups.pID
WHERE (sSSN, sID) NOT IN (
SELECT pSSN, pID, pCheck
FROM vGroups
WHERE pID = pvID
AND pDate >= pvStartDate
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论