Understanding Subqueries for SQL

<title>Sub-query</title> Sub-query Table of Contents

• Summarize:
• Related reading

Source: Open class on the Internet

Data preparation

Create Table Goods(goods_id mediumint (8) unsignedPrimary KeyAuto_increment, Goods_namevarchar(120) not NULL default "', cat_idsmallint(5) unsigned not NULL default ' 0 ', brand_idsmallint(5) unsigned not NULL default ' 0 ', GOODS_SNChar(15) not NULL default "', Goods_numbersmallint(5) unsigned not NULL default ' 0 ', Shop_pricedecimal(10,2) unsigned not NULL default ' 0.00 ', Market_pricedecimal(10,2) unsigned not NULL default ' 0.00 ', Click_countint(Ten) unsigned not NULL default ' 0 ') Engine=myisamdefaultCharset=utf8;Insert  into' Goods 'Values(1,' kd876 ', 4, 8,' ecs000000 ', 1,1388.00,1665.60,9), (4,' Nokia n85 original charger ', 8, 1,' ecs000004 ', 17,58.00,69.60,0), (3,' Nokia original 5800 headset ', 8, 1,' ecs000002 ', 24,68.00,81.60,3), (5,' Sony ericsson original m2 card reader ', 11, 7,' ecs000005 ', 8,20.00,24.00,3), (6,' Sheng Chuang Kingmax memory card ', 11, 0,' ecs000006 ', 15,42.00,50.40,0), (7,' Nokia N85 original stereo headset hs-82 ', 8, 1,' ecs000007 ', 20,100.00,120.00,0), (8,' Philips [email protected] ', 3, 4,' ecs000008 ', 1,399.00,478.79,10), (9,' Nokia E66 ', 3, 1,' ecs000009 ', 4,2298.00,2757.60,20), (10,' Sony Ericsson C702c ', 3, 7,' ecs000010 ', 7,1328.00,1593.60,11), (11,' Sony Ericsson C702c ', 3, 7,' ecs000011 ', 1,1300.00,0.00,0), (12,' Motorola a810 ', 3, 2,' ecs000012 ', 8,983.00,1179.60,13), (13,' nokia 5320 XpressMusic ', 3, 1,' ecs000013 ', 8,1311.00,1573.20,13), (14,' Nokia 5800xm ', 4, 1,' ecs000014 ', 1,2625.00,3150.00,6), (15,' Motorola a810 ', 3, 2,' ecs000015 ', 3,788.00,945.60,8), (16,' Henderson Albert G101 ', 2, 11,' ecs000016 ', 0,823.33,988.00,3), (17,' Amoi N7 ', 3, 5,' ecs000017 ', 1,2300.00,2760.00,2), (18,' Amoi T5 ', 4, 5,' ecs000018 ', 1,2878.00,3453.60,0), (19,' Samsung sgh-f258 ', 3, 6,' ecs000019 ', 12,858.00,1029.60,7), (20,' Samsung BC01 ', 3, 6,' ecs000020 ', 12,280.00,336.00,14), (21,' Kim Li A30 ', 3, 10,' ecs000021 ', 40,2000.00,2400.00,4), (22,' multi-reach Touch HD ', 3, 3,' ecs000022 ', 1,5999.00,7198.80,16), (23,' Nokia N96 ', 5, 1,' ecs000023 ', 8,3700.00,4440.00,17), (24,' p806 ', 3, 9,' ecs000024 ', 100,2000.00,2400.00,35), (25,' PHS/fixed $50 Prepaid card ', 13, 0,' ecs000025 ', 2,48.00,57.59,0), (26,' PHS/fixed $20 prepaid card ', 13, 0,' ecs000026 ', 2,19.00,22.80,0), (27,' Unicom 100 yuan Prepaid card ', 15, 0,' ecs000027 ', 2,95.00,100.00,0), (28,' Unicom 50 yuan Prepaid card ', 15, 0,' ecs000028 ', 0,45.00,50.00,0), (29,' Mobile $100 Prepaid card ', 14, 0,' ecs000029 ', 0,90.00,0.00,0), (30,' Mobile $20 prepaid card ', 14, 0,' ecs000030 ', 9,18.00,21.00,1), (31,' Motorola E8 ', 3, 2,' ecs000031 ', 1,1337.00,1604.39,5), (32,' Nokia N85 ', 3, 1,' ecs000032 ', 4,3010.00,3612.00,9);Create Table category(cat_idsmallintunsigned auto_incrementPrimary Key, Cat_namevarchar(90) not NULL default "', parent_idsmallintunsigned) engine MyISAM charset UTF8;INSERT  into' Category 'VALUES(1,' phone type ', 0), (2,' CDMA phones ', 1), (3,' GSM phone ', 1), (4,' 3G mobile ', 1), (5,' dual-mode phone ', 1), (6,' mobile phone accessories ', 0), (7,' Charger ', 6), (8,' Headphones ', 6), (9,' Battery ', 6), (11,' Reader and memory card ', 6), (12,' Prepaid card ', 0), (13,' PHS/Landline prepaid card ', 12), (14,' mobile phone recharge card ', 12), (15,' Unicom Mobile prepaid card ', 12);

Check out the latest line of goods (with the largest item number up to date, implemented with subqueries)

SELECT * from goods where goods_id = (select Max (goods_id) from goods) \gselect * from goods ORDER BY goods_id DESC LIMIT 1 \g

Query the column name of the item with number 19 (use left JOIN to query and sub-query respectively)

Select category.* from category left join goods using (cat_id) where goods.goods_id = 19;select category.* from category W Here cat_id = (select cat_id from goods where goods_id = 19);

Use the where sub-query to remove the latest items from each column in the goods table.

Select goods.* from  goods where goods_id on (select Max (goods_id) from goods Group by cat_id) \g

Use the From sub-query to remove the latest items from each column in the goods table.

SELECT * FROM (select goods_id, cat_id, goods_name to goods order by goods_id DESC) as TMP GROUP by CAT_ID;  --The correct answer in class

Use the Exists Type sub-query, find out all the items have a product column

SELECT * from category where exists (select * from goods where goods.cat_id=category.cat_id); select * from category where Category.id in (select goods.cat_id from goods);

Summarize:

Subquery has where, on, from 3, corresponding query results are one row, one column, and multi-row multi-column
The From-type subquery needs to rename the queried data to another table

Related reading

• Multi-table operation
  
• Join operation
  
• Grouping, aggregation
  

Understanding Subqueries for SQL