一个查询中有多个查询?

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.

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;    

You only need one query where you get all the information in the right order. So first so order by agent and then by day.

As you cannot order the days alphabetically, you need for example a CASE statement to get the right order:

SELECT
    *
FROM `your_table`
ORDER BY
    `agent`,
    CASE
        WHEN `Day` = 'Mon' THEN 1
        WHEN `Day` = 'Tue' THEN 2
        ...
        WHEN `Day` = 'Sun' THEN 7
    END

Storing the day number instead of the text would make this a bit more efficient of course.

Then when you get all your rows, you keep track of the current agent and as soon as the agent changes, you add a new row.

Consider the solution using group_concat function which will get all days for a certain agent with all needed respective time ranges:

SELECT Agent, group_concat(Day,' ', Start, '-', End separator '/') AS dayData 
FROM `schedules`
GROUP BY Agent

This will get resultset which will look like as follows:

Agent  | dayData    
-----------------------------    
agent1 | Mon 17:00-23:00/Tue 16:00-23:00/Wed 18:00-23:00
agent2 | .... ..

When the resultset is fetched on PHP side, you can easily process each row with explode('/', $row['dayData']) (getting an array of days with time ranges). Then, explode each item with explode(" ", $day)... and so on. After that, you can create a "beautiful" output using html <table>