php - 在 MySQL 中，我需要按位置检索得分最高的艺术家

Question

在 MySQL 中，我需要按位置获取得分最高的艺术家。请帮忙。

user_location_tbl(用户位置表)

|--------------------------------------|
| countryLat | countryLong  |  userid  |
|--------------------------------------|
| 31.695766  | 54.624023    |  1       |
| 20.593684  | 78.96288     |  2       |
| 20.593684  | 78.96288     |  3       |
| 20.593684  | 78.96288     |  4       |
| 31.695766  | 54.624023    |  5       |
|--------------------------------------|

fans_table(查看哪个艺术家有哪个用户作为粉丝)

|----------------------|----------
| artist_id  | user_id |  Points |   
|----------------------|----------
| 1          | 1       |    20    |
| 1          | 2       |    30    |
| 2          | 1       |    40    |
| 2          | 3       |    40    |   
| 3          | 1       |    60    |
|----------------------|----------

artist_table(艺术家列表)

|-------------------|
| artistid | name   |
|-------------------|
| 1        | raja   | 
| 2        | sekar  |
| 3        | thomas |
|-------------------|

我需要明智地了解哪个艺术家的位置最高，但我无法在单个查询中完成。如果我将 sum(points) 和 countryLat、countryLong、artistid 分组，我会得到以下结果...

|---------------------------------|
| 100 | 20.593684 | 78.96288  | 1 |
| 50  | 20.593684 | 78.96288  | 2 |
| 100 | 31.695766 | 54.624023 | 3 |
| 90  | 31.695766 | 54.624023 | 1 |
|---------------------------------|

...但我只需要在该位置具有最高点的艺术家，如下例所示...

|---------------------------------|
| 100 | 20.593684 | 78.96288  | 1 |
| 100 | 31.695766 | 54.624023 | 3 |
|---------------------------------|

SQL

SELECT Sum(c.tot_points), 
       a.artist_id 
FROM   `fans_table` AS a 
       INNER JOIN `user_location_tbl` AS b 
               ON a.user_id = b.user_id 
       INNER JOIN artist_table AS c 
               ON a.artist_id = c.id 
GROUP  BY b.countrylat, 
          b.countrylong, 
          a.artist_id 

以上是我的查询

Answer 1

所以假设我理解你的问题是正确的，你想知道哪个艺术家在每个位置拥有最多的分数，所以这将是手动计算的过程:

// First we add up all the points for each artist in each location:
|----------------------------------------------|
| Points | countryLat | countryLong | artistid |
|----------------------------------------------|
|    30  |  20.593684 |  78.96288   |     1    |
|    40  |  20.593684 |  78.96288   |     2    |
|     0  |  20.593684 |  78.96288   |     3    |
|    20  |  31.695766 |  54.624023  |     1    |
|    40  |  31.695766 |  54.624023  |     2    |
|    60  |  31.695766 |  54.624023  |     3    |
|----------------------------------------------|

// Then we get the max for each location
|----------------------------------------------|
| Points | countryLat | countryLong | artistid |
|----------------------------------------------|
|    40  |  20.593684 |  78.96288   |     2    |
|    60  |  31.695766 |  54.624023  |     3    |
|----------------------------------------------|

执行此操作的查询如下

SELECT artist_id, ul.countryLat, ul.countryLong, max_points
FROM fans_table f
JOIN user_location_tbl ul
ON ul.userid = f.user_id
JOIN (
    SELECT countryLat, countryLong, MAX(f.total_points) max_points
    FROM
        (SELECT artist_id, countryLat, countryLong, SUM(Points) as total_points
        FROM fans_table f
        JOIN user_location_tbl ul
        ON ul.userid = f.user_id
        GROUP BY artist_id, countryLat, countryLong) f
    GROUP BY countryLat, countryLong) p
ON p.countryLat = ul.countryLat
AND p.countryLong = ul.countryLong
GROUP BY f.artist_id, ul.countryLat, ul.countryLong
HAVING p.max_points = SUM(Points)