mysql - 查询获取上个月数据(从现在开始计算)并解析Json以获取Json中的项目

Question

我有两个相关的表，如下所示:

主票:

t_m_id     t_open       t_closed
==================================
111      2018-12-01     2018-12-05
222      2018-12-02     2018-12-06 
333      2018-12-03     2018-12-07
444      2018-12-04     2018-12-08

主数据:

m_id     m_reference
=====================================
111    {"id": "01","name": "Bahary"}
222    {"id": "02","name": "Mail"}
333    {"id": "03","name": "Ivan"}
444    {"id": "04","name": "Scheil"}

我应该如何做，使我的表格看起来像这样(使用过滤器从 t_open 获取上个月的数据):

id   name      t_open     t_closed
===============================
01   Bahary   2018-12-01  2018-12-05
02   Mail     2018-12-02  2018-12-06
03   Ivan     2018-12-03  2018-12-07
04   Scheil   2018-12-04  2018-12-08

请帮助我......谢谢，

Answer 1

我认为这可能会给出您预期的输出。

  with cte as (
 select 111 as t_m_id,       '2018-12-01' as t_open,     '2018-12-05' as t_closed 
 union all 
 select 222 as t_m_id,       '2018-12-02' as t_open,     '2018-12-06' as t_closed 
 union all 
 select 333 as t_m_id,       '2018-12-03' as t_open,     '2018-12-07' as t_closed 
 union all 
 select 444 as t_m_id,       '2018-12-04' as t_open,     '2018-12-08' as t_closed) , 
 cte2 as (      

 select 111 as m_id,    '{"id": "01","name": "Bahary"}' as m_reference union all 
 select 222 as m_id,    '{"id": "02","name": "Mail"}' as m_reference union all 
 select 333 as m_id,    '{"id": "03","name": "Ivan"}' as m_reference union all 
 select 444 as m_id,    '{"id": "04","name": "Scheil"}' as m_reference)

  select json_unquote(json_extract(m_reference, '$.id')) as ID, c.t_open, t_closed,
  json_unquote(json_extract(m_reference, '$.name')) AS name from cte   c 
  join cte2 c2 on c.t_m_id = c2.m_id;

输出:

ID, t_open,     t_closed,   name
01, 2018-12-01, 2018-12-05, Bahary 
02, 2018-12-02, 2018-12-06, Mail
03, 2018-12-03, 2018-12-07, Ivan
04, 2018-12-04, 2018-12-08, Scheil