Question

I m reading this article and I m trying to understand this SQL statement but I am still somewhat new to 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:

  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

Answer 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.

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.

The results are ordered by linea.

Answer 2

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

Answer 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.

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.

Answer 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

Answer 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.

Answer 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 ()

Answer 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).

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

友情链接