如上,主要用到两个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、最后结果如下:
Original: https://www.cnblogs.com/camg/p/15924443.html
Author: 在斑马线上散布
Title: MySQL根据指定字段值判断,给字段拼接指定字符
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/684555/
转载文章受原作者版权保护。转载请注明原作者出处!