Geek Answers Handbook

MySQL - show pairs of records in one record

I have the following data in one table:

Time, Type, Kilometers 12:00, 1, 0.1 12:30, 2, 0.2 14:00, 1, 0.4 15:00, 2, 1.0 16:00, 1, 1.2 16:30, 2, 1.5 16:45, 1, 2.0

This data is sorted chronologically using the DateTime field. I would like to show these “pairs” of entries as 1 line, for example:

 StartTime, Type1Km, Type2Km 12:00, 0.1, 0.2 14:00, 0.4, 1.0 16:00, 1.2, 1.5 16:45, 2.0, NULL

There are a few caveats: if there is no Type1 to run, then show NULL in the resulting Type1Km Field tables. Similarly, if there is no end to type 2, show NULL in the "Type2Km records" field.

How can i do this?

+8
sql join mysql
source share
3 answers

Unfortunately, MySQL does not have a FULL OUTER JOIN , so you will need UNION two sets together.

This will give you cases when Type1Km exists, regardless of whether Type2Km is Type2Km .

 SELECT t1.`Time` as StartTime, t1.`Kilometers` as Type1Km, t2.`Kilometers` as Type2Km FROM `times` t1 LEFT JOIN `times` t2 ON t2.`Type` = 2 AND t2.`Time` = (SELECT `Time` FROM `times` WHERE `Time` > t1.`Time` ORDER BY `Time` LIMIT 1) WHERE t1.`Type` = 1

Now we need cases when Type1Km does not exist.

 SELECT t2.`Time` as StartTime, NULL as Type1Km, t2.`Kilometers` as Type2Km FROM `times` t2 LEFT JOIN `times` t1 ON t1.`Time` = (SELECT `Time` FROM `times` WHERE `Time` < t2.`Time` ORDER BY `Time` DESC LIMIT 1) WHERE t2.`Type` = 2 AND (t1.`Type` = 2 OR t1.`Type` IS NULL)

UNION together, and you have the desired result:

 ( SELECT t1.`Time` as StartTime, t1.`Kilometers` as Type1Km, t2.`Kilometers` as Type2Km FROM `times` t1 LEFT JOIN `times` t2 ON t2.`Type` = 2 AND t2.`Time` = (SELECT `Time` FROM `times` WHERE `Time` > t1.`Time` ORDER BY `Time` LIMIT 1) WHERE t1.`Type` = 1 ) UNION ALL ( SELECT t2.`Time` as StartTime, NULL as Type1Km, t2.`Kilometers` as Type2Km FROM `times` t2 LEFT JOIN `times` t1 ON t1.`Time` = (SELECT `Time` FROM `times` WHERE `Time` < t2.`Time` ORDER BY `Time` DESC LIMIT 1) WHERE t2.`Type` = 2 AND (t1.`Type` = 2 OR t1.`Type` IS NULL) ) ORDER BY `StartTime`

Update

In my previous query, I forgot to consider having a type 2 record at the very beginning. Updated to account for this. Here are the results I get:

The data in the times table:

 +----------+------+------------+ | Time | Type | Kilometers | +----------+------+------------+ | 11:00:00 | 2 | 0.1 | | 12:00:00 | 1 | 0.1 | | 12:30:00 | 2 | 0.2 | | 14:00:00 | 1 | 0.4 | | 14:30:00 | 1 | 0.8 | | 15:00:00 | 2 | 1.0 | | 15:30:00 | 2 | 0.2 | | 16:00:00 | 1 | 1.2 | | 16:30:00 | 2 | 1.5 | | 16:45:00 | 1 | 2.0 | +----------+------+------------+

Query Results:

 +-----------+---------+---------+ | StartTime | Type1Km | Type2Km | +-----------+---------+---------+ | 11:00:00 | NULL | 0.1 | | 12:00:00 | 0.1 | 0.2 | | 14:00:00 | 0.4 | NULL | | 14:30:00 | 0.8 | 1.0 | | 15:30:00 | NULL | 0.2 | | 16:00:00 | 1.2 | 1.5 | | 16:45:00 | 2.0 | NULL | +-----------+---------+---------+
+4
source share

If you can, change the table structure to type 1km and type2km, then either update when adding kilometers, or just summarize when choosing.

0
source share

It can work

 select max(Time), substring_index(group_concat(if(type=2,null,Kilometers) order by type),',',1), substring_index(group_concat(if(type=1,null,Kilometers) order by type desc),',',1) from your_table group by date_format(Time, '%H');

i, assuming a group by the hour, and also worthless, your results are not accurate

To better explain what this fun request is

 group_concat(if(type=2,null,Kilometers) order by type -- this is ensure the Kilometers must NOT from type=2 group_concat(if(type=1,null,Kilometers) order by type desc -- this is ensure the Kilometers must NOT from type=1
0
source share

More articles:


All Articles