mysql未知列(union)

dfuffjeb 于 2021-06-21 发布在 Mysql

关注(0)|答案(4)|浏览(402)

我有以下疑问：

select a.clei, a.partNumber, 
(
  SELECT count(*) FROM 
    ( SELECT * from search_upload_data s where a.clei is not null AND a.clei = s.clei 
      UNION
      SELECT * from search_upload_data s where a.partNumber is not null AND a.partNumber = s.partNumber 
    ) as t1
) as total
from api_analytics_data a

它抛出错误：

Error Code: 1054. Unknown column 'a.clei' in 'where clause'

我猜mysql不允许在内部sql中使用连接引用，但我不知道如何解决这个问题。
我需要搜索上传数据的计数，请参见此处的sql fiddle：http://sqlfiddle.com/#!9/172ac/2号
<=========================================================================>
附加说明
我得想办法和工会合作。每个人的答案中的or条件在小范围内起作用，但在实际的数据库中却陷入了大量数据的困境。
以下是解释（对于拉贾特的回答）：

sql mysql union Join count

来源：https://stackoverflow.com/questions/50383957/mysql-unknown-column-in-where-clause-union

4条答案

按热度按时间

edqdpe6u1#

试试这个

select a.clei, a.partNumber, 
 ( SELECT COUNT(1) As Total
       from search_upload_data s
       where ( a.clei is not null  AND a.clei = s.clei )
       OR (a.partNumber is not null AND a.partNumber = s.PartNumber)

    ) as t1 

from api_analytics_data a

赞(0）回复(0）举报 2021-06-21

i2byvkas2#

select a.clei, a.partNumber, 
(
  SELECT count(s.id) FROM search_upload_data s where (a.clei is not null AND a.clei = s.clei) OR (a.partNumber is not null AND a.partNumber = s.partNumber) 
) as Total
from api_analytics_data a

现在试试

赞(0）回复(0）举报 2021-06-21

epggiuax3#

试试这个：简单的加入 OR 以及 GROUP BY ```
SELECT a.clei,
a.partNumber,
COUNT(1) tot
FROM api_analytics_data a
LEFT JOIN search_upload_data s ON (a.clei = s.clei OR a.partNumber = s.partNumber)
AND (a.clei is not null OR a.partNumber is not null)
GROUP BY a.clei, a.partNumber

output:http：//sqlfiddle.com/#！949年5月56日

赞(0）回复(0）举报 2021-06-21

i1icjdpr4#

无论如何，这与您所期望的输出是一致的。只需使用一个常规的或来选择克莱或零件号。

select a.clei, a.partNumber, 
(
  SELECT count(*) FROM search_upload_data s WHERE 
     (a.clei is not null AND a.clei = s.clei) OR 
     (a.partNumber is not null AND a.partNumber = s.partNumber) 
) as Total
from api_analytics_data a

赞(0）回复(0）举报 2021-06-21

我来回答

mysql未知列(union)

4条答案

相关问题

热门标签

最新问答