mysql-ignore如果不存在值

whitzsjs  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(298)

我有一个用于wordpress的mysql查询,可以获取高级自定义字段数据。如果其中一行没有结果,则整个查询不提供结果。我想知道我是否可以添加一些东西到查询中,如果没有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'

例如;meta\u key='ex\u diameter'可能根本不存在,但整个结果不是使用空ex\u diameter获取结果,而是空的,不返回任何行。

zbsbpyhn

zbsbpyhn1#

而不是。。。

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中的所有列都将为空(这就是为什么我们需要把条件从where子句移到on子句。。。where子句中的条件不能由null满足。。。
对任何可以接受行“丢失”的表重复相同的模式。
实际上,使用 LEFT JOIN ,你首先需要驾驶台。可能想要 wp_posts wpp 表,并在外部联接条件中引用wpp.id。我真的不认为有必要 wp0 参考资料,您可以从wpp.id获取post\u 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 condition ,而不是在不修改条件的情况下,通过添加或条件。。。

AND ( condition OR some_other_condition )

mysql有时在有或有条件的情况下(在性能方面)做得不太好。

相关问题