在 MySQL 中使用 UNION ALL 和 INNER JOIN 合併數據

我嘗試將數據合併到一行。我有疑問：

(select t.`id`, 
       t.`coins_id`, 
       t.`first_coin`, 
       t.`second_coin`, 
       t.`price` as `price_old`, 
       null as `price_young`, 
       t.`time`
from hist_all t
inner join (
 select `first_coin`, 
       MIN(`time`) as `MINDATE` 
 from hist_all group by `first_coin` 
) t1 
on t.`first_coin` = t1.`first_coin` 
and (t.`time` = t1.`MINDATE`) 
WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
and `time` between (NOW() - interval 9999 minute) 
AND NOW()
group by t.`first_coin` 
order by t.`ID`)
UNION ALL
(select t.`id`, 
       t.`coins_id`, 
       t.`first_coin`, 
       t.`second_coin`, 
       null as `col_null_1`, 
       t.`price` as `new`, 
       t.`time`
from hist_all t
inner join (
 select `first_coin`, 
        MAX(`time`) as `MAXDATE` 
  from hist_all 
  group by `first_coin`
) t1 
on t.`first_coin` = t1.`first_coin` 
and (t.`time` = t1.`MAXDATE`) 
WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
and `time` between (NOW() - interval 9999 minute) 
AND NOW()
group by t.`first_coin` 
order by t.`ID`)

例如，此查詢重複：

我需要這樣的結果：

id和值是無關緊要的，我舉了他們的例子coins_id。time

這可能可以簡化，但在最簡單的形式中，您需要做的就是對 price_old 和 price_young 使用聚合函式：

select first_coin, second_coin, max(price_old), max(price_young)
from (
   (select t.`id`, 
           t.`coins_id`, 
           t.`first_coin`, 
           t.`second_coin`, 
           t.`price` as `price_old`, 
           null as `price_young`, 
           t.`time`
   from hist_all t
   inner join (
     select `first_coin`, 
           MIN(`time`) as `MINDATE` 
     from hist_all group by `first_coin` 
   ) t1 
   on t.`first_coin` = t1.`first_coin` 
   and (t.`time` = t1.`MINDATE`) 
   WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
   and `time` between (NOW() - interval 9999 minute) 
   AND NOW()
   group by t.`first_coin` 
   order by t.`ID`)
   UNION ALL
   (select t.`id`, 
           t.`coins_id`, 
           t.`first_coin`, 
           t.`second_coin`, 
           null as `col_null_1`, 
           t.`price` as `new`, 
           t.`time`
   from hist_all t
   inner join (
     select `first_coin`, 
            MAX(`time`) as `MAXDATE` 
      from hist_all 
      group by `first_coin`
   ) t1 
   on t.`first_coin` = t1.`first_coin` 
   and (t.`time` = t1.`MAXDATE`) 
   WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
   and `time` between (NOW() - interval 9999 minute) 
   AND NOW()
   group by t.`first_coin` 
   order by t.`ID`)
) as t
group by first_coin, second_coin

引用自：https://dba.stackexchange.com/questions/204574