MySQL Consult several data of the same field

Navigation
1

Hello I hope to explain my problem well, first apologies if my question is not understood, from a table log of events I need to obtain data of those events, I have the fields DATE, ID, DATA the field DATE is of DATETIME type, the field ID represents the event name, the DATA field represents an integer value.

As you can see in the image there is a table with three columns, it is required to extract several data from the ID field, something similar to the following image.

For this I was thinking about using subqueries, but I'm not having much luck. 

Select (select FECHA, DATO from MI_TABLA where ID=90), (select FECHA, DATO from MI_TABLA where ID=100), (select FECHA, DATO from MI_TABLA where ID=150) where FECHA between '14/11/2017' and '27/11/2017';

I appreciate your kind attention.

    
asked by root2 21.11.2017 в 00:00
source

3 answers

1

What you are looking for is a type of PIVOT. You can simulate this with conditional aggregations: 

select fecha,
       max(case when id = '3678' then dato end) as id_3678,
       max(case when id = '3676' then dato end) as id_3676,
       max(case when id = '3686' then dato end) as id_3686
  from tbl
 group by fecha
 order by fecha

Of course, for this to work, it is assumed that you already know in advance what the possible values are for the ID column.

    
answered by 21.11.2017 в 14:49
0

Would it be something like this? 

    SELECT FECHA, max(case when ID=3678 then DATO end) as "3678", max(case when ID=3676 then DATO end) as "3676", max(case when ID=3686 then DATO end) as "3686" from MI_TABLA group by FECHA ASC
    
answered by 21.11.2017 в 16:14
-1

If I understand correctly, your sentence should be like this: 

select fecha, dato from MI_TABLA where id in(90,100,150) and fecha between '14/11/2017' and '27/11/2017'
    
answered by 21.11.2017 в 00:38
How can I solve the error (25,13) of android studio 3.0? Pause non-visible videos