mysql - 使用 CONCAT() 将列值合并到列中

Question

我想使用 IF() 函数将某些列值转换为列和 CONCAT() 函数，但我无法以某种方式得到它。在我运行这个sql之后:

SELECT 

IF( meta_key =  'property_id',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_id,
IF( meta_key =  'property_contract',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_contract,
IF( meta_key =  'property_agents',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_agents


FROM wp_postmeta

WHERE property_id !='faslse'
and property_agents = 'John Doe'

我收到此错误:1054 - “where 子句”中的未知列“property_id”

编辑:

我正在尝试此查询，但返回 0 行...

select *

from
(  select
  IF( meta_key =  'property_id',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_id,
  IF( meta_key =  'property_contract',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_contract,
  IF( meta_key =  'property_agents',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_agents

from
  wp_postmeta) p


WHERE 
 p.property_id != 'false'
  and p.property_contract != 'false'
  and p.property_agents!= 'false'

Answer 1

别名应用在where子句之后，所以你有两个选择，重复IF子句:

select
  IF( meta_key =  'property_id',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_id,
  IF( meta_key =  'property_contract',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_contract,
  IF( meta_key =  'property_agents',  CONCAT(meta_key, ' ', meta_value),  'false' ) AS property_agents

from
  wp_postmeta
where
  IF( meta_key =  'property_id',  CONCAT(meta_key, ' ', meta_value),  'false' ) !='faslse'
  and IF( meta_key =  'property_agents',  CONCAT(meta_key, ' ', meta_value),  'false' ) = 'John Doe'

或使用子查询:

select *
from
  (
     select
       IF(...) as property_id,
       IF(...) as property_agents
     from
       wp_postmeta
  ) s
where
  s.property_id != 'false'
  and s.property_agents = 'John Doe'

可以通过多种方式优化查询，但这取决于您的要求。

编辑

从问题上看不清楚，但我认为你要解决的问题有点不同。我假设您的 wp_postmeta 表中有一个 post_id 字段，并且我假设每个帖子的属性位于不同的行中。通过以下查询，您可以获得单个帖子的 ID、契约(Contract)和代理:

select
  post_id,
  max(case when meta_key = 'property_id' then CONCAT(meta_key, ' ', meta_value) end) as property_id,
  max(case when meta_key = 'property_contract' then CONCAT(meta_key, ' ', meta_value) end) as property_contract,
  max(case when meta_key = 'property_id' then CONCAT(meta_key, ' ', meta_value) end) as property_agents
from
  wp_postmeta
group by
  post_id

然后你可以添加过滤器:

having
  property_agents='property_agents John Doe'