mysql - 在不同列中显示联合结果 - mysql

Question

查询

select 
   min(temperature) as minTemp, 
   max(temperature) as maxTemp from test 
union  
( 
   select 
      temperature as Temp , 
      datetime as Date 
   from test 
      order by datetime desc 
   limit 1
) 

返回结果:

+---------+---------------------+
| minTemp | maxTemp             |
+---------+---------------------+
|   24.11 | 26.739999771118164  |
|   25.93 | 2017-05-14 15:11:09 |
+---------+---------------------+

如何在单独的列中显示结果，例如:

+---------+---------------------+----------+----------------------+
| minTemp | maxTemp             |   Temp   |Date                  |
+---------+---------------------+----------+----------------------+
|   24.11 | 26.739999771118164  | 25.93    | 2017-05-14 15:11:09  |
|         |                     |          |                      |
+---------+---------------------+----------+----------------------+

Answer 1

我认为在这种情况下CROSS JOIN是你的 friend 。你可以这样做:

select 
   min(temperature) as minTemp, 
   max(temperature) as maxTemp,
   t.Temp,
   t.Date
from 
    test 
    CROSS JOIN
    (
        select 
          t1.temperature as Temp , 
          t1.datetime as Date 
       from 
          test as t1 
       order by 
          t1.datetime desc 
       limit 1
    ) as t