MSYQL Database subquery __ Database

Sub-query:

(In the following example, CAT_ID says the column, goods_name the name of the commodity, shop_price the commodity price)

One, where subquery: The comparison of the results of the inner query as the outer-level query

Cases:

1, selectgoods_id,goods_name from goods where goods_id = (select Max (goods_id) fromgoods);

2, find out each column under the latest products (to goods_id Max for the latest)

Select Goods_id,cat_id,goods_name fromgoods where goods_id in (select Max (goods_id) to goods group by cat_id);

This sentence is equivalent to the first by CAT_ID group to find out the latest in each column of all good_id

And then check out the latest items in each column.

3, find out the most expensive items in each column

Select Goods_id,cat_id,goods_name,shop_pricefrom goods where Shop_price in (select Max (shop_price) from goods group Bycat _ID);

This sentence is equivalent to the first CAT_ID group to find out each column the most expensive all prices,

And then check out the items in each column.

Two, from-type subquery: The internal query results as a temporary table for the outer SQL query again

Use from to query the latest items in each column (max ID is up to date)

Cases:

SELECT * FROM (select Goods_id,cat_id,goods_namefrom goods ORDER by CAT_ID asc,goods_id DESC) as TMP GROUP by CAT_ID;

Inner-layer query: Selectgoods_id,cat_id,goods_name from goods to cat_id asc,goods_id desc;

The results are as follows:

The overall query results are as follows:

Third, exists subquery: The outer layer of the query results to the inner layers, to see if the query is set up

Cases:

Select Cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);

The outer query results are put into the inner layer using

Comments:

Five seed sentences are ordered: Where,group,having,order By,limit in this order can not be reversed

Columns are understood as variables to compute the Select Goods_id,market_price-shop_price from goods where market_price-shop_price>200;


The removed structure can be interpreted as a temporary table.