时间戳

首页 > 解决方案 > MySQL1054 - “字段列表”中的未知列

mysql - MySQL1054 - “字段列表”中的未知列

问题描述

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'

我想在第二组 concat 中显示 myview 数据..

标签： mysqlgroup-concat

解决方案

您可以使用子查询，因为此时列别名对于选择的不同列不可见。我假设您需要第二级聚合并且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

推荐阅读