mysql union合并不同列

sdnqo3pr 于 2022-11-21 发布在 Mysql

关注(0)|答案(1)|浏览(209)

我想移除空值并将昨天的值上移但我不知道怎么做.

完整sql：

(SELECT
    COUNT(1) toDay, NULL AS yesterDay
  FROM
    bas_user
    
  WHERE UNIX_TIMESTAMP(user_datetime) BETWEEN UNIX_TIMESTAMP(
          DATE_FORMAT(CURDATE(), '%Y-%m-%d %H:%i:%s')
        )
        AND UNIX_TIMESTAMP(NOW())
    GROUP BY HOUR(user_datetime))
UNION
(SELECT
   NULL AS toDay,COUNT(1) yesterDay
  FROM
    bas_user
     WHERE  UNIX_TIMESTAMP(user_datetime) BETWEEN UNIX_TIMESTAMP(
          DATE_SUB(
            DATE_FORMAT(CURDATE(), '%Y-%m-%d %H:%i:%s'),
            INTERVAL 1 DAY
          )
        )
        AND UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
        
  GROUP BY HOUR(user_datetime)
  )

mysql

来源：https://stackoverflow.com/questions/74454622/mysql-union-merge-different-columns

1条答案

按热度按时间

xe55xuns1#

若要合并两个结果集，您需要一个链接索引键。例如，假设user_id是两者的链接索引键。

-- Step 1 
create table user_today (
    user_id      int,
    today_count  int);
    
create table user_yesterday (
    user_id         int,
    yesterday_count int);
    
 
insert into user_today values (101, 10), (102, 20), (103, 30);
insert into user_yesterday values (102, 25), (103, 35), (104, 45);

-- Step 2
select COALESCE(t.user_id, y.user_id) as user_id,
       t.today_count,
       y.yesterday_count
  from user_today t
  left
  join user_yesterday y
 using (user_id)
 union
select COALESCE(y.user_id, t.user_id) as user_id,
       t.today_count,
       y.yesterday_count
  from user_yesterday y
  left
  join user_today t
 using (user_id);

结果：

user_id|today_count|yesterday_count|
-------+-----------+---------------+
    101|         10|               |
    102|         20|             25|
    103|         30|             35|
    104|           |             45|

赞(0）回复(0）举报 2022-11-21

我来回答

mysql union合并不同列

1条答案

相关问题

热门标签

最新问答