在 Sql 中冒泡列 [英] Bubbling Up Columns in Sql

问题描述

请原谅这个令人费解的例子，但我相信我缺少一些关于 sql 的基本知识，我不确定它是什么.我有这个疯狂的查询...

Pardon the convoluted example, but I believe there is something fundamental about sql I am missing and I'm not sure what it is. I have this crazy query...

SELECT * 
  FROM (
  SELECT * 
    FROM (
    SELECT @t1 := @t1 +1 AS leaderboard_entry_youngness_rank, 1 - @t1 /100 AS
       leaderboard_entry_youngness_based_on_expiry, leaderboard_entry . * , 
       NOW( ) - leaderboard_entry_timestamp AS leaderboard_entry_age_in_some_units, 
       TO_DAYS( NOW( ) ) - TO_DAYS( leaderboard_entry_timestamp ) 
         AS leaderboard_entry_age_in_days
    FROM leaderboard_entry) AS inner_temp
    NATURAL JOIN leaderboard
    NATURAL JOIN user
    WHERE (
    leaderboard_load_key =  'sk-en-adjectives-1'
    OR leaderboard_load_key =  '-sk-en-adjectives-1'
    )
    AND leaderboard_quiz_mode =  '0'
    ORDER BY leaderboard_entry_age_in_some_units ASC , leaderboard_entry_timestamp ASC 
    LIMIT 0 , 100
  ) AS outer_temp
ORDER BY leaderboard_entry_elapsed_time_ms ASC , leaderboard_entry_timestamp ASC 
LIMIT 0 , 50

我添加了第二个嵌套的 SELECT 语句，因为用户表中的 user_name 没有在最外层查询中返回.但是现在根据行索引比率生成的 leaderboard_entry_youngness_based_on_expiry 字段无法正常工作.

I added the second nested SELECT statement because the user_name in the user table was not being returned in the outermost query. But now the leaderboard_entry_youngness_based_on_expiry field, which is being generated based on a row index ratio, is not working correctly.

如果我删除第二个嵌套的 SELECT 语句，leaderboard_entry_youngness_based_on_expiry 会按预期工作，但不会返回 user_name 列.

If I remove the second nested SELECT statement, the leaderboard_entry_youngness_based_on_expiry works as expected, but the user_name column is not returned.

我怎样才能同时满足两者?为什么会发生这种情况?

How can I satisfy both? Why is this happening?

谢谢！

这源于以下问题:

向返回的 MySQL 添加编号列表列查询

推荐答案

在您内部的 SELECT 语句中，您没有 user.user_name，这就是不返回用户名的原因.删除外部查询，像之前那样做，但使用 user.user_name 像这样:

In your inner SELECT statement, you do not have user.user_name, that's why username is not returned. Remove the outer query, do it like earlier but with user.user_name like this:

....
SELECT @t1 := @t1 +1 AS leaderboard_entry_youngness_rank, 1 - @t1 /100 AS
   leaderboard_entry_youngness_based_on_expiry, leaderboard_entry . * , 
   NOW( ) - leaderboard_entry_timestamp AS leaderboard_entry_age_in_some_units, 
   TO_DAYS( NOW( ) ) - TO_DAYS( leaderboard_entry_timestamp ) 
     AS leaderboard_entry_age_in_days, user.user_name

....

这篇关于在 Sql 中冒泡列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！