在 mysql 中分组时获取最大可能性值 - Fetch maximum possibility value while group by in mysql

Fetch maximum possibility value while group by in mysql

本文关键字：可能性获取 mysql | 更新日期: 2023-09-27

我的数据库中有如下表格

----------------------
id | keyword    | location 
----------------------
1  | Test-New   |    1
2  | Test       |    1
3  | Test       |    1
4  | Test       |    1
----------------------

我需要根据位置获得结果，所以我使用了分组依据

select keyword, location ,count(*) as count from table_name group by location

现在我得到以下结果

----------------------------
keyword    | Location |  count
---------------------------
Test-New   |    1     |   4
---------------------------

但我想要每个位置和每个位置的计数最受欢迎的关键字。在此示例中，我需要位置 1 的 Test 而不是 Test-New（因为在实际表中，Test 出现了 3 次，Test-New 出现了 1 次）。

所以我需要这样的结果

--------------------------
keyword | Location |  count
--------------------------
Test    |    1     |   4
--------------------------

这

工作正常......享受拉古拜

   SELECT  
temp.keyword,temp.location,SUM(temp.TotalLocation) AS COUNT
 FROM (SELECT 
    keyword,location,
    COUNT(location) AS TotalLocation
  FROM
    `test` 
  GROUP BY location,keyword ORDER BY TotalLocation DESC)
  temp GROUP BY location;

演示在此处执行

你能试试这个查询吗：

select keyword ,count(keyword) as TotalKeyword from table_name group by location

SELECT a.*
  FROM 
     ( SELECT keyword
           , location
           , COUNT(*) cnt 
        FROM my_table 
       GROUP 
          BY keyword
           , location
     ) a
  JOIN
     ( SELECT keyword
            , MAX(cnt) max_cnt
         FROM 
            ( SELECT keyword
                   , location
                   , COUNT(*) cnt 
                FROM my_table 
               GROUP 
                  BY keyword
                   , location
            ) x 
        GROUP 
           BY keyword
     ) b
    ON b.keyword = a.keyword
   AND b.max_cnt = a.cnt;

你的问题有点混乱，你要求这样的东西吗？

select *,
count(location) as location_count
from test1 group by location, keyword