如何通过其他表值来命令sql查询

i know that * is remember answer for this question, but I have a bit different situation. I have a lot cells in second table, but SQL query get only first. Ok, not all query, only WHERE tag.

我知道*是这个问题的记忆答案,但我有一点不同的情况。我在第二个表中有很多单元格,但SQL查询只是第一个。好的,不是所有查询,只有WHERE标记。

Ex:
1 table:

例:1表:

user_id = 1
user_id = 2

2 table:

user_id = 1 | year = 2015 | rating = 55
user_id = 1 | year = 2016 | rating = 10
user_id = 2 | year = 2016 | rating = 50
user_id = 2 | year = 2016 | rating = 5

SQL query:

$query = "SELECT c.*,v.upvotes
        FROM ".PREFIX."_users c
        LEFT JOIN (SELECT user_id,pol,vid_sporta,year_sport,category,SUM(rating) as upvotes
        FROM ".PREFIX."_userrating
        GROUP BY user_id
        ) v
        ON c.user_id = v.user_id
        WHERE year_sport='2015'
        ORDER BY upvotes DESC";

This query give me only one user, whitch has 2015 first.

这个查询只给我一个用户,第一个是2015年。

I need some while cycle in SQL query :) Help, please :)

我在SQL查询中需要一些循环:)帮助,请:)

1 个解决方案

#1

It appears that there maybe a couple of issues going on.

似乎可能会出现一些问题。

First,

SELECT user_id,pol,vid_sporta,year_sport,category,SUM(rating) as upvotes

SHOULD BE

SELECT user_id,SUM(rating) as upvotes

You only want to include the columns that you are returning to the rest of your query and any column that is not apart of an aggregate function must also appear in your GROUP BY statement.

您只希望包含要返回到查询其余部分的列,并且任何不属于聚合函数的列也必须出现在GROUP BY语句中。

Second and this is why you may only be getting 1 record is the location of your WHERE year_sport = '2015'. Because the where condition is in the outer query it is treating your LEFT JOIN as an INNER JOIN and limiting to all results that have that year_sport in userrating. Move the where statement to your inner query and you should get all of your users but only upvotes from year_sport =2015.

第二,这就是为什么你可能只获得1条记录是WHERE year_sport ='2015'的位置。因为where条件在外部查询中,所以它将LEFT JOIN视为INNER JOIN并限制为在用户中具有该year_sport的所有结果。将where语句移动到内部查询中,您应该获得所有用户,但只能使用year_sport = 2015的upvotes。

$query = "SELECT c.*,v.upvotes
        FROM ".PREFIX."_users c
        LEFT JOIN (SELECT user_id,SUM(rating) as upvotes
        FROM ".PREFIX."_userrating
       WHERE year_sport='2015'
        GROUP BY user_id
        ) v
        ON c.user_id = v.user_id
        ORDER BY upvotes DESC";

#1