MySQL如何将WHERE子句中未指定的值分组为“其他”？

Question

我需要制作一份网站访问量的统计报告。在报告中，用户可以指定各种参数。到目前为止，每当他们为参数选择值时，就会添加一个额外的 WHERE 子句，如下所示：

SELECT id_browser, COUNT(visits)
FROM table
WHERE [...] 
AND id_browser IN (1, 3, 6)
GROUP BY id_browser

但我真正需要做的是显示其余浏览器的统计信息，但我需要将它们分组为“其他”浏览器”。我该怎么做？

Answer 1

尝试以下：

    SELECT id_browser, 
    count(if(id_browser IN (1, 3, 6),1,0)) as 136browser,
    count(if(id_browser Not IN (1, 3, 6),1,0)) as other_browser
    FROM table
    WHERE [...] 
    GROUP BY id_browser

Answer 2

使用 CASE 语法：

例如，

SELECT CASE
           WHEN id_browser = 1 THEN 'IE'      -- I am just inventing
           WHEN id_browser = 3 THEN 'Chrome'  -- some mappings
           WHEN id_browser = 6 THEN 'Firefox' -- for these
           ELSE 'All Other Browsers'
       END
       ,COUNT(visits)
 FROM [table]
WHERE [...] 
GROUP BY CASE
           WHEN id_browser = 1 THEN 'IE'
           WHEN id_browser = 3 THEN 'Chrome'
           WHEN id_browser = 6 THEN 'Firefox'
           ELSE 'All Other Browsers'
       END

Answer 3

您可以使用 UNION 合并结果，

试试这个：

SELECT *
FROM
(
    SELECT id_browser, COUNT(visits)
    FROM table
    WHERE [...] 
    AND id_browser IN (1, 3, 6)
    GROUP BY id_browser
         UNION
    SELECT 'Other Browser' as id_browser, COUNT(visits)
    FROM table
    WHERE [...] 
    AND id_browser NOT IN (1, 3, 6)
) AllResult

Answer 4

听起来这是一个简单的联合：

select id_browser, count(visits) from table
where [...] and id_browser in (1, 3, 6)
group by id_browser
union
select 'Other browsers', count(visits) from table
where [...] and id_browser not in (1, 3, 6)
group by id_browser

Answer 5

Try this

SELECT 
   SUM( CASE WHEN id_browser =1 then 1 else 0 ) AS IE
   SUM( CASE WHEN id_browser =2 then 1 else 0 ) AS FIREFOX
   SUM( CASE WHEN id_browser =3 then 1 else 0 ) AS CHROME
   SUM( CASE WHEN id_browser not in (1,2,3) then 1 else 0 ) AS OTHERS
FROM tableName