flyonok

统计

留言簿(8)

ACE

book

boost

bsd

c study

c++

code download

codeblock

computer clound

Eclipse

embed system

erlang

ET++

gtk

ic card

java

KDE

libevent

linux

linux--MM

mysql

network education

one card

oracle

pcap relation

php

powerbuilder

python

QT

software config

software test

SQL server

UML

wireless

wxwidgets

陈宾

阅读排行榜

评论排行榜

left join现象之一,出现null

在一次MySQL练习中,我通过如下sql语句创建数据库表shop及插入相关语句:
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),(5,'xiaowy',20.4);
然后执行下列语句:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
出现如下结果:
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0005 | xiaowy | 20.40 |
+---------+--------+-------+
发现会出现s2中的article,dealer,price有NULL值
执行如下语句:
select s1.article,s1.dealer,s1.price from shop s1 right join shop s2 on s1.price < s2.price where s1.article is null;
出现如下结果:
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    NULL | NULL   |  NULL |
+---------+--------+-------+
总有一行空值
执行如下语句:
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 |
|    0005 | xiaowy | 20.40 |
+---------+--------+-------+
这里不知如何解释
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;
 

posted on 2010-11-26 13:36 flyonok 阅读(929) 评论(0)  编辑 收藏 引用 所属分类: mysql


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理