MySQL LEFT JOIN 默认值,数据过滤,排序的处理

MySQL LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据,RIGHT JOIN 和 LEFT JOIN 方向相反,其他完全一样,主要理解 LEFT JOIN,RIGHT JOIN 也是一样的。

举个栗子,我们有个商品分销系统,首先系统全局设置了一个默认的分销比例,为了方便,这里就为10%,然后每个商品还可以单独设置分销比率。

我们直接将商品信息存储在 WordPress 默认的 posts 表里面,当该商品的分销比率不同于系统默认值的时候,我们会在 postmeta 表插入一条记录, meta_key 为 commission,meta_value 为该产品独立设置的分销比率。

因为只有部分商品设置了分销比率,我们可以通过 LEFT JOIN 获取商品信息和它的分销比率。

SELECT wp_posts.*, wp_postmeta.meta_value as commission
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish' AND wp_postmeta.meta_key = 'commission'
LIMIT 0, 10

什么时候过滤数据

上面的 SQL 语句返回空,这里就出现了第一个问题,首先要明确一下是数据过滤是在 JOIN 之前过滤还是 JOIN 之后过滤的。

  • JOIN 之前过滤的,放到 on 子句中。
  • JOIN 之后过滤的,放到 where 子句中。

我们这里是在 JOIN 之前要先过滤单独设置 commisson 的 meta 数据,所以 wp_postmeta.meta_key = 'commission' 要放到 ON 子句中:

SELECT wp_posts.*, wp_postmeta.meta_value as commission
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'commission'
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
LIMIT 0, 10

用默认值取代 null

上面 SQL 可以返回商品信息和他分销比率,但是没有单独设置的分销比率的商品返回结果是 null,能否使用默认的分销比率代替 null 值呢?

SELECT wp_posts.*, COALESCE(wp_postmeta.meta_value, 10) as commission
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'commission'
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
LIMIT 0, 10

COALESCE(value,…) 是一个可变参的 SQL 函数,它可以接受多个参数,返回第一个不为 NULL 的参数,如果所有参数都为 NULL,此函数返回 NULL,当它使用 2 个参数时,和 IFNULL 函数作用相同。

排序

使用了默认值之后,我们就可以使用 commission 进行排序了:

SELECT wp_posts.*, COALESCE(wp_postmeta.meta_value, 10) as commission
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'commission'
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
ORDER BY commission+0 DESC, wp_posts.post_date DESC
LIMIT 0, 10

首先使用 commission 进行排序,相同的商品,则使用发布时间进行排序。


©我爱水煮鱼,本站推荐使用的主机:阿里云,国外主机建议使用BlueHost

本站长期承接 WordPress 优化建站业务,请联系微信:「chenduopapa」。