mysql - sql查询语法大小写、join和group by

Question

我有两个 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    |       |
+--------+---------------+------+-----+---------+-------+

我有一个如下查询，我的加入技能有限:

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 的总流量的报告，并显示该设备的友好名称。我在案件和加入之间完全迷失了方向 - 你能给我指明正确的方向吗？

Answer 1

如错误所示，您必须在 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+ 中，您可以使用 CTEs代替派生表:

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