MySQL根据指定字段值判断,给字段拼接指定字符

如上,主要用到两个SQL函数

1、字符串拼接函数:

CONCAT(str1,str2,...)

2、CASE WHEN判断函数:

CASE sex
          WHEN '1' THEN '男'
          WHEN '2' THEN '女'
ELSE '其他' END

3、直接上代码

SELECT
    m.id AS '刊登记录id',
    n.id AS '变种记录id',
    m.item_id AS 'listing消息',
    b.shop_name AS '站点',
    n.product_sku AS '公司sku',
    n.sku AS '店铺sku',
    m.online_status AS 'listing上架状态',
    (
        CASE
        WHEN m.site_code = 'MY' THEN
            CONCAT(
                'https://www.lazada.com.my/i',
                m.item_id,
                '.html'
            )
        WHEN m.site_code = 'TH' THEN
            CONCAT(
                'https://www.lazada.co.th/i',
                m.item_id,
                '.html'
            )
        WHEN m.site_code = 'SG' THEN
            CONCAT(
                'https://www.lazada.sg/i',
                m.item_id,
                '.html'
            )
        WHEN m.site_code = 'VN' THEN
            CONCAT(
                'https://www.lazada.vn/i',
                m.item_id,
                '.html'
            )
        WHEN m.site_code = 'PH' THEN
            CONCAT(
                'https://www.lazada.com.ph/i',
                m.item_id,
                '.html'
            )
        WHEN m.site_code = 'ID' THEN
            CONCAT(
                'https://www.lazada.co.id/i',
                m.item_id,
                '.html'
            )
        ELSE
            '未知'
        END
    ) AS 'listing链接'
FROM
    lazada_online_listing AS m
LEFT JOIN lazada_online_listing_variant AS n ON n.listing_online_id = m.id
LEFT JOIN lazada_account AS b ON b.id = m.shop_id
WHERE
    n.product_sku = ''
AND m.online_status = 'live'
LIMIT 20;

4、最后结果如下:

MySQL根据指定字段值判断,给字段拼接指定字符

Original: https://www.cnblogs.com/camg/p/15924443.html
Author: 在斑马线上散布
Title: MySQL根据指定字段值判断,给字段拼接指定字符

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/684555/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球