如何在MAX中使用GROUP_CONCAT(CASE WHEN ...)?
问题描述:
我想从这个查询中得到结果,但我在使用group_concat和max case时遇到了问题。如何在MAX中使用GROUP_CONCAT(CASE WHEN ...)?
SELECT
t1.id,
t1.name,
MAX(CASE WHEN t2.code = t1.f_name THEN t2.name END) AS name2,
MAX(CASE WHEN t2.code = t1.l_name THEN t2.name END) AS name3,
MAX(CASE WHEN t2.code = t1.a_name THEN t2.name END) AS name4,
GROUP_CONCAT(MAX(CASE WHEN FIND_IN_SET(t3.code,t1.vehicles) THEN t3.name END) AS car_owned)
GROUP_CONCAT(MAX(CASE WHEN FIND_IN_SET(t3.code,t1.homes) THEN t3.name END) AS home_owned)
FROM
first_table AS t1 LEFT JOIN second_table AS t2
ON t2.code IN (t1.f_name, t1.l_name, t1.a_name)
LEFT JOIN second_table AS t3 ON FIND_IN_SET(t3.code,t1.vehicles) > 0 OR FIND_IN_SET(t3.code,t1.homes) > 0
GROUP BY
t1.id,
t1.name
任何帮助谢谢。小提琴是here
答
试试这个
SELECT
t1.id,
t1.name,
CASE WHEN t2.code = t1.f_name THEN t2.name END AS name2,
CASE WHEN t2.code = t1.l_name THEN t2.name END AS name3,
CASE WHEN t2.code = t1.a_name THEN t2.name END AS name4,
CASE WHEN FIND_IN_SET(t3.code,t1.vehicles) > 0 THEN GROUP_CONCAT(DISTINCT(t3.name END)) AS car_owned)
CASE WHEN FIND_IN_SET(t3.code,t1.homes) > 0 THEN GROUP_CONCAT(DISTINCT(t3.name)) END AS home_owned)
FROM
first_table AS t1 LEFT JOIN second_table AS t2
ON t2.code IN (t1.f_name, t1.l_name, t1.a_name)
LEFT JOIN second_table AS t3 ON FIND_IN_SET(t3.code,t1.vehicles) > 0 OR FIND_IN_SET(t3.code,t1.homes) > 0
GROUP BY
t1.id,
t1.name
小提琴是here
不知道我在下面。你能分享一些样本数据和你试图得到的结果吗? – Mureinik 2014-11-24 07:38:53
我不知道你在做什么 – Strawberry 2014-11-24 07:39:08
@Strawberry我在first_table列的home中选择连接列时出现问题,而车辆已经连接了来自第二个表的连接值,现在我想从second_table获取相关名称。 – jones 2014-11-24 08:22:57