数据库环境：mysql5.6

需求描述(图1)：

最终期望的结果(图2)：

如上图所示，现有两张表cj_lottery_winning_record和cj_lottery_winning_user_info

两者的关系是一对多。

现在进行连接查询时，

SELECT

a.id winning_id,

a.awards_setting_id,

a.award_name,

a.award_type,

a.create_time,

a.awards_name,

a.get_status,

a.push_status,

b.key,

b.value honoree_info

FROM

cj_lottery_winning_record a

LEFT JOIN cj_lottery_record_user_info b

ON a.id = b.winning_id

WHERE activity_id=’100270’

ORDER BY a.create_time DESC LIMIT 0,10;

会得到图1所示的结果。

如果想得到图2所示的结果，只需将sql语句改成下面的即可：

SELECT

a.id winning_id,

a.awards_setting_id,

a.award_name,

a.award_type,

a.create_time,

a.awards_name,

a.get_status,

a.push_status,

b.info

FROM

cj_lottery_winning_record a

LEFT JOIN

(SELECT

winning_id,

GROUP_CONCAT(

IF(

key = ‘微信’,

CONCAT(‘微信:’, value, ‘,’),

IF(

key = ‘手机’,

CONCAT(‘手机:’, value, ‘,’),

IF(

key = ‘地址’,

CONCAT(‘地址:’, value, ‘,’),

”

)

)

)

) AS info

FROM

cj_lottery_record_user_info

GROUP BY winning_id) b

ON a.id = b.winning_id

WHERE a.activity_id = ‘100270’

ORDER BY a.create_time DESC

LIMIT 0, 10 ;