3.6常用查询的例子

页面导航:首页 > 数据库 > Mysql > 3.6常用查询的例子

3.6常用查询的例子

来源: 作者: 时间:2016-02-02 08:56 【

这里有一些用MySQL解决常用问题的例子 在一些例子中,使用shop表存储某个商人(经销商)的每件物品(物品号)的价格。假定每个商人对每项物品有一个固定价格,那么(article,dealer)即为

这里有一些用MySQL解决常用问题的例子.

在一些例子中,使用shop表存储某个商人(经销商)的每件物品(物品号)的价格。假定每个商人对每项物品有一个固定价格,那么(article,dealer)即为该记录的主关键字。

启动命令行工具mysql并选择:

shell>  your-database-name

(在大多数MySQL中,你可以使用test数据库)。

你可以使用以下语句创建示例表:

CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
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);

执行语句后,表应包含以下内容:

SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+

3.6.1 列的最大值
“最大的物品号是多少?”

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+

3.6.2 拥有某列最大值的行

任务:找出最贵商品的编号,经销商和价格

用一个子查询很容易做到:

SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+

另一个解决方案是使用LEFT JOIN或者按照价格降序排序所有行,然后使用MySQL特定的LIMIT子句只取第一行:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

注意:如果有多个最贵的物品,假设每个都是19.95,那么LIMIT只会取其中的一个.

3.6.3 按组找出列的最大值
任务:找出每项物品的最高价格.

SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+

3.6.4 拥有某个字段的组间最大值的行

任务:对每项物品,找出最贵价格的经销商.

这个问题可用这样一个子查询来解决:

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 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+

上面的例子使用了一个相互关联的子查询,这种效率比较低(查看 Section 13.2.10.7,“Correlated Subqueries” ). 其他可能的解决方案在From子句中使用了一个不相关的子查询或LEFT JOIN.

不相关的子查询:

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;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

LEFT JOIN是基于s1.price达到最大值有效的,那时s2.price不会有更大的值并且s2那一行将为NULL. 查看 Section 13.2.9.2, “JOIN
Syntax”.

3.6.5 使用自定义变量

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<