如何从多个表中计算不同的id？

2 回答

• 0

  非常简单 . 您的数据库用于存储和检索数据 . 不要很好地格式化它 . 你做的是

  select date, count(distinct id) from table1 group by date
  

  然后你对第二个表做同样的事情 .

  其余的在应用层完成 .

  回复于 2024-05-03T14:39:34+08:00
• 0

  一种方法是使用UNION ALL将总的唯一ID粘在一起 .

  然后SUM总计 .

  SELECT 
  `date` AS `Date`, 
  SUM(TotalUniqueId1) AS `Count First Table`,
  SUM(TotalUniqueId2) AS `Count Second Table`,
  SUM(TotalUniqueId3) AS `Count 3th Table`,
  SUM(TotalUniqueId4) AS `Count 4th Table`
  FROM
  (
    SELECT `date`, 
    COUNT(DISTINCT id) AS TotalUniqueId1, 
    0 AS TotalUniqueId2,
    0 AS TotalUniqueId3,
    0 AS TotalUniqueId4
    FROM `Table1`
    GROUP BY `date`
  
    UNION ALL
  
    SELECT `date`, 0, COUNT(DISTINCT id), 0, 0
    FROM `Table2`
    GROUP BY `date`
  
    UNION ALL
  
    SELECT `date`, 0, 0, COUNT(DISTINCT id), 0
    FROM `Table3`
    GROUP BY `date`
  
    UNION ALL
  
    SELECT `date`, 0, 0, 0, COUNT(DISTINCT id)
    FROM `Table4`
    GROUP BY `date`
  ) q
  GROUP BY `date`
  ORDER BY `date`;
  

  回复于 2024-05-03T14:39:34+08:00