MySQL queries Several SQL statements of the most expensive book dealers

Source: Internet
Author: User
Different books have different prices at different dealers. here we need to find out who is the highest distributor of each book? Find the lowest possible.

Different books have different prices at different dealers. here we need to find out who is the highest distributor of each book? Find the lowest possible.

Mysql> use test;
Database changed
Mysql> create table shop (
-> Article INT (4) unsigned zerofill default '100' not null,
-> Dealer CHAR (20) DEFAULT ''not null,
-> Price DOUBLE (16, 2) DEFAULT '0. 00' not null,
-> Primary key (article, dealer ));
Query OK, 0 rows affected (0.13 sec)

Mysql> insert into shop VALUES
-> (1, 'A', 3.45), (1, 'B', 3.99), (2, 'A', 10.99), (3, 'B ', 1.45 ),
-> (3, 'C', 1.69), (3, 'D', 1.25), (4, 'D', 19.95 );
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0

Mysql> select * from shop;
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 1, 0003 | C | 1.69 |
| 1, 0003 | D | 1.25 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
7 rows in set (0.06 sec)

Mysql> select article, max (price) from shop group by article
->;
+ --------- + ------------ +
| Article | max (price) |
+ --------- + ------------ +
| 0001/3.99 |
| 0002/10.99 |
| 0003/1.69 |
| 0004/19.95 |
+ --------- + ------------ +
4 rows in set (0.05 sec)

Mysql> select article, max (price), dealer from shop group by article;
+ --------- + ------------ + -------- +
| Article | max (price) | dealer |
+ --------- + ------------ + -------- +
| 0001 | 3.99 | A |
| 0002 | 10.99 | A |
| 1, 0003 | 1.69 | B |
| 0004 | 19.95 | D |
+ --------- + ------------ + -------- +
4 rows in set (0.00 sec)

Mysql> select article, dealer, price from shop s1
-> Where price = (select max (s2.price) from shop s2
-> Where s1.article = s2.article );
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 1, 0003 | C | 1.69 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
4 rows in set (0.01 sec)

Mysql> select s1.article, dealer, s1.price
-> From shop s1
-> Join (
-> Select article, max (price) as price from shop
-> Group by article) as s2
-> On s1.article = s2.article and s1.price = s2.price;
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 1, 0003 | C | 1.69 |
| 1, 0004 | D | 19.95 |
+ --------- + -------- + ------- +
4 rows in set (0.05 sec)

Mysql> select s1.article, s1.dealer, s1.price from shop s1
-> Left join shop s2 on s1.article = s2.article and s1.price select s1.article, s1.dealer, s1.price, s2. * from shop s1 left join shop s2
On s1.article = s2.article and s1.price

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.