统计相关

 

想要筛选 json字段里面数据

SELECT
  R.id as '序号ID',
	R.user_id,
	JSON_EXTRACT( M.attachment, '$.coins' ) as '赠送金币',
	JSON_EXTRACT( M.attachment, '$.points' ) as '赠送彩票',
	M.title as '标题',
	M.created_at as '赠送时间',
	R.updated_at as '领取时间'
	
FROM
	sh_message_rel AS R
	LEFT JOIN sh_message AS M ON R.message_id = M.id 
WHERE
	R.store_id = 10004 
	AND scene = 31 
-- 	AND JSON_EXTRACT( M.attachment, '$.coins' ) + JSON_EXTRACT( M.attachment, '$.points' ) >= 1000 
-- 	AND JSON_EXTRACT( M.attachment, '$.coins' ) + JSON_EXTRACT( M.attachment, '$.points' ) <= 10000