时间戳

首页 > 解决方案 > 选择语句中无法识别的列

sql - 选择语句中无法识别的列

问题描述

我在此代码的 Select 语句中遇到错误。

SELECT Track.Name, Track.UnitPrice, 
    Count(*) AS Purchase_count, 
    Purchase_count * Track.UnitPrice AS Total_per_track
FROM Track
JOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackId
GROUP BY Track.Name
ORDER BY  Total_per_track desc
LIMIT 10;

返回的错误是

Result: no such column: Purchase_count
At line 1:
SELECT Track.Name, Track.UnitPrice, 
    Count(*) AS Purchase_count, 
    Purchase_count * Track.UnitPrice AS Total_per_track
    FROM Track
    JOIN InvoiceLine
    ON Track.TrackId = InvoiceLine.TrackId
    GROUP BY Track.Name
    ORDER BY  Total_per_track desc
LIMIT 10;

标签： sqlgroup-bycolumn-alias

解决方案

您不能SELECT在同一子句中引用该子句中定义的别名（WHERE例如，也不能在子句中引用）。您需要重复原始表达式，或使用派生表（子查询或 cte）。

这里的表达式很简单，所以重复似乎更相关：

SELECT 
    t.Name, 
    t.UnitPrice, 
    COUNT(*) AS Purchase_count, 
    COUNT(*) * t.UnitPrice AS Total_per_track
FROM Track t
INNER JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY t.TrackId, t.Name, t.UnitPrice
ORDER BY Total_per_track desc
LIMIT 10;

笔记：

  • 我在子句中添加了TrackIdand ；是否可以避免错误地将两条相同的轨道组合在一起；出现在子句中并且不是聚合函数的一部分，因此将它也包含在子句中是一个好习惯（尽管它似乎在功能上依赖于）UnitPriceGROUP BYTrackIdNameUnitPriceSELECTGROUP BYTrackId

  • 表别名使查询的读写更短

推荐阅读