I have one sql table that looks like this called "posts":
我有一个sql表，它看起来像这样的“posts”:
id | user
--------------------------------
0 | tim
1 | tim
2 | bob
And another called "votes" that stores either upvotes or downvotes on the posts in the "posts" table:
还有一种叫做“投票”，它在“职位”表中储存了向上或向下的选票:
id | postID | type
--------------------------------
0 | 0 | 0
1 | 2 | 1
2 | 0 | 1
3 | 0 | 1
4 | 3 | 0
In this table, the 'type' is either a 0 for downvote or 1 for upvote.
在该表中，“类型”要么是0表示下投，要么是1表示上投。
How would I go about ordering posts by "tim" by the number of (upvotes - downvotes) the post has?
我将如何通过“蒂姆”的数量(向上拉选票)来订购邮递?
3 个解决方案
#1
3
SELECT
p.id,
p.user,
SUM(v.type * 2 - 1) AS votecount
FROM posts p
LEFT JOIN votes v ON p.id = v.postID
WHERE p.user = 'tim'
GROUP BY p.id, p.user
ORDER BY votes DESC
UPDATE – p and v explained.
更新- p和v解释。
In this query, p and v are aliases of, respectively, posts and votes. An alias is essentially an alternative name and it is defined only within the scope of the statement that declares it (in this case, the SELECT statement). Not only a table can have an alias, but a column too. In this query, votecount is an alias of the column represented by the SUM(v.type * 2 - 1) expression. But presently we are talking only about tables.
在这个查询中，p和v分别是post和vote的别名。别名实质上是一个替代名称，它只在声明它的语句范围内定义(在本例中是SELECT语句)。不仅表可以有别名，而且列也可以有别名。在这个查询中，votecount是由SUM(v)表示的列的别名。类型* 2 - 1)表达式。但是现在我们只讨论桌子。
Before I go on with explanation about table aliases, I'll briefly explain why you may need to prefix column names with table names, like posts.id as opposed to just id. Basically, when a query references more than one table, like in this case, you may find it quite useful always to prefix column names with the respective table names. That way, when you are revisiting an old script, you can always tell which column belongs to which table without having to look up the structures of the tables referenced. Also it is mandatory to include the table reference when omitting it creates ambiguity as to which table the column belongs to. (In this case, referencing the id column without referencing the posts table does create ambiguous situation, because each table has got their own id.)
在我继续解释表别名之前，我将简要地解释为什么需要在列名前面加上表名，比如post。id与id相反，基本上，当查询引用多个表时(如本例中所示)，您可能会发现总是用各自的表名作为列名的前缀是非常有用的。这样，当您重新访问旧脚本时，您总是可以知道哪个列属于哪个表，而不必查询引用的表的结构。此外，在省略表引用时，还必须包含表引用，这会造成列属于哪个表的模糊性。(在这种情况下，引用id列而不引用posts表确实会造成不明确的情况，因为每个表都有自己的id。)
Now, a large and complex query may be difficult to read when you write out complete table names before column names. This is where (short) aliases come in handy: they make a query easier to read and understand, although I've already learnt that not all people share that opinion, and so you should judge for yourself: this question contains two versions of the same query, one with long-named table references and the other with short-aliased ones, as well as an opinion (in a comment to one of the answers) why aliases are not suitable.
现在，当您在列名之前写出完整的表名时，可能很难阅读大型复杂查询。这就是(短)别名派上用场:他们使查询更容易阅读和理解,尽管我已经知道,并不是所有的人分享观点,所以你应该自己作出判断:这个问题包含两个相同版本的查询,一个与short-aliased long-named表引用和其他国家,以及意见(在评论的一个答案)为什么别名是不合适的。
Anyway, using short table aliases in this particular query may not be as beneficial as in some more complex statements. It's just that I'm used to aliasing tables whenever the query references more than one.
总之，在这个特定查询中使用短表别名可能不如在一些更复杂的语句中有用。只是在查询引用多于一个时，我就习惯于将表混叠。
This MySQL documentation article contains the official syntax for aliasing tables in MySQL (which is actually the same as in standard SQL).
这个MySQL文档文章包含了MySQL中别名表的官方语法(实际上与标准SQL中是一样的)。
#2
1
Not tested, but should work:
没有经过测试，但应该有效:
select post.id, sum(if(type = 0, -1, 1)) as score
from posts join votes on post.id = votes.postID
where user = 'tim'
group by post.id
order by score
Do you plan to concur SO? ;-)
你打算同意吗?:-)
#3
1
Edit: I cut out the subquery since in mysql its unnecessary. The original query was portable, but unnecessary for mysql.
编辑:我删除了子查询，因为在mysql中它是不必要的。最初的查询是可移植的，但对mysql来说没有必要。
select
p.id, SUM(case
when v.type = 0 then -1
when v.type = 1 then 1
else 0 end) as VoteCount
from
posts p
left join votes v
on p.id = v.postid
where
p.[user] = 'tim'
group by
p.id
order by
VoteCount desc
#1
3
SELECT
p.id,
p.user,
SUM(v.type * 2 - 1) AS votecount
FROM posts p
LEFT JOIN votes v ON p.id = v.postID
WHERE p.user = 'tim'
GROUP BY p.id, p.user
ORDER BY votes DESC
UPDATE – p and v explained.
更新- p和v解释。
In this query, p and v are aliases of, respectively, posts and votes. An alias is essentially an alternative name and it is defined only within the scope of the statement that declares it (in this case, the SELECT statement). Not only a table can have an alias, but a column too. In this query, votecount is an alias of the column represented by the SUM(v.type * 2 - 1) expression. But presently we are talking only about tables.
在这个查询中，p和v分别是post和vote的别名。别名实质上是一个替代名称，它只在声明它的语句范围内定义(在本例中是SELECT语句)。不仅表可以有别名，而且列也可以有别名。在这个查询中，votecount是由SUM(v)表示的列的别名。类型* 2 - 1)表达式。但是现在我们只讨论桌子。
Before I go on with explanation about table aliases, I'll briefly explain why you may need to prefix column names with table names, like posts.id as opposed to just id. Basically, when a query references more than one table, like in this case, you may find it quite useful always to prefix column names with the respective table names. That way, when you are revisiting an old script, you can always tell which column belongs to which table without having to look up the structures of the tables referenced. Also it is mandatory to include the table reference when omitting it creates ambiguity as to which table the column belongs to. (In this case, referencing the id column without referencing the posts table does create ambiguous situation, because each table has got their own id.)
在我继续解释表别名之前，我将简要地解释为什么需要在列名前面加上表名，比如post。id与id相反，基本上，当查询引用多个表时(如本例中所示)，您可能会发现总是用各自的表名作为列名的前缀是非常有用的。这样，当您重新访问旧脚本时，您总是可以知道哪个列属于哪个表，而不必查询引用的表的结构。此外，在省略表引用时，还必须包含表引用，这会造成列属于哪个表的模糊性。(在这种情况下，引用id列而不引用posts表确实会造成不明确的情况，因为每个表都有自己的id。)
Now, a large and complex query may be difficult to read when you write out complete table names before column names. This is where (short) aliases come in handy: they make a query easier to read and understand, although I've already learnt that not all people share that opinion, and so you should judge for yourself: this question contains two versions of the same query, one with long-named table references and the other with short-aliased ones, as well as an opinion (in a comment to one of the answers) why aliases are not suitable.
现在，当您在列名之前写出完整的表名时，可能很难阅读大型复杂查询。这就是(短)别名派上用场:他们使查询更容易阅读和理解,尽管我已经知道,并不是所有的人分享观点,所以你应该自己作出判断:这个问题包含两个相同版本的查询,一个与short-aliased long-named表引用和其他国家,以及意见(在评论的一个答案)为什么别名是不合适的。
Anyway, using short table aliases in this particular query may not be as beneficial as in some more complex statements. It's just that I'm used to aliasing tables whenever the query references more than one.
总之，在这个特定查询中使用短表别名可能不如在一些更复杂的语句中有用。只是在查询引用多于一个时，我就习惯于将表混叠。
This MySQL documentation article contains the official syntax for aliasing tables in MySQL (which is actually the same as in standard SQL).
这个MySQL文档文章包含了MySQL中别名表的官方语法(实际上与标准SQL中是一样的)。
#2
1
Not tested, but should work:
没有经过测试，但应该有效:
select post.id, sum(if(type = 0, -1, 1)) as score
from posts join votes on post.id = votes.postID
where user = 'tim'
group by post.id
order by score
Do you plan to concur SO? ;-)
你打算同意吗?:-)
#3
1
Edit: I cut out the subquery since in mysql its unnecessary. The original query was portable, but unnecessary for mysql.
编辑:我删除了子查询，因为在mysql中它是不必要的。最初的查询是可移植的，但对mysql来说没有必要。
select
p.id, SUM(case
when v.type = 0 then -1
when v.type = 1 then 1
else 0 end) as VoteCount
from
posts p
left join votes v
on p.id = v.postid
where
p.[user] = 'tim'
group by
p.id
order by
VoteCount desc