【Mysql】业务视角下，SQL 字段处理专题

DATE_FORMAT(violation_date, '%Y-%m-%d') AS formatted_date

select id,group_concat(price separator '##') from goods group by id;

COALESCE(column1,column2,column3)

CONCAT(column1,column2,column3)

REGEXP_REPLACE(answer, '<[^>]*>+', '')

-- 例如：字段值为{"lng":"111.722685","lat":"40.797585"}-- 提取方法为：json_extract(coordinate_info,'$.lng'),json_extract(coordinate_info,'$.lat')
-- 同时去掉双引号的方法：JSON_UNQUOTE(json_extract(coordinate_info,'$.lng'))

-- 拼接一个json，比如：{'id':'123','name','张三'}SeLeCt JSON_ARRAYAG(JSON_OBJECT('id',Id,'name',Name))

/** 获取未来7天日期和星期x**/select *,CONCAT(day_name,'(',week_name,')') day_week_nameFROM(select DATE_FORMAT(weekday.day1,'%Y-%m-%d') day_name,(CASE DAYOFWEEK(weekday.day1)	WHEN 1 THEN '星期日'	WHEN 2 THEN '星期一'	WHEN 3 THEN '星期二'	WHEN 4 THEN '星期三'	WHEN 5 THEN '星期四'	WHEN 6 THEN '星期五'	WHEN 7 THEN '星期六'	ELSE '未知'END) week_name FROM(select DATE_SUB(NOW(),INTERVAL -1 DAY) day1 unionselect DATE_SUB(NOW(),INTERVAL -2 DAY) day2 unionselect DATE_SUB(NOW(),INTERVAL -3 DAY) day3 UNIONselect DATE_SUB(NOW(),INTERVAL -4 DAY) day4 UNIONselect DATE_SUB(NOW(),INTERVAL -5 DAY) day5 UNIONselect DATE_SUB(NOW(),INTERVAL -6 DAY) day6 UNIONselect DATE_SUB(NOW(),INTERVAL -7 DAY) day7) weekday) table1

-- 查询xx商场（坐标为：121.75977,60.819568）周边500m内的饭店记录
SELECT l.*, ST_Distance_Sphere(l.coordinates, POINT(121.75977,60.819568)) AS distanceFROM demo_locations lHAVING distance <= 500
-- distance <= 500 代表：500米半径范围内.

SELECT ST_X(location) AS longitude, ST_Y(location) AS latitude; -- 其中，ST_X函数返回POINT类型的坐标经度，ST_Y函数返回坐标纬度