MySQL嵌套select语句问题
问题描述:
我努力让我的嵌套SQL语句正常工作。MySQL嵌套select语句问题
它告诉我在第二个SELECT附近有一个语法错误。
任何人都可以建议吗?
SELECT date, Expense_Date, cost1
RIGHT JOIN t ON dates.date = t.Expense_Date
FROM (
SELECT Expense_Date, IFNULL(Sum(Total_Cost),0) as cost1
FROM Expenses
RIGHT JOIN membership_userrecords ON Expenses.id = membership_userrecords.pkValue
where membership_userrecords.memberID = 'kieran' AND membership_userrecords.tableName='Expenses'
GROUP BY Expense_Date
) AS t
GROUP BY date ;
答
试试这个
SELECT date, Expense_Date, cost1
FROM (
SELECT Expense_Date, IFNULL(Sum(Total_Cost),0) as cost1
FROM Expenses
RIGHT JOIN membership_userrecords ON Expenses.id = membership_userrecords.pkValue
where membership_userrecords.memberID = 'kieran' AND membership_userrecords.tableName='Expenses'
GROUP BY Expense_Date
) AS t
LEFT JOIN t ON dates.date = t.Expense_Date
GROUP BY date ;
答
忘记第二SELECT
...你是在缺少FROM
条款你的第一个SELECT
本身所看到如下
SELECT date, Expense_Date, cost1 <-- here
RIGHT JOIN t ON dates.date = t.Expense_Date
像@rahul说你失踪FROM子句 –
谢谢!!!你在这里帮了我很多。此查询的目标是显示日期表中的所有日期以及仅显示用户被允许查看的费用表中的费用。无论哪里都没有开销,我希望仍然输出日期,只显示0.但是,这并没有发生 - 我只是根据用户权限获取有限的记录集。有什么我做错了吗? – Kieran
@Kieran处理嵌套查询时,首先验证您的内部查询为您提供了正确的数据。联接也应该是这样的: 'SELECT日期,EXPENSE_DATE,COST1 FROM(SELECT EXPENSE_DATE,IFNULL(萨姆(TOTAL_COST),0)作为COST1 FROM费用 RIGHT JOIN membership_userrecords ON Expenses.id = membership_userrecords.pkValue 其中membership_userrecords.memberID = '基兰' AND membership_userrecords.tableName = '费用' GROUP BY EXPENSE_DATE )为T RIGHT JOIN日期ON dates.date = t.Expense_Date GROUP BY日期;' –