对于键值对数据的查询
为了方便查看,将其称之为A表
A表:
可以看到A表中的列有:id ,inquiry_id, data_key, data_value,date_key列中的各个字段对应的data_value中的各个数据
B表:
B表中的主键id和A表中的inquiry_id相同,那么现在需求是:将AB两表相同id的所有数据查询出来,需要把data_key中的各个数据作为单独的key和data_value中的数据逐个对应,刚开始我想到的是子查询,后来发现运用max函数更加方便快捷,现在供上查询语句:
SELECT
quiry.id,
quiry.org_id,
quiry.service_process,
quiry.status,
quiry.reply_status,
quiry.create_time,
max( CASE extdata.data_key WHEN 'patientName' THEN extdata.data_value ELSE '' END ) AS 'patientName',
max( CASE extdata.data_key WHEN 'staffName' THEN extdata.data_value ELSE '' END ) AS 'staffName' ,
max( CASE extdata.data_key WHEN 'patientSex' THEN extdata.data_value ELSE '' END ) AS 'patientSex' ,
max( CASE extdata.data_key WHEN 'patientAge' THEN extdata.data_value ELSE '' END ) AS 'patientAge',
max( CASE extdata.data_key WHEN 'staffAccountId' THEN extdata.data_value ELSE '' END ) AS 'staffAccountId' ,
max( CASE extdata.data_key WHEN 'staffHeader' THEN extdata.data_value ELSE '' END ) AS 'staffHeader' ,
max( CASE extdata.data_key WHEN 'staffTitle' THEN extdata.data_value ELSE '' END ) AS 'staffTitle' ,
max( CASE extdata.data_key WHEN 'staffOrgName' THEN extdata.data_value ELSE '' END ) AS 'staffOrgName'
FROM
inquiry_info AS quiry
LEFT JOIN inquiry_ext_data AS extdata ON quiry.id = extdata.inquiry_id
AND quiry.is_delete =0
GROUP BY quiry.id
最后得到结果就是这样的: