Karpol Karpol - 14 days ago 6
MySQL Question

Left join select last and first row and Left join select some row as column

First of all I show my current query.

SELECT u.id, u.username, GROUP_CONCAT(DISTINCT up.weight) as weights, GROUP_CONCAT(DISTINCT pd.type_change, ':', pd.sum_type_change) as statistics
FROM user u
LEFT JOIN
(SELECT up.weight, up.user_id FROM user_parameter up WHERE up.date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00' ORDER BY up.user_id, up.date ASC) up
ON u.id = up.user_id
LEFT JOIN
(SELECT pd.type_change, count(pd.id) as sum_type_change, dd.user_id FROM diet_day dd
LEFT JOIN product_day pd ON dd.id = pd.diet_day_id
WHERE dd.date_start BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00' GROUP BY dd.user_id, pd.type_change) pd
ON u.id = pd.user_id
GROUP BY u.id


This works fine but I have to substract first and last up.weight instead of

GROUP_CONCAT(DISTINCT up.weight)


In addition to this I have to change

GROUP_CONCAT(DISTINCT pd.type_change, ':', pd.sum_type_change)


This always return string like this [1: count, 2: count, 3: count, 4: count]
Instead of one column (group_concat) I need to have four new columns.

It is possible to do all of this in mysql query?

Answer

You'll need separate subqueries to get the first and last up.weight.

SELECT u.id, u.username, lastup.weight - firstup.weight as delta_weight, 
       GROUP_CONCAT(DISTINCT pd.type_change, ':', pd.sum_type_change) as statistics
FROM user u
LEFT JOIN
    (SELECT up.weight, up.user_id 
     FROM user_parameter up
     JOIN (SELECT user_id, MAX(up.date) AS maxdate
           FROM user_parameter
           WHERE date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00'
           GROUP BY user_id) AS maxup
     ON up.user_id = maxup.user_id AND up.date = maxup.maxdate
     WHERE up.date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00') lastup
    ON u.id = lastup.user_id
LEFT JOIN
    (SELECT up.weight, up.user_id 
     FROM user_parameter up
     JOIN (SELECT user_id, MIN(up.date) AS mindate
           FROM user_parameter
           WHERE date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00'
           GROUP BY user_id) AS minup
     ON up.user_id = maxup.user_id AND up.date = minup.mindate
     WHERE up.date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00') firstup
    ON u.id = firstup.user_id
LEFT JOIN
    (SELECT pd.type_change, count(pd.id) as sum_type_change, dd.user_id 
     FROM diet_day dd
     LEFT JOIN product_day pd ON dd.id = pd.diet_day_id
     WHERE dd.date_start BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00' GROUP BY dd.user_id, pd.type_change) pd
    ON u.id = pd.user_id
GROUP BY u.id

Regarding getting the statistics in separate columns instead of concatenating them with GROUP_CONCAT, see MySQL pivot table

Comments