你应该用 SUM 代替 COUNT 当其中的条件为真时，1是返回值，当它为假时，0是返回值，因此对这些值求和就好像是根据条件对它们进行计数一样。你还应该确保日期中没有时间。

SELECT 
  DATE(`date`) `date`,
  SUM(deviceType="mobile") As numOfMobile,
  SUM(deviceType="desktop") AS numOfDesktop,
  SUM(deviceType="tablet") AS numOfTablet
FROM 
  tableName
GROUP BY DATE(`date`)
ORDER BY DATE(`date`);

要使用count，请执行以下操作：

SELECT 
  DATE(`date`) `date`,
  COUNT(IF(deviceType="mobile", 1, NULL)) As numOfMobile,
  COUNT(IF(deviceType="desktop", 1, NULL)) AS numOfDesktop,
  COUNT(IF(deviceType="tablet", 1, NULL)) AS numOfTablet
FROM 
  tableName
GROUP BY DATE(`date`)
ORDER BY DATE(`date`);

在这种情况下，如果条件为false，则返回null，并且count函数不考虑这一点。

你的想法是对的，不过，正如你所看到的，这不是有效的语法。你需要一个 case 尽管如此：

SELECT 
  date,
  COUNT(CASE deviceType WHEN 'mobile' THEN 1 END) As numOfMobile,
  COUNT(CASE deviceType WHEN 'desktop' THEN 1 END) AS numOfDesktop,
  COUNT(CASE deviceType WHEN 'tablet' THEN 1 END) AS numOfTablet
FROM 
  tableName
GROUP BY date
ORDER BY date ASC

您可以尝试如下方式：

SELECT 
    date,
    sum(deviceType= "desktop") As numOfDesktop,
    sum(deviceType= "tablet") As numOfTablet,
    sum(deviceType = 'mobile') as numOfMobile
FROM 
    tableName
GROUP BY date
ORDER BY date ASC