php MySQL query statement is written as: [select * from goods where goods_id in (select max(goods_id) from goods group by cat_id);].
#The so-called subquery statement is to first query a result through a statement, and then query again from the result through a query statement. There are generally three types of subquery statements: Let’s use a case to explain.
(Video tutorial recommendation: php video tutorial)
Case: Query the latest product information under each category in [Example 1].
If you query through a single sentence, you cannot get it. The following is wrong:
select max(goods_id),cat_id,goods_name from goods group by cat_id;
In this way, the latest product ID number goods_id of each category is obtained, but other information such as goods_name is the old one. Therefore, if you want to obtain the latest product information for each category, you must either query separately or use a subquery.
1. Where subquery statement.
select * from goods where goods_id in ( select max(goods_id) from goods group by cat_id);
In this code, we first use group by to query the latest product ID of each category, and then use the where...in () conditional statement to query the product information of the latest product ID of each category. . Here is the group by statement as a subquery statement of where.
2. from subquery statement.
select * from (select * from goods order by cat_id,goods_id desc) as gk group by cat_id;
From subquery is to use the results of the select query as a table, such as the bold code above. Note: The result set queried by select must be given an alias, such as the red part in the above code. Otherwise, an error will be reported.
In this way, we also query the latest product information under each category.
3. exists subquery statement:
Title: Query [Example 1] which columns have products. Category table name: category
select * from category where exists( select * from goods where goods.cat_id=category.cat_id )
The exists in the previous sentence is checked (when the category ID in the product table = the category ID in the category table), is there any product? If there is, then query the information of this category. If not, exclude this category. The final result is that the product classification information of the product is queried.
Well, here we introduce three commonly used mysql database subquery statements: where subquery statement, from subquery statement, and exists subquery statement. Of course, if you don't want to use subquery statements, you can also split them into two statements for execution, and the effect will be the same.
Related recommendations: php training
The above is the detailed content of How to write php mysql query statement. For more information, please follow other related articles on the PHP Chinese website!