php - SQL 返回 1 行

Question

是否可以采用以下查询并让它返回 1 行数据而不是 5 行？

 select count(rank_1) * 5 as 1st from fcs_player_rankings where rank_1 = "$wp_player['id']"
 UNION ALL
 select count(rank_2) * 4 as 2nd from fcs_player_rankings where rank_2 = "$wp_player['id']"
 UNION ALL
 select count(rank_3) * 3 as 3rd from fcs_player_rankings where rank_3 = "$wp_player['id']"
 UNION ALL
 select count(rank_4) * 2 as 4th from fcs_player_rankings where rank_4 = "$wp_player['id']"
 UNION ALL
 select count(rank_5) * 1 as 5th from fcs_player_rankings where rank_5 = "$wp_player['id']"

表架构如下所示:

mysql> desc fcs_player_rankings;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11) | NO   | MUL | NULL    |                |
| award_id | int(11) | NO   |     | NULL    |                |
| rank_1   | int(11) | YES  |     | NULL    |                |
| rank_2   | int(11) | YES  |     | NULL    |                |
| rank_3   | int(11) | YES  |     | NULL    |                |
| rank_4   | int(11) | YES  |     | NULL    |                |
| rank_5   | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

现在，此查询将为我在单独的行中提供的每个 ID 返回 rank_1、rank_2、rank_3、rank_4 和 rank_5。有没有办法把所有的排名排成一行？下面的 LIMIT 1 子句在逻辑上不能解决这个问题。

Answer 1

尝试条件聚合

SELECT SUM(rank_1 = "$wp_player['id']") * 5 `1st`,
       SUM(rank_2 = "$wp_player['id']") * 4 `2nd`,
       SUM(rank_3 = "$wp_player['id']") * 3 `3rd`,
       SUM(rank_4 = "$wp_player['id']") * 2 `4th`,
       SUM(rank_5 = "$wp_player['id']") * 1 `5th`
 FROM fcs_player_rankings

这是 SQLFiddle 演示