sql查询语法case，join和group by

我有两个mysql表：

mysql> desc macToNames;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| mac   | varchar(17)   | YES  | UNI | NULL    |       |
| Name  | text          | YES  |     | NULL    |       |
| Seen  | decimal(10,0) | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

和

mysql> desc stats;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| mac    | varchar(17)   | YES  |     | NULL    |       |
| ipAddr | text          | YES  |     | NULL    |       |
| epoch  | decimal(10,0) | NO   |     | NULL    |       |
| sent   | decimal(10,0) | NO   |     | NULL    |       |
| recv   | decimal(10,0) | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

我有一个问题如下，与我的有限 join 技能：

select case when macToNames.Name is null then stats.mac else macToNames.Name end as 'Hostname'
     ,stats.mac as 'mac',stats.ipAddr as 'ipAddr'
     ,stats.epoch as 'epoch'
     ,stats.sent as 'sent'
     ,stats.recv as 'recv' 
  from stats 
  left
  join macToNames 
    on macToNames.mac = stats.mac
  limit 5;
+-------------------+-------------------+---------------+------------+----------+-----------+
| Hostname          | mac               | ipAddr        | epoch      | sent     | recv      |
+-------------------+-------------------+---------------+------------+----------+-----------+
| x1                | 39-F2-BC-2F-4D-E9 | 192.168.1.232 | 1593836118 |   307197 |    623309 |
| someho-lxc        | 29-F2-BC-2F-4D-E9 | 192.168.1.52  | 1593836118 |  4273599 |   4207535 |
| 39-F2-BC-2F-4D-E9 | 39-F2-BC-2F-4D-E9 | 192.168.1.216 | 1593836118 |     4899 |      6503 |
| tinker            | 39-F2-AC-2F-4D-E9 | 192.168.1.166 | 1593836119 |    60312 |   8563601 |
| u1                | 3A-F2-BC-2F-4D-E9 | 192.168.1.172 | 1593836119 |      380 |       380 |
+-------------------+-------------------+---------------+------------+----------+-----------+

这对我来说很困难-我希望将上面的查询作为子查询运行

mysql> select Hostname,mac from (select case when macToNames.Name is null then stats.mac else macToNames.Name end as 'Hostname',stats.mac as 'mac',stats.ipAddr as 'ipAddr',stats.epoch as 'epoch',stats.sent as 'sent',stats.recv as 'recv' from stats left join macToNames on macToNames.mac=stats.mac);
ERROR 1248 (42000): Every derived table must have its own alias

还有一个：

mysql> select Hostname,mac,sum(stats.Sent)/(1000000000) as 'Sent',sum(stats.Recv)/1000000000 as 'Recv' group by mac from (select case when macToNames.Name is null then stats.mac else macToNames.Name end as 'Hostname',stats.mac as 'mac',stats.ipAddr as 'ipAddr',stats.epoch as 'epoch',stats.sent as 'sent',stats.recv as 'recv' from stats left join macToNames on macToNames.mac=stats.mac);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by mac from (select case when macToNames.Name is null then stats.mac else ' at line 1

我试图生成一个报告，总流量的每台mac电脑，并显示友好的名称为该设备。我完全迷失在案件和加入之间-你能给我指出正确的方向吗？

如错误所示，必须为中的派生表或子查询提供别名 FROM 或者 JOIN 条款。此外，对于聚合查询 SELECT 列必须存在于 GROUP BY （以下是 FROM 以及 JOIN 条款）。注意下面顶层中的所有列都由别名限定 sub . 另外，您的列别名 stats 字段是多余的，因为没有使用表达式，它们重复原始列名。最后，对于许多零，使用 E 为了可读性。

select sub.Hostname
       , sub.mac
       , sum(sub.Sent)/1E9 as 'Sent'
       , sum(sub.Recv)/1E9 as 'Recv' 
from 
  (select case 
              when macToNames.Name is null 
              then stats.mac 
              else macToNames.Name 
           end as 'Hostname'
           , stats.mac
           , stats.ipAddr
           , stats.epoch
           , stats.sent
           , stats.recv
    from stats 
    left join macToNames 
         on macToNames.mac=stats.mac
  ) as sub
group by sub.Hostname
       , sub.mac

在mysql 8.0+中，可以使用CTE代替派生表：

with sub AS (
   select case 
            when macToNames.Name is null 
            then stats.mac 
            else macToNames.Name 
          end as 'Hostname'
          , stats.mac
          , stats.ipAddr
          , stats.epoch
          , stats.sent
          , stats.recv
   from stats 
   left join macToNames 
         on macToNames.mac=stats.mac)

select sub.Hostname
       , sub.mac
       , sum(sub.Sent)/1E9 as 'Sent'
       , sum(sub.Recv)/1E9 as 'Recv' 
from sub
group by sub.Hostname
       , sub.mac

sql查询语法case，join和group by

1条答案

相关问题

热门标签

最新问答