主页 / dba / 问题 / 95981
Accepted
Dannymh
Asked: 2015-03-24 06:15:42 +0800 CST

子查询的别名不起作用

  • 772

我一直在尝试通过聚合函数正常工作来获得订单。

即按（bp +点）排序

但是，我也尝试将这些作为总量从以下查询中添加。如果我尝试将这两列加在一起，我会收到一个错误，即找不到字段 bp。

如果我尝试通过添加这些字段进行排序，则无法正确排序，只能按点排序

询问

SELECT COUNT(game.game_id) AS correct_picks, 
       sum(game.points) points, 
       pick.user_id,

       (select sum(game.points)    
        FROM xf_nflj_pickem_pick pick
        LEFT JOIN xf_nflj_pickem_game game
        ON game.game_id = pick.game_id
        WHERE game.bonus=1 AND pick.user_id = user.user_id 
            and game.week_id <= 8 AND game.winner = pick.team_id
        Group by user.user_id) as bp,

       (bp + points) as total,

       user.user_id, 
       user.username, 
       user.avatar_date, 
       user.avatar_width, 
       user.avatar_height, 
       user.gravatar,  
       user.gender 
FROM xf_nflj_pickem_game game   
    LEFT JOIN xf_nflj_pickem_pick as pick
        ON (pick.game_id = game.game_id)        
    LEFT JOIN xf_user as user
        ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id) 
    AND game.week_id <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0               
GROUP BY pick.user_id 
ORDER BY (points + bp) DESC, user.username, pick.user_id ASC

如果我只使用 order by

correct_picks | points| user_id| bp| 
16            | 16    | 845    | 3 |
14            | 14    | 1698   | 3 |
16            | 16    | 109    | 3 |
19            | 19    | 26787  | 3 |

所以这里有很多随机结果，而不是按以下顺序排序

用户 ID, 26787, 845, 109, 1698

mysql subquery

1 个回答

  1. Best Answer

    尝试这个：

    SELECT correct_picks, points, pick_user_id, bp, (bp + points) AS total, user_user_id, username, avatar_date, avatar_width, avatar_height, gravatar, gender
FROM
(
SELECT COUNT(game.game_id) AS correct_picks, 
       SUM(game.points) points, 
       pick.user_id AS 'pick_user_id', -- Alias to avoid same name with user.user_id

       (SELECT SUM(game.points)    
    FROM xf_nflj_pickem_pick pick
    LEFT JOIN xf_nflj_pickem_game game
    ON game.game_id = pick.game_id
    WHERE game.bonus=1 AND pick.user_id = user.user_id 
        AND game.week_id <= 8 AND game.winner = pick.team_id
    GROUP BY user.user_id) AS bp,

       user.user_id AS 'user_user_id', -- Alias to avoid same name with pick.user_id
       user.username, 
       user.avatar_date, 
       user.avatar_width, 
       user.avatar_height, 
       user.gravatar,  
       user.gender 
FROM xf_nflj_pickem_game game   
    LEFT JOIN xf_nflj_pickem_pick AS pick
    ON (pick.game_id = game.game_id)        
    LEFT JOIN xf_user AS USER
    ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id) 
    AND game.week_id <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0               
GROUP BY pick.user_id 
) tbl1 -- Alias for the subquery as table
ORDER BY (points + bp) DESC, username, pick_user_id ASC;

    将您的查询变成一个表（作为子查询），然后使用ORDER BY (points + bp) DESC, username, pick_user_id ASC.

    似乎mysql不允许在与别名列相同的行或层次结构中使用列的别名。

    • 0

相关问题