mysql查詢練習

標籤：

mysql> #查詢每個欄目最貴的商品mysql> select goods_id,shop_price,cat_id from (select goods_id,shop_price,cat_id from goods order by shop_price desc) as temp group by cat_id;+----------+------------+--------+| goods_id | shop_price | cat_id |+----------+------------+--------+|       16 |     823.33 |      2 ||       22 |    5999.00 |      3 ||       18 |    2878.00 |      4 ||       23 |    3700.00 |      5 ||        7 |     100.00 |      8 ||        6 |      42.00 |     11 ||       25 |      48.00 |     13 ||       29 |      90.00 |     14 ||       27 |      95.00 |     15 |+----------+------------+--------+9 rows in set (0.02 sec)mysql> #查詢出每個欄目最低的商品mysql> select goods_id,shop_price,cat_id from(select goods_id,shop_price,cat_idfrom goods order by shop_price asc) as temp group by cat_id;+----------+------------+--------+| goods_id | shop_price | cat_id |+----------+------------+--------+|       16 |     823.33 |      2 ||       20 |     280.00 |      3 ||        1 |    1388.00 |      4 ||       23 |    3700.00 |      5 ||        4 |      58.00 |      8 ||        5 |      20.00 |     11 ||       26 |      19.00 |     13 ||       30 |      18.00 |     14 ||       28 |      45.00 |     15 |+----------+------------+--------+9 rows in set (0.00 sec)mysql> #查詢出每個欄目的平均價格mysql> select goods_id,cat_id,shop_price from goods where cat_id=2;+----------+--------+------------+| goods_id | cat_id | shop_price |+----------+--------+------------+|       16 |      2 |     823.33 |+----------+--------+------------+1 row in set (0.00 sec)mysql> select goods_id,cat_id,shop_price from goods where cat_id=3;+----------+--------+------------+| goods_id | cat_id | shop_price |+----------+--------+------------+|        8 |      3 |     399.00 ||        9 |      3 |    2298.00 ||       10 |      3 |    1328.00 ||       11 |      3 |    1300.00 ||       12 |      3 |     983.00 ||       13 |      3 |    1311.00 ||       15 |      3 |     788.00 ||       17 |      3 |    2300.00 ||       19 |      3 |     858.00 ||       20 |      3 |     280.00 ||       21 |      3 |    2000.00 ||       22 |      3 |    5999.00 ||       24 |      3 |    2000.00 ||       31 |      3 |    1337.00 ||       32 |      3 |    3010.00 |+----------+--------+------------+15 rows in set (0.00 sec)mysql> select cat_id,avg(shop_price) from goods where cat_id=3;+--------+-----------------+| cat_id | avg(shop_price) |+--------+-----------------+|      3 |     1746.066667 |+--------+-----------------+1 row in set (0.00 sec)mysql> select cat_id from goods group by cat_id;+--------+| cat_id |+--------+|      2 ||      3 ||      4 ||      5 ||      8 ||     11 ||     13 ||     14 ||     15 |+--------+9 rows in set (0.00 sec)mysql> select cat_id,avg(shop_price) from goods where cat_id in (    -> select cat_id from goods group by cat_id);+--------+-----------------+| cat_id | avg(shop_price) |+--------+-----------------+|      4 |     1232.526774 |+--------+-----------------+1 row in set (0.03 sec)mysql> select cat_id,sum(goods_number) from goods where cat_id=4;+--------+-------------------+| cat_id | sum(goods_number) |+--------+-------------------+|      4 |                 3 |+--------+-------------------+1 row in set (0.00 sec)mysql> select cat_id,sum(goods_number) from goods group by cat_id;+--------+-------------------+| cat_id | sum(goods_number) |+--------+-------------------+|      2 |                 0 ||      3 |               203 ||      4 |                 3 ||      5 |                 8 ||      8 |                61 ||     11 |                23 ||     13 |                 4 ||     14 |                 9 ||     15 |                 2 |+--------+-------------------+9 rows in set (0.00 sec)mysql> #取出每個欄目下的商品的平均價格mysql> select cat_id,avg(shop_price) as ‘平均價‘ from goods group by cat_id;+--------+-------------+| cat_id | 平均價           |+--------+-------------+|      2 |  823.330000 ||      3 | 1746.066667 ||      4 | 2297.000000 ||      5 | 3700.000000 ||      8 |   75.333333 ||     11 |   31.000000 ||     13 |   33.500000 ||     14 |   54.000000 ||     15 |   70.000000 |+--------+-------------+9 rows in set (0.03 sec)mysql> #取出每個欄目下的庫存量mysql> select cat_id,goods_number from goods group by cat_id;+--------+--------------+| cat_id | goods_number |+--------+--------------+|      2 |            0 ||      3 |            1 ||      4 |            1 ||      5 |            8 ||      8 |           17 ||     11 |            8 ||     13 |            2 ||     14 |            0 ||     15 |            2 |+--------+--------------+9 rows in set (0.00 sec)mysql> #查詢每個欄目下商品的種類mysql> select cat_id,count(*) as ‘商品種類‘ from goods cat_id;+--------+----------+| cat_id | 商品種類        |+--------+----------+|      4 |       31 |+--------+----------+1 row in set (0.00 sec)mysql> select cat_id,count(*) as ‘商品種類‘ from goods group by  cat_id;+--------+----------+| cat_id | 商品種類        |+--------+----------+|      2 |        1 ||      3 |       15 ||      4 |        3 ||      5 |        1 ||      8 |        3 ||     11 |        2 ||     13 |        2 ||     14 |        2 ||     15 |        2 |+--------+----------+9 rows in set (0.02 sec)mysql> #having和group綜合使用查詢mysql> #查詢該店的商品比市場價所省的價格mysql> select goods_id,shop_price,market_price-shop_price as ‘比市場省的錢‘ from goods;+----------+------------+--------------+| goods_id | shop_price | 比市場省的錢          |+----------+------------+--------------+|        1 |    1388.00 |       277.60 ||        4 |      58.00 |        11.60 ||        3 |      68.00 |        13.60 ||        5 |      20.00 |         4.00 ||        6 |      42.00 |         8.40 ||        7 |     100.00 |        20.00 ||        8 |     399.00 |        79.79 ||        9 |    2298.00 |       459.60 ||       10 |    1328.00 |       265.60 ||       11 |    1300.00 |     -1300.00 ||       12 |     983.00 |       196.60 ||       13 |    1311.00 |       262.20 ||       14 |    2625.00 |       525.00 ||       15 |     788.00 |       157.60 ||       16 |     823.33 |       164.67 ||       17 |    2300.00 |       460.00 ||       18 |    2878.00 |       575.60 ||       19 |     858.00 |       171.60 ||       20 |     280.00 |        56.00 ||       21 |    2000.00 |       400.00 ||       22 |    5999.00 |      1199.80 ||       23 |    3700.00 |       740.00 ||       24 |    2000.00 |       400.00 ||       25 |      48.00 |         9.59 ||       26 |      19.00 |         3.80 ||       27 |      95.00 |         5.00 ||       28 |      45.00 |         5.00 ||       29 |      90.00 |       -90.00 ||       30 |      18.00 |         3.00 ||       31 |    1337.00 |       267.39 ||       32 |    3010.00 |       602.00 |+----------+------------+--------------+31 rows in set (0.05 sec)mysql> #查詢每個商品所積壓的貨款mysql> select goods_id,goods_number*shop_price as ‘積壓的貨款‘ from goods;+----------+------------+| goods_id | 積壓的貨款         |+----------+------------+|        1 |    1388.00 ||        4 |     986.00 ||        3 |    1632.00 ||        5 |     160.00 ||        6 |     630.00 ||        7 |    2000.00 ||        8 |     399.00 ||        9 |    9192.00 ||       10 |    9296.00 ||       11 |    1300.00 ||       12 |    7864.00 ||       13 |   10488.00 ||       14 |    2625.00 ||       15 |    2364.00 ||       16 |       0.00 ||       17 |    2300.00 ||       18 |    2878.00 ||       19 |   10296.00 ||       20 |    3360.00 ||       21 |   80000.00 ||       22 |    5999.00 ||       23 |   29600.00 ||       24 |  200000.00 ||       25 |      96.00 ||       26 |      38.00 ||       27 |     190.00 ||       28 |       0.00 ||       29 |       0.00 ||       30 |     162.00 ||       31 |    1337.00 ||       32 |   12040.00 |+----------+------------+31 rows in set (0.01 sec)mysql> #查詢積壓的總貨款mysql> select sum(shop_price*goods_number) as ‘積壓的總貨款‘ from goods;+--------------+| 積壓的總貨款           |+--------------+|    398620.00 |+--------------+1 row in set (0.00 sec)mysql> #查詢每個欄目積壓的貨款mysql> select cat_id,sum(shop_price*goods_number) as ‘積壓的貨款‘ from goods group by cat_id;+--------+------------+| cat_id | 積壓的貨款         |+--------+------------+|      2 |       0.00 ||      3 |  356235.00 ||      4 |    6891.00 ||      5 |   29600.00 ||      8 |    4618.00 ||     11 |     790.00 ||     13 |     134.00 ||     14 |     162.00 ||     15 |     190.00 |+--------+------------+9 rows in set (0.00 sec)mysql> #查詢比市場價省錢200元以上的商品及該商品所省的錢mysql> select goods_id,market_price-shop_price as ‘sheng‘ from goods where market_price-shop_price>200;+----------+---------+| goods_id | sheng   |+----------+---------+|        1 |  277.60 ||        9 |  459.60 ||       10 |  265.60 ||       13 |  262.20 ||       14 |  525.00 ||       17 |  460.00 ||       18 |  575.60 ||       21 |  400.00 ||       22 | 1199.80 ||       23 |  740.00 ||       24 |  400.00 ||       31 |  267.39 ||       32 |  602.00 |+----------+---------+13 rows in set (0.12 sec)mysql> select goods_id,market_price-shop_price as ‘sheng‘ from goods having sheng>200;+----------+---------+| goods_id | sheng   |+----------+---------+|        1 |  277.60 ||        9 |  459.60 ||       10 |  265.60 ||       13 |  262.20 ||       14 |  525.00 ||       17 |  460.00 ||       18 |  575.60 ||       21 |  400.00 ||       22 | 1199.80 ||       23 |  740.00 ||       24 |  400.00 ||       31 |  267.39 ||       32 |  602.00 |+----------+---------+13 rows in set (0.00 sec)mysql> #查詢貨款大於2w的欄目，以及該欄目積壓的貨款mysql> select cat_id,sum(goods_number*shop_price) as huokuan from goods group by cat_id having huokuan>20000;+--------+-----------+| cat_id | huokuan   |+--------+-----------+|      3 | 356235.00 ||      5 |  29600.00 |+--------+-----------+2 rows in set (0.01 sec)mysql>

 

mysql查詢練習