php - 执行 GROUP BY 时强制将特定记录置顶

Question

我有以下 MySQL 查询和从中查询的表:

SELECT
 `Song`.`id`,
 `Song`.`file_name`,
 `User`.`first_name`,
 `Vote`.`value`,
 Sum(`Vote`.`value`) AS score
FROM `songs` AS `Song`
LEFT JOIN votes AS `Vote` ON (`Song`.`id`=`Vote`.`song_id`)
LEFT JOIN `users` AS `User` ON (`Song`.`user_id` = `User`.`id`)
GROUP BY `Vote`.`song_id`
LIMIT 20;

mysql> describe songs;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment | 
| file_name | varchar(255) | NO   |     | NULL    |                | 
| user_id   | int(11)      | NO   |     | NULL    |                | 
| created   | datetime     | NO   |     | NULL    |                | 
| modified  | datetime     | NO   |     | NULL    |                | 
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| username   | varchar(255) | NO   |     | NULL    |                | 
| password   | varchar(255) | NO   |     | NULL    |                | 
| first_name | varchar(255) | NO   |     | NULL    |                | 
| last_name  | varchar(255) | NO   |     | NULL    |                | 
| is_admin   | tinyint(1)   | NO   |     | 0       |                | 
| created    | datetime     | NO   |     | NULL    |                | 
| modified   | datetime     | NO   |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

mysql> describe votes;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment | 
| value    | int(11)  | NO   |     | NULL    |                | 
| song_id  | int(11)  | NO   |     | NULL    |                | 
| user_id  | int(11)  | NO   |     | NULL    |                | 
| created  | datetime | NO   |     | NULL    |                | 
| modified | datetime | NO   |     | NULL    |                | 
+----------+----------+------+-----+---------+----------------+

这个查询的功能就像我想要的那样，除了一件事之外。 Vote.value 字段中返回的值不是来自与登录应用程序的用户关联的行。我需要得分值是所有值的总和，无论它与哪个用户关联，但 Vote.value 字段应该属于登录用户(每个用户每首歌只能获得一个投票记录)。

我的第一个想法是以某种方式对表进行排序，以便当分组发生时，登录用户的投票记录位于顶部，但我不知道如何进行强制任意值到顶部的排序。任何想法都会非常有帮助。

Answer 1

第三个加入

LEFT JOIN votes AS `VotePerUser` ON (`Song`.`id`=`Vote`.`song_id` 
AND `Song`.`user_id`=`votes`.`user_id`)

并将 Vote.value 替换为 VotePerUser.Value