MySQL - 如果不存在值则忽略 AND

Question

我有一个用于 WordPress 的 MySQL 查询，用于获取高级自定义字段数据。如果 AND 行之一没有结果，则整个查询不会提供结果。我想知道是否可以在查询中添加一些内容，如果 AND 行没有结果，则忽略 AND 行？

SELECT DISTINCT post_title,
wp0.post_id as the_post_id, 
wp1.meta_value as _sale_price,
wp2.meta_value as _regular_price,
wp3.meta_value as ex_diameter,
wp4.meta_value as ex_center,
wp5.meta_value as ex_length,
wp6.meta_value as ex_alloy,
wp7.meta_value as ex_butt_1,
wp8.meta_value as ex_butt_2,
wp9.meta_value as ex_taper_1,
wp10.meta_value as ex_taper_2,
wp11.meta_value as ex_center


from wp_postmeta as wp0
INNER JOIN wp_postmeta as wp1
ON wp1.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp2
ON wp2.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp3
ON wp3.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp4
ON wp4.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp5
ON wp5.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp6
ON wp6.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp7
ON wp7.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp8
ON wp8.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp9
ON wp9.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp10
ON wp10.post_id=wp0.post_id
INNER JOIN wp_postmeta as wp11
ON wp11.post_id=wp0.post_id



INNER JOIN wp_posts as wpp
ON wp1.post_id=wpp.ID
WHERE wp1.post_id='39' 
AND wp1.meta_key='_sale_price'
AND wp2.meta_key='_regular_price'
AND wp3.meta_key='ex_diameter'
AND wp4.meta_key='ex_center'
AND wp5.meta_key='ex_length'
AND wp6.meta_key='ex_alloy'
AND wp7.meta_key='ex_butt_1'
AND wp8.meta_key='ex_butt_2'
AND wp9.meta_key='ex_taper_1'
AND wp10.meta_key='ex_taper_2'
AND wp11.meta_key='ex_center'

例如； wp3.meta_key='ex_diameter' 可能根本不存在，但不是得到带有空白 ex_diameter 的结果，而是整个结果为空并且不返回任何行。

Answer 1

而不是...

 INNER JOIN wp_postmeta as wp11
 ON wp11.post_id=wp0.post_id

 WHERE ...
 AND wp11.meta_key='ex_center'

您可以使用“外连接”操作而不是“内连接”，将条件从 WHERE 子句移至连接的 ON 子句，然后删除任何需要 wp11 中任何列中的非 NULL 值的条件...

 LEFT JOIN wp_postmeta as wp11
 ON wp11.post_id=wp0.post_id
 AND wp11.meta_key='ex_center'

 WHERE ...

使用外连接，如果在 wp11 中没有找到匹配的行，则 wp11 中的所有列都将为 NULL。 (这就是为什么我们需要将条件从WHERE子句移至ON子句...WHERE子句中的条件不能由NULL满足...

对任何可以接受“缺失”行的表重复相同的模式。

<小时/>

实际上，使用LEFT JOIN，您首先需要“驱动”表。可能希望在 FROM 子句中首先使用 wp_posts wpp 表，并在外连接条件中引用 wpp.id。我真的不认为需要 wp0 引用，您可以从 wpp.id 获取 post_id

SELECT
  FROM wp_posts wpp
  LEFT JOIN wp_postmeta wp1 ON wp1.post_id=wpp.id AND wp1.meta_key='_sale_price'
  LEFT JOIN wp_postmeta wp2 ON wp2.post_id=wpp.id AND wp2.meta_key='...'
  LEFT JOIN wp_postmeta wp3 ON wp3.post_id=wpp.id AND wp3.meta_key='...'
  ...
  LEFT JOIN wp_postmeta wp11 ON wp11.post_id=wpp.id AND wp11.meta_key='ex_center'
 WHERE wpp.id = 39

我有一种感觉，您刚刚开始体验与 EAV 合作的乐趣。让乐趣开始吧!

<小时/>

回答您提出的问题...不，没有办法绕过 AND 条件，除非修改条件，通过添加 OR 条件...

AND ( condition OR some_other_condition )

MySQL 有时在 OR 条件方面表现不佳(就性能而言)。