时间戳

首页 > 解决方案 > SQL 查询、JOIN 和 CASE 语句给出错误

mysql - SQL 查询、JOIN 和 CASE 语句给出错误

问题描述

在我添加 JOIN 和 WHERE 语句之前，我的查询一直有效。我需要从另一个表中加入 id，这样我就可以只过滤我想要的行。有什么建议吗？帮助表示赞赏。

错误：#1066 - 不是唯一的表/别名：'ahv9t_facileforms_subrecords'

     SELECT
record,
MAX(CASE WHEN title = 'Listing ID' then value END) AS Listing,
MAX(CASE WHEN title = 'Status' then value END) AS Status,
MAX(CASE WHEN title = 'Resort' then value END) AS Resort,
MAX(CASE WHEN title = 'Points on Deed' then value END) AS Points,
MAX(CASE WHEN title = 'Use Year' then value END) AS UseYear,
MAX(CASE WHEN title = 'Points on Deed' then value END) AS Points,
MAX(CASE WHEN title = 'Last Years Points' then value END) AS LastYearPoints,
MAX(CASE WHEN title = 'This Years Points' then value END) AS ThisYearPoints,
MAX(CASE WHEN title = 'Next Years Points' then value END) AS NextYearPoints,
MAX(CASE WHEN title = 'Points Available' then value END) AS PointsAvailable,
MAX(CASE WHEN title = 'Price Per Point' then value END) AS PricePerPoint,
MAX(CASE WHEN title = 'Price' then value END) AS Price,
MAX(CASE WHEN title = 'Closing Cost' then value END) AS ClosingCost,
MAX(CASE WHEN title = 'Annual Dues at Closing' then value END) AS AnnualDuesAtClosing,
MAX(CASE WHEN title = 'Total Cost' then value END) AS TotalCost,
MAX(CASE WHEN title = 'Direct Price Per Point' then value END) AS DirectPricePerPoint,
MAX(CASE WHEN title = 'Savings' then value END) AS Savings,
MAX(CASE WHEN title = 'Contract Expires' then value END) AS ContractExpires,
MAX(CASE WHEN title = 'Current Annual Fees' then value END) AS CurrentAnnualFees
FROM ahv9t_facileforms_subrecords
JOIN ahv9t_facileforms_subrecords ON ahv9t_facileforms_records.id = ahv9t_facileforms_subrecords.record
WHERE ahv9t_facileforms_records.form = '12'
GROUP BY record

标签： mysqlsql

解决方案

所以在我修复了 JOIN 部分之后。它给了我一个列“标题”是模棱两可的错误。所以我在“title=”前面为我的每一列/行添加了表名。那解决了它。现在我得到了我所追求的。

SELECT
record,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Listing ID' then value END) AS Listing,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Status' then value END) AS Status,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Resort' then value END) AS Resort,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Points on Deed' then value END) AS Points,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Use Year' then value END) AS UseYear,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Points on Deed' then value END) AS Points,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Last Years Points' then value END) AS LastYearPoints,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'This Years Points' then value END) AS ThisYearPoints,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Next Years Points' then value END) AS NextYearPoints,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Points Available' then value END) AS PointsAvailable,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Price Per Point' then value END) AS PricePerPoint,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Price' then value END) AS Price,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Closing Cost' then value END) AS ClosingCost,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Annual Dues at Closing' then value END) AS AnnualDuesAtClosing,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Total Cost' then value END) AS TotalCost,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Direct Price Per Point' then value END) AS DirectPricePerPoint,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Savings' then value END) AS Savings,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Contract Expires' then value END) AS ContractExpires,
MAX(CASE WHEN ahv9t_facileforms_subrecords.title = 'Current Annual Fees' then value END) AS CurrentAnnualFees
FROM ahv9t_facileforms_subrecords
JOIN ahv9t_facileforms_records ON ahv9t_facileforms_records.id = ahv9t_facileforms_subrecords.record
WHERE ahv9t_facileforms_records.form = '12'
GROUP BY record

推荐阅读