时间戳

首页 > 解决方案 > 如何在查询中将多个表连接在一起

mysql - 如何在查询中将多个表连接在一起

问题描述

我对 MySQL 还很陌生，并且在将数据从多个表中提取到一个表中时遇到了问题（如果这有意义的话！）我可以按行而不是按列获取数据 - 我哪里出错了？

这是我到目前为止所拥有的：（仅限于 'id=18' 进行测试）

SELECT name from users where id=18
UNION
SELECT COUNT(*) as ACTIVE_RADARS FROM `radars` as at WHERE DATE(`lastHeartbeat`) = CURDATE() AND userId = 18 GROUP BY `userId`
UNION
SELECT COUNT(*) as ALERTS_TODAY FROM `alerts` as at WHERE DATE(`created_at`) = CURDATE() AND userId = 18 GROUP BY `userId`
UNION
SELECT COUNT(*) as ALERTS_THIS_MONTH  FROM `alerts` as atm WHERE `userId` = 18 AND created_at > DATE_FORMAT(NOW() ,'%Y-%m-01') GROUP BY `userId`
UNION
SELECT COUNT(*) as SCANNED_TODAY FROM `scanned_urls` as at WHERE DATE(`created_at`) = CURDATE() AND userId = 18 GROUP BY `userId`
UNION
SELECT COUNT(*) as SCANNED_THIS_MONTH FROM `scanned_urls` as sim WHERE `userId` = 18 AND created_at > DATE_FORMAT(NOW() ,'%Y-%m-01') GROUP BY `userId`

就目前而言，我在一列中有行，但我需要在多列中有 1 行。

任何帮助表示赞赏。

标签： mysql

解决方案

这是开始：

SELECT u.name
  FROM users 
 WHERE u.id = 18

然后我们可以得到行数

SELECT r.userid
     , COUNT(*) AS active_radars
  FROM `radars` r 
 WHERE r.lastheartbeat >= DATE(NOW()) 
   AND r.lastheartbeat <  DATE(NOW()) + INTERVAL 1 DAY 
   AND r.userid = 18 
 GROUP
    BY r.userId

我们可以使该查询成为内联视图，并执行外连接。像这样的东西：

SELECT u.name
     , ar.active_radars
     , al.alerts_today
  FROM users

  LEFT
  JOIN ( SELECT r.userid
              , COUNT(*) AS active_radars
           FROM `radars` r 
          WHERE r.lastheartbeat >= DATE(NOW()) 
            AND r.lastheartbeat <  DATE(NOW()) + INTERVAL 1 DAY 
            AND r.userid = 18 
          GROUP
             BY r.userId
       ) ar
    ON ar.userid = u.id

  LEFT
  JOIN ( SELECT a.userid
              , COUNT(*) AS alerts_today
           FROM `alerts` a
          WHERE a.created_at >= CURDATE()
            AND a.created_at <  CURDATE() + INTERVAL 1 DAY
            AND a.userid = 18
          GROUP
             BY a.userid
       ) al
   ON al.userid = u.id

 WHERE u.id = 18

鉴于我们只需要返回计数，作为连接的替代方法，我们可以在 SELECT 列表中使用相关子查询。

推荐阅读