The having statement of MySQL

mysql> use Qq;database changedmysql> #查询本店价比市场价省的钱, and ask to save more than 200 yuan to remove to mysql> select Goods_id,market_price,shop _price, (Market_price-shop_price) as discount from goods where has discount>200; Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' have discount>200 ' a T line 1mysql> Select Goods_id,market_price,shop_price, (market_price-shop_price) as discount from goods have discount>200;+----------+--------------+------------+----------+| goods_id | Market_price | Shop_price |        Discount |+----------+--------------+------------+----------+|      1 |    1665.60 |   1388.00 |        277.60 | |      9 |    2757.60 |   2298.00 |       459.60 | |      10 |    1593.60 |   1328.00 |       265.60 | |      13 |    1573.20 |   1311.00 |       262.20 | |      14 |    3150.00 |   2625.00 |       525.00 | |      17 |    2760.00 |   2300.00 |       460.00 | |      18 |   3453.60 | 2878.00 |       575.60 | |      21 |    2400.00 |   2000.00 |       400.00 | |      22 |    7198.80 |  5999.00 |       1199.80 | |      23 |    4440.00 |   3700.00 |       740.00 | |      24 |    2400.00 |   2000.00 |       400.00 | |      31 |    1604.39 |   1337.00 |       267.39 | |      32 |    3612.00 |   3010.00 | 602.00 |+----------+--------------+------------+----------+13 rows in Set (0.01 sec) mysql> # Where and having cannot appear simultaneously mysql> #查询出每种商品所积压的货款mysql > select Goods_id,shop_price,goods_number,shop_price*goods_ Number as JY from goods;+----------+------------+--------------+-----------+| goods_id | Shop_price | Goods_number |        JY |+----------+------------+--------------+-----------+|    1 |            1388.00 |   1 |        1388.00 | |      4 |           58.00 |    17 |        986.00 | |      3 |           68.00 |   24 |        1632.00 | |      5 |            20.00 |    8 |        160.00 | |      6 |           42.00 |    15 |        630.00 | |     7 |           100.00 | 20 |        2000.00 | |     8 |            399.00 |    1 |        399.00 | |    9 |            2298.00 |   4 |       9192.00 | |    10 |            1328.00 |   7 |       9296.00 | |    11 |            1300.00 |   1 |       1300.00 | |     12 |            983.00 |   8 |       7864.00 | |    13 |            1311.00 |  8 |       10488.00 | |    14 |            2625.00 |   1 |       2625.00 | |     15 |            788.00 |   3 |       2364.00 | |     16 |            823.33 |      0 |       0.00 | |    17 |            2300.00 |   1 |       2300.00 | |    18 |            2878.00 |   1 |       2878.00 | |     19 |           858.00 |  12 |       10296.00 | |     20 |           280.00 |   12 |       3360.00 | |    21 |           2000.00 |  40 |       80000.00 | |    22 |            5999.00 |   1 |       5999.00 | |    23 |            3700.00 |  8 |       29600.00 | |    24 |          2000.00 | 100 |       200000.00 | |      25 |            48.00 |     2 |       96.00 | |      26 |            19.00 |     2 | 38.00 | |      27 |            95.00 |    2 |       190.00 | |      28 |            45.00 |      0 |       0.00 | |      29 |            90.00 |      0 |       0.00 | |      30 |            18.00 |    9 |       162.00 | |    31 |            1337.00 |   1 |       1337.00 | |    32 |            3010.00 |  4 | 12040.00 |+----------+------------+--------------+-----------+31 rows in Set (0.00 sec) Mysql> #查询该店积压的总货款, The sum of the backlog for each commodity mysql> select SUM (goods_number*shop_price) from goods;+------------------------------+|                    SUM (goods_number*shop_price) |+------------------------------+| 398620.00 |+------------------------------+1 row in Set (0.00 sec) mysql> #查询每个栏目下的积压货款mysql > select Cat_id,sum ( Shop_price*goods_number) from goods Group by cat_id;+--------+------------------------------+| cat_id |      SUM (shop_price*goods_number) |+--------+------------------------------+|                         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> #查询出栏目积压货款, and filter out backlog of payments > 20000 columns mysql> Select Cat_id,sum (goods_number*shop_price) as jhk, from goods group by cat_id, having JH k>20000;+--------+-----------+| cat_id |      JHK |+--------+-----------+| 3 |      356235.00 | |  5 | 29600.00 |+--------+-----------+2 rows in Set (0.00 sec) mysql> CREATE table result (name varchar) Default n ull, subject varchar (+) default NULL, score tinyint (4) default null--engine MyISAM charset UTF 8;    Query OK, 0 rows affected (0.08 sec) mysql> insert into result{-\cmysql> insert into result, values --(' Zhang San ', ' mathematics ', '), (' Zhang San ',' Language ', ' (' Zhang San ', ' geography ', ' + '); ERROR 1366 (HY000): Incorrect string value: ' \xd5\xc5\xc8\xfd ' for column ' name ' at row 1mysql> set names gnk; ERROR 1115 (42000): Unknown character set: ' Gnk ' mysql> set names GBK; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into result, values---(' Zhang San ', ' math ', ', '), (' Zhang San ', ' Language ', ', ' (' Zhang San ', ' geography ', 40);    Query OK, 3 rows affected (0.02 sec) Records:3 duplicates:0 warnings:0mysql> INSERT into result, values  (', ', ', '),---(' Zhang San ', ' math ', ' + '),\cmysql> insert into result, values--(' John Doe ', ' language ', "), (' John Doe ', ' politics ', ' a '),--(' Harry ', ' politics ', 30); Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0mysql> SELECT * from result;+------+--------- +-------+| name | Subject | Score |+------+---------+-------+| Zhang San |    Math | 90 | | Zhang San |    language | 50 | | Zhang San |    Geography | 40 | | John Doe |    language | 55 | | John Doe |    Politics | 45|| Harry |    Politics | |+------+---------+-------+6 rows in Set (0.03 sec) mysql> #查询2门及2门以上不及格者的平均成绩mysql > select Name,avg (Subject) Form result group by name have subject<60; Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' result group by name Havi Ng subject<60 ' at line 1mysql> select Name,avg (subject) Form result group by name have score<60; Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' result group by name Havi Ng score<60 ' at line 1mysql> select Name,avg (score) Form result group by name where score<60; Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' result group by name Wher E score<60 ' at line 1mysql> Select NAME,SCORE,SCORE&LT;60 from result GROUP by name;+------+-------+----------+| name | Score | score<60 |+------+-------+----------+|    Zhang San |        90 | 0 | |    John Doe |        55 | 1 | |    Harry |        30 | 1 |+------+-------+----------+3 rows in Set (0.02 sec) mysql> Select name,score,score<60 from result;+------+------ -+----------+| name | Score | score<60 |+------+-------+----------+|    Zhang San |        90 | 0 | |    Zhang San |        50 | 1 | |    Zhang San |        40 | 1 | |    John Doe |        55 | 1 | |    John Doe |        45 | 1 | |    Harry |        30 | 1 |+------+-------+----------+6 rows in Set (0.00 sec) mysql> Select Name,score,sum (score<60) from result group by NA me;+------+-------+---------------+| name | Score | SUM (score<60) |+------+-------+---------------+|    Zhang San |             90 | 2 | |    John Doe |             55 | 2 | |    Harry |             30 | 1 |+------+-------+---------------+3 rows in Set (0.00 sec) mysql> Select Name,score,sum (score<60) as-G from ResUlt GROUP BY name has g>=2;+------+-------+------+| name | Score | G |+------+-------+------+|    Zhang San |    90 | 2 | |    John Doe |    55 | 2 |+------+-------+------+2 rows in Set (0.00 sec) mysql> Select Name,avg (score) from result group by name have sum (SC ORE&LT;60) >=2;+------+------------+| name | AVG (Score) |+------+------------+|    Zhang San | 60.0000 | |    John Doe | 50.0000 |+------+------------+2 rows in Set (0.00 sec) mysql> Select Name,avg (score) from result group by name where sum (score<60) >=2; Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' where sum (score<60) ; =2 ' at line 1mysql> select Name,avg (score) from result group by name have sum (score<60) >=2;+------+----------- -+| name | AVG (Score) |+------+------------+|    Zhang San | 60.0000 | |    John Doe | 50.0000 |+------+------------+2 rows in Set (0.00 sec) mysql> Select Name,avg (Score),SUM (score<60) as G from result group by name have g>=2;+------+------------+------+| name | AVG (Score) | G |+------+------------+------+|    Zhang San |    60.0000 | 2 | |    John Doe |    50.0000 | 2 |+------+------------+------+2 rows in Set (0.00 sec) mysql> #首先将平均数求出, and then screened out two subjects and more than two subjects, screening with score<60, whether pass with 0, 1, then sum is the number of >=2, filter out the results mysql> #找出每个栏目下id号最大的商品mysql > select goods_id,cat_id,goods_name from goods group by cat_id ORDER BY goods_id desc limit 1;+----------+--------+-----------------+| goods_id | cat_id |       Goods_name |+----------+--------+-----------------+|     29 | 14 | Mobile $100 Prepaid Card |+----------+--------+-----------------+1 row in Set (0.03 sec) mysql> Select Goods_id,cat_id,goods_n Ame from goods GROUP by CAT_ID have goods_id order by goods_id desc limit 1;+----------+--------+-----------------+| goods_id | cat_id |       Goods_name |+----------+--------+-----------------+|     29 | 14 | Mobile $100 Prepaid Card |+----------+--------+-----------------+1 Row in Set (0.00 sec) mysql> Select Goods_id,cat_id,goods_name from goods group by CAT_ID have goods_i D ORDER by goods_id desc;+----------+--------+-----------------------+| goods_id | cat_id |       Goods_name |+----------+--------+-----------------------+|     29 | 14 |       Mobile $100 Prepaid Card | |     27 | 15 |       China Unicom $100 Prepaid Card | |     25 | 13 |       PHS/fixed $50 Prepaid Card | |      23 | 5 |       Nokia N96 | |      16 | 2 |        Henderson Albert G101 | |      8 | 3 |        Philips [Email protected] | |     5 | 11 |        Sony Ericsson original M2 card Reader | |      4 | 8 |        Nokia N85 Original Charger | |      1 | 4 | KD876 |+----------+--------+-----------------------+9 rows in Set (0.00 sec) mysql> Select Goods_id,cat_ Id,goods_name from goods GROUP by CAT_ID have Max (goods_id), +----------+--------+-----------------------+| goods_id | cat_id | Goods_name |+----------+--------+-----------------------+|      16 | 2 |        Henderson Albert G101 | |      8 | 3 |        Philips [Email protected] | |      1 | 4 |       KD876 | |      23 | 5 |        Nokia N96 | |      4 | 8 |        Nokia N85 Original Charger | |     5 | 11 |       Sony Ericsson original M2 card Reader | |     25 | 13 |       PHS/fixed $50 Prepaid Card | |     29 | 14 |       Mobile $100 Prepaid Card | |     27 | 15 | Unicom 100 yuan Prepaid card |+----------+--------+-----------------------+9 rows in Set (0.00 sec) mysql> Select Goods_id,c At_id,goods_name from goods group by cat_id where cat_id have Max (goods_id); Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' where cat_id have Max (g  oods_id) ' at line 1mysql> select goods_name,goods_id,cat_id from (select goods_name,cat_id,goods_id from Goods ORDER by goods_id desc) as temp GROUP BY cat_id;+------------------------------+----------+--------+| Goods_name | goods_id | cat_id |+------------------------------+----------+--------+|       Henderson Albert G101 |      16 | 2 | |       Nokia N85 |      32 | 3 | |       Amoi T5 |      18 | 4 | |       Nokia N96 |      23 | 5 | |        Nokia N85 Original stereo Headset HS-82 |      7 | 8 | |        Sheng Chuang Kingmax Memory Card |     6 | 11 | |       PHS/fixed $20 Prepaid Card |     26 | 13 | |       Mobile $20 Prepaid Card |     30 | 14 | |       China Unicom $50 Prepaid Card |     28 | |+------------------------------+----------+--------+9 rows in Set (0.03 sec) mysql> #必须要用到子查询

The having statement of MySQL