MySQL1054 - 'Field List' 中的未知列

Question

SELECT 
  id, 
  Prospect_Id, 
  GROUP_CONCAT(Current_Status ORDER BY id) AS myview, 
  GROUP_CONCAT(myview) AS myview1 
FROM prospectdata1 
GROUP BY Prospect_Id 
HAVING myview REGEXP 'PHONE_FOLLOW_UP.*DUPLICATE'

我想在第二组中显示我的 View 数据..

Answer 1

您可以使用子查询，因为此时列别名对于选择的不同列不可见。我假设您需要第二级聚合，并且 Prospect_Id 唯一标识一行，因为您没有按 id 进行分组。

使用子查询:

SELECT
  id, Prospect_Id, myview, GROUP_CONCAT(myview) AS myview1
FROM (
  SELECT 
    id, Prospect_Id, GROUP_CONCAT(Current_Status ORDER BY id) AS myview,
  FROM prospectdata1 
  GROUP BY Prospect_Id 
  HAVING myview REGEXP 'PHONE_FOLLOW_UP.*DUPLICATE'
) t
GROUP BY Prospect_Id