mysql - 我无法从 sql 中的计数值获取最大值

Question

我有两个表。表 t1 有列(ID，名称)，t2 有列(ID，日期，时间)...我想运行像

这样的查询

select t1.Name,t2.ID,count(t2.Date)as Present_Days,MAX(x) as total 
FROM (SELECT COUNT(t2.ID) AS x FROM t2 GROUP BY ID) as y 
from t1 
INNER JOIN t2 on t1.ID=t2.ID 
group by Name

当我从查询中删除这一行“MAX(x) as Total FROM (SELECT COUNT(t2.ID) AS x FROM t2 GROUP BY ID) as y”时，它给出了结果..但原始查询不起作用...可以执行什么操作来运行此查询。

Answer 1

错误的 FROM ( ) 动态表和两个 FROM 而不是正确的内部联接，可能是您正在寻找类似的东西

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
  FROM ( 
          SELECT t2.ID, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID

如果您需要 t2 中的另一个名为 time 的列，您可以使用

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
      , t2.`time`
  FROM ( 
          SELECT t2.ID, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID, t2.`time`

事实上，您加入了表 t2，您可以直接在主选择中选择 t2.time ..

无论如何，您也可以从子选择中选择 t2.time，在子查询中正确添加此列并使用适当的别名，例如下面示例中的列 my_time

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
      , y.my_time
  FROM ( 
          SELECT t2.ID, t2.`time` as my_time, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID, t2.`time`

对于absent_days，您可以使用所涉及的两列之间的差异(无别名)

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
      , t2.`time`
      , MAX(y.x) - count(t2.Date) as Absent_Days
  FROM ( 
          SELECT t2.ID, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID, t2.`time`

如果您需要按名称过滤，可以

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
      , t2.`time`
      , MAX(y.x) - count(t2.Date) as Absent_Days
  FROM ( 
          SELECT t2.ID, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  where   t1.Name =  'somename' 
  group by t1.Name, t2.ID, t2.`time`