此查询返回错误代码1054"字段列表"中的未知列"Nb_p"。
SELECT Lot.LotNbPieces AS Nb_P,
LotValeurFonciere AS VF,
LotSurfCarrez AS M2
FROM data_immo.Lot
WHERE (Lot.LotType='Appartement' AND Lot.LotNbPieces=2 OR Lot.LotNbPieces=3)
GROUP BY Nb_P, VF, M2;
Nb_p(代表房间/公寓的数量(被称为"Lot.LotNbPieces"。所以不要理解这个错误。你能给我一些意见吗?
此外,我需要使用这些别名来计算平均值。因此,上面的查询将以这种方式修改,
SELECT Lot.LotNbPieces AS Nb_P,
LotValeurFonciere AS VF,
LotSurfCarrez AS M2,
avg(CASE WHEN Nb_P=2 THEN VF END) AS Moy_VF_2P,
avg(CASE WHEN Nb_P=3 THEN VF END) AS Moy_VF_3P,
avg(CASE WHEN Nb_P=2 THEN M2 END) AS Moy_M2_2P,
avg(CASE WHEN Nb_P=3 THEN M2 END) AS Moy_M2_3P
FROM data_immo.Lot
WHERE (Lot.LotType='Appartement'
AND Lot.LotNbPieces=2 OR Lot.LotNbPieces=3)
GROUP BY Nb_P, VF, M²;
我们可以在SELECT子句中添加这些计算语句吗?这是正确的语法吗?
非常感谢您的反馈,
在第一个查询中,您没有使用聚合函数。。所以你不应该使用group-by-。。在这些情况下使用DISTINCT
你也在以错误的方式使用条件
SELECT distinct Lot.LotNbPieces AS Nb_P, LotValeurFonciere AS VF, LotSurfCarrez AS M2
FROM data_immo.Lot
WHERE Lot.LotType='Appartement' AND (Lot.LotNbPieces=2 OR Lot.LotNbPieces=3)
对于第二个查询不能在select子句中使用列名别名,因为在解析select子句后,数据库引擎会获取对别名的引用,因此在语句时必须重复该代码
SELECT Lot.LotNbPieces AS Nb_P
, LotValeurFonciere AS VF
, LotSurfCarrez AS M2
, avg(CASE WHEN Lot.LotNbPieces=2 THEN LotValeurFonciere END) AS Moy_VF_2P
, avg(CASE WHEN Lot.LotNbPieces=3 THEN LotValeurFonciere END) AS Moy_VF_3P
, avg(CASE WHEN Lot.LotNbPieces=2 THEN LotSurfCarrez END) AS Moy_M2_2P
, avg(CASE WHEN Lot.LotNbPieces=3 THEN LotSurfCarrez END) AS Moy_M2_3P
FROM data_immo.Lot WHERE Lot.LotType='Appartement'
AND (Lot.LotNbPieces=2 OR Lot.LotNbPieces=3)
GROUP BY b_P, VF, M2
如果别名在组中不起作用,那么你只需重复代码就可以解决问题
GROUP BY Lot.LotNbPieces
, LotValeurFonciere
, LotSurfCarrez