有人可以向我解释这个SQL查询吗？

I'm reading this article and I'm trying to understand this SQL statement but I am still somewhat new to SQL.

我正在阅读这篇文章,我正在尝试理解这个SQL语句,但我仍然对SQL有些新意。

I'm not sure what comment and c refer to.
I think one of them is the table name but I am not sure of the other. Also, apparently there is a subquery within it which I have not had any experience with:

我不确定评论和c是什么意思。我认为其中一个是表名,但我不确定另一个。此外,显然其中有一个子查询,我没有任何经验:

  SELECT c.id, c.user_id, c.body, c.deep, c.lineage, c.parent_id,
         (SELECT COUNT(*) 
            FROM comment 
           WHERE comment.lineage LIKE (CONCAT(c.lineage,'%')) 
             AND comment.lineage != c.lineage) AS replies
    FROM comment as c
ORDER BY c.lineage

7 个解决方案

#1

SELECT c.id,
       c.user_id,
       c.body, 
       c.deep, 
       c.lineage, 
       c.parent_id, (
       SELECT COUNT(*)
         FROM comment
        where comment.lineage LIKE (CONCAT(c.lineage,'%'))
          AND comment.lineage!=c.lineage)
       as replies
       FROM comment as c 
       order by c.linea

The first list are all the fields to be selected, with the prefix of c which is the alias later to the comment table.

第一个列表是要选择的所有字段,前缀为c,后面是注释表的别名。

The query in a query is a subquery, which runs that query which does a like and concatenates .clineage with % (which is the wildcard). This subquery result is saved in replies.

查询中的查询是一个子查询,它运行该查询,它执行类似的操作并将.clineage与%连接(这是通配符)。此子查询结果保存在回复中。

The results are ordered by linea.

结果按linea排序。

#2

c is an alias for a table named comment defined with comment as c.

c是名为comment的表的别名,注释为c。

#3

comment is indeed the name of a table in this query. c is an alias used for that table (in the syntax comment as c) so that elsewhere in the query the comment table can be referenced with simply a c instead of the entire table name.

comment确实是此查询中表的名称。 c是用于该表的别名(在语法注释中为c),因此在查询的其他位置可以使用c而不是整个表名来引用注释表。

In this particular case, where the sub-query is also querying from the same table, the alias allows it to reference that same table from the parent query. This is useful here because, in the context of the sub-query, c.lineage is a static value (per row returned from the parent query) which is used to filter rows in the sub-query (with comment.lineage). The sub-query can then return a single value per row of the parent query, and that value is aliased to the name replies in the result.

在这种特殊情况下,子查询也在同一个表中查询,别名允许它从父查询引用同一个表。这在这里很有用,因为在子查询的上下文中,c.lineage是一个静态值(从父查询返回的每行),用于过滤子查询中的行(带有comment.lineage)。然后,子查询可以返回父查询的每一行的单个值,并且该值是结果中名称回复的别名。

#4

"comment" is the table name, and "c" is just an alias for it to save typing. The query gets a list of comments from the comments table. It returns a number of columns specified by c.id, c.user_id, c.body, c.deep, c.lineage, c.parent_id, as well as the number of replies to this comment, as specified by (SELECT COUNT(*) FROM comment where comment.lineage LIKE (CONCAT(c.lineage,'%')) AND comment.lineage!=c.lineage) as replies

“comment”是表名,“c”只是保存输入的别名。该查询从comments表中获取注释列表。它返回由c.id,c.user_id,c.body,c.deep,c.lineage,c.parent_id指定的列数,以及此注释的回复数,由(SELECT COUNT( *)FROM comment其中comment.lineage LIKE(CONCAT(c.lineage,'%'))和comment.lineage!= c.lineage)作为回复

#5

Comment is a table and c is an alias for the last comment table reference. So, c.id refers to the id column in the last instance of the comment table.

Comment是一个表,c是最后一个注释表引用的别名。因此,c.id引用注释表的最后一个实例中的id列。

#6

You were very close in what you thought. comment is the table name, and so is c. See the line that says FROM comment as c' that is labeling the comment as c. The subquery is everything inside those outer ()

你的想法非常接近。 comment是表名,c也是。请参阅将注释标记为c'的行,将注释标记为c。子查询是那些外部的所有内容()

#7

The as keyword creates an alias for something so that you can refer to it later unambiguously. So, comment refers to the table and c is an alias for that same table. This is especially useful since you're referring to comment in two different contexts (in both the main query and the subquery).

as关键字为某些内容创建别名,以便您以后可以明确地引用它。因此,comment指的是表,c是同一个表的别名。这特别有用,因为您在两个不同的上下文中引用注释(在主查询和子查询中)。

It also allows you to assign the name replies to the result of your subquery:

它还允许您为子查询的结果指定名称回复:

(SELECT COUNT(*) 
   FROM comment 
  WHERE comment.lineage LIKE (CONCAT(c.lineage,'%')) 
    AND comment.lineage!=c.lineage) as replies