一个查询中有多个查询？

Question

I have a table in a database with start and stop times for each day of the week for some staff in Mysql in the following way:

Agent     Day     Start     End
agent1    Mon     17:00     23:00
agent1    Tue     16:00     23:00
agent1    Wed     18:00     23:00

I need this to the displayed in the following way:

Agent       Mon              Tue           Wed
        start   stop    start   stop  start   stop
agent1  17:00   23:00   16:00   23:00 18:00   23:00

Is there a way to do this through a query? I was thinking of doing multiple queries in one query but I don't quite get it.

Answer 1

You can sue a lot of case

select agent,
      case day when 'Mon' then start end as start_mon,
      case day when 'Mon' then `end` end as end_mon, 
      case day when 'Tue' then start end as start_tue,
      case day when 'Tue' then `end` end as end_the,    
      case day when 'Wed' then start end as start_wed,
      case day when 'Wed' then `end` end as end_wed
 from your_table      

for gettin the value on the same row of agent you can use a temp table and group bt

select agent, max(start_mon), max(end_mon), max(start_tue), max(end_tue), max(start_wed), max(end_web)
   from ( 
        select agent,
           case day when 'Mon' then start end as start_mon,
           case day when 'Mon' then `end` end as end_mon, 
           case day when 'Tue' then start end as start_tue,
           case day when 'Tue' then `end` end as end_the,    
           case day when 'Wed' then start end as start_wed,
           case day when 'Wed' then `end` end as end_wed
        from your_table ) as t
    group by agent;