The having statement of MySQL

Source: Internet
Author: User
Tags utf 8

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.