Mysql嵌套选择 [英] Mysql Nested Select

问题描述

接着这个问题 last_question 与此表

`id`, `bbs_id`, `user_id`, `like_dislike`
(15,   4,        2,         0),
(14,   4,        1,         0),
(13,   3,        1,         0),
(12,   2,        1,         1),
(11,   1,        2,         0),
(10,   1,        1,         1);

如何查看单个用户喜欢或不喜欢的东西?可以说，我希望有一个汇总表，其中包含所有喜欢和不喜欢的地方，并在另一列中确定用户x是否喜欢它.

How can I see what an individual users like or dislike was? Lets say I wanted to have an aggregate table of all the likes and dislikes with another column for whether user x liked it.

这是我尝试过的查询

$user_id = 1;


SELECT bbs_id,
     (SELECT like_dislike FROM bb_ratings WHERE user_id={$user_id}) AS thisUsersRating,
       SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bb_ratings
GROUP BY bbs_id

我猜我在这里遇到的问题是，如何在此特定行中而不是在所有行中引用user_id = x. 提前致谢 安德鲁

I guess the problem I am running into here is, how do you refer to user_id = x in this particular row, not in all the rows. Thanks in Advance Andrew

推荐答案

我为前面的问题添加了答案，请先参考该答案，以供理解.

I have added an answer to the previous question, please refer to that first, for understanding.

您不能单独通过bb_ratings对其分组并对其进行黑客攻击.之所以会得到Null，是因为您在考虑网格而不是关系集(这是关系模型的核心概念).

You can't get it from bb_ratings alone by GROUPing it and hacking it. You get Null because you are thinking in terms of a grid, not relational sets (that is the central concept of the Relational Model).

1. 在确定要转到哪个表以服务查询之前，需要确定结果集结构所需的内容.

1. Before you decide which table(s) to go to, to service your query, you need to decide what you want for the structure of your result set.

然后用WHERE子句约束它(哪些行).

Then constraint it (which rows) with the WHERE clause.

然后找出要从中获取列的位置(哪些表).要么连接到更多表，要么在WHERE子句上进行更多工作；或标量子查询，与外部查询相关.

Then figure out where (what tables) to get the columns from. Either joins to more tables, and more work on the WHERE clause; or scalar subqueries, correlated to the outer query.

您不清楚您想要什么.看起来您想要与上一个问题相同的报告，并为给定的用户投票添加一列.对我来说，结果集的结构是一个公告列表.好吧，我可以从bulletin那里得到它，而无需转到bulletin_like然后必须将其分组.

You are not clear about what you want. It looks like you want the same report as the previous question, plus a column for the given users vote. To me, the structure of your result set is a list of bulletins. Well, I can get that from bulletin, no need to go to bulletin_like and then have to GROUP that.

如果您从集合的角度考虑，这非常容易，无需在物化视图或嵌套"查询中跳转:

If you think in terms of sets, it is very easy, no need to jump through hoops with materialised views or "nested" queries:

SELECT name AS bulletin, 
    (SELECT COUNT(like) 
        FROM  bulletin_like bl 
        WHERE bl.bulletin_id = b.bulletin_id 
        AND   like = 1
        ) AS like,
    (SELECT COUNT(like) 
        FROM  bulletin_like bl 
        WHERE bl.bulletin_id = b.bulletin_id 
        AND   like = 0
        ) AS dislike,
    (SELECT COUNT(like) 
        FROM  bulletin_like bl 
        WHERE bl.bulletin_id = b.bulletin_id 
        AND   bl.user_id = {$user_d}
        AND   like = 1
        ) AS your_vote
    FROM bulletin b

对评论的回复

我觉得您的发言对于我处理SQL的方式非常重要

I have the feeling that what you are saying is very important for how I approach SQL

1. 是的，绝对是.如果您愿意预先学习正确的知识，它将:

1. Yes, absolutely. If you are willing to learn the right stuff up front, it will:

• 以后为您解决各种问题
• 提高查询效率
• 允许您更快地编写代码
  .

• save you all kinds of problems later
• make your queries more efficient
• allow you to code faster
  .

现在，忘记使用结果集作为表(要慢得多)和临时表(如果数据库是Normalized绝对不需要).直接查询表好很多.您需要学习各种主题，例如关系模型；如何使用SQL；如何不使用SQL以避免出现问题；等.我愿意为您提供帮助，并在您身边待一会儿，但我需要知道您的意愿.来回需要一些时间.这个网站上有些杂音，所以我将忽略其他评论(直到最后)，仅回复您的评论.

For now, forget about using result sets as tables (much slower), and temp tables (definitely not required if your database is Normalised). You are much better off querying the tables directly. You need to learn various subjects such as the Relational model; how to use SQL; how not to use SQL so as to avoid problems; etc. I am willing to help you, and stay with you for a while, but I need to know you are willing. It will take a bit of back-and-forth. There is a bit of noise on this site, so I will ignore other comments (until the end) and respond only to yours.

• 停止使用GROUP BY，这严重阻碍了您对SQL的理解.如果不使用GROUP BY就无法获取所需的报告，请提出问题.
  

• stop using GROUP BY, it is seriously hindering your understanding of SQL. If you can't get the report you want without using GROUP BY, ask a question.
  .

此发布的问题.让我知道您迷路了，此后，我将提供更多详细信息.

This posted question. Let me know at which point you got lost, and I will provide more detail from that point forward.

• 对于这个问题，您需要一个带有喜欢的公告列表；不喜欢和这个用户喜欢.那是对的吗 ?您是否尝试过我提供的代码?
  

• For this question, you want a list of bulletins, with likes; dislikes; and this users likes. Is that correct ? Have you tried the code I provided ?
  .

这篇关于Mysql嵌套选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！