CSDN博客

img ccat

SQL Story摘录(二)————联接查询初探

发表于2002/6/10 9:37:00  1572人阅读

分类: SQL Story

例1-2、键值重复的信息
现在看一下压缩掉重复信息的PRODUCT表
ID PNAME PRICE NUMBER PDESCRIPTION
1Apple 123000NULL
2Banana 16.997600NULL
3Olive 25.224500NULL
4Coco Nut 40.992000NULL
4Orange 15.995500NULL
5Pineapple 302500NULL
6Olive 25.223000NULL

这里还有几个有问题的地方。表中Coco Nut和Orange的ID都是4,ID号为3和6的两种商品的品名(PNAME)都是Olive。而我们的原意显然是想要让每一种商品对应一个ID号,而且表中的ID号和PNAME都应该是唯一的。这个表中只有7行,我们可以直接观察,用肉眼发现问题,表中数据量很大时呢?
现在我们回顾一下例1中查询重复数据的语句。我们用
……
GROUP BY ID, PNAME, PRICE, NUMBER, PDESCRIPTION
对数据集进行了分组,并用
HAVING COUNT(*) > 1
过滤出了重复的数据,依此类推,单独对ID列进行分组和过滤,能否找出ID重复的数据呢?试一试:
SELECT ID
FROM PRODUCT
GROUP BY ID
HAVING COUNT(*) > 1
返回结果:
ID
-----------
4
这样倒是出现了我们所要的ID号,可这种报表实在没什么实际意义,现在我们查一下这个ID到底是谁:
SELECT ID, PNAME, PRICE, NUMBER, PDESCRIPTION
FROM PRODUCT
GROUP BY ID
HAVING COUNT(*) > 1
这条语句执行出错,很显然,ID号之后的四列既不在GROUP BY中,也是统计函数,它们不应该出现在这里。而这样的语句:
SELECT ID, PNAME, PRICE, NUMBER, PDESCRIPTION
FROM PRODUCT
GROUP BY ID, PNAME, PRICE, NUMBER, PDESCRIPTION
HAVING COUNT(*) > 1
返回的是一个空结果集:
ID PNAME PRICE NUMBER PDESCRIPTION
----------- -------------------- --------------------- ----------- ------------------------------------

 

(所影响的行数为 0 行)
很多朋友用子查询
SELECT ID, PNAME, PRICE, NUMBER, PDESCRIPTION
FROM PRODUCT
WHERE ID IN (
SELECT ID
FROM PRODUCT
GROUP BY ID
HAVING COUNT(*) > 1
)
来解决,我还见过一个用二级游标的例子(!?),那么没有更好的办法了吗?
我更喜欢以下这行语句:
SELECT L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
FROM PRODUCT L
JOIN PRODUCT R
ON L.ID = R.ID
GROUP BY L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
HAVING COUNT(*) > 1
返回结果如下:
ID PNAMEPRICE NUMBER PDESCRIPTION
4Coco Nut40.992000NULL
4Orange 15.995500NULL

使用联接查询,速度会比子查询快很多,因为不用每次用IN操作在子语句中的结果集中搜索数据。尤其当表中数据很多,返回的结果集也很大时,其差异是相当惊人的。如果在多处理器,多硬盘的服务器上运行,联接查询还可以充分利用并行运算来提高效率。1999年夏天,IBM公司的工程师们在兰州大学出席全国数据库技术会议时,向我们讲解了运用并行运算技术优化联接查询所带来的性能飚升。相比之下,子查询在这方面有点吃亏。有些强大的数据库引擎会在适当的时候将子查询转化为联接查询,或反之。但把真理掌握在我们自己手中,不是更好吗?
当然,子查询并不是一定比联接慢,有机会我也会演示一些子查询快于联接查询的例子,甚至有些子查询语句,用联接是很难实现的。理论来讲,联接查询会生成一个迪卡尔积,这个集合的大小是组成它的各个子集的乘积。这会带来空间上的巨大开销(实际我们所见的数据库系统没有一个真这么干的)。而子查询的情况比较复杂。由生成的结果集来分,有标量子查询和向量子查询,(标量子查询指返回一个简单数据的查询,这种子查询语句在MS SQL Server中可以直接做为外部查询语句的一列);由子查询与外部查询的关系来分,有相关子查询和非相关子查询(相关子查询的结果集取决于外部查询当前的数据行,非相关子查询反之)。通常相关子查询比较让人头痛,它需要反复执行子查询语句,若外部查询操作的数据集(不是返回的数据集)行数为n,子查询操作的数据集行数为m,那它的复杂度最大将是m的n次方!加上子查询数据集展开带来的巨大空间开销,会极大影响速度。上例中的子查询比较幸运,是一个无关的向量子查询,但即使如此,也要在运算中保存一个子结果集并对其反复操作,而且难以并行运算,结果是它的速度不会比联接查询快。这也就是MySQL在很长时间里一直不支持子查询的原因。在通常情况下,大数据集的操作,联接查询的性能总是优于子查询,所以我们应当充分掌握这一方法。
以例2中最终的联接查询为例,我们分析一下编写这种联接查询的思路。前面提到,在理论上,联接数据集时,会生成一个迪卡尔积。如果有一个表T的内容如下:
Word
----
a
b
那么执行“SELECT L.Word, R.Word FROM T AS L JOIN T AS R ON L.Word = R.Word”时,会先生成
L.WordR.Word
a a
a b
ba
bb

然后再执行“ON L.Word = R.Word”,将其过滤为
L.WordR.Word
a a
bb

在这里,我们就利用中间这个迪卡尔积做文章。如果PRODUCT表的ID列中数据的确是唯一的,那对它做自联接后,就应该像刚才看到的T表Word列一样,结果集中的ID也仍然保持唯一。现在我们执行这个语句试试:
SELECT L.ID, R.ID, L.PNAME, R.PNAME
FROM PRODUCT L
JOIN PRODUCT R
ON L.ID = R.ID
结果如下:
IDIDPNAMEPNAME
11AppleApple
22BananaBanana
33OliveOlive
44OrangeOrange
44Coco nutOrange
44OrangeCoco nut
44Coco nutCoco nut
55PineapplePineapple
66OliveOlive

注意到了吗?原本重复两次的ID号4,现在,重复了4次。这是由于Coco Nut和Orange两行ID号重复,迪卡尔积为其平方,无法为联接条件过滤。所以,我们对这个结果集按其中一个子集的ID字段和另一个子集的其它字段进行分组后,ID为4的数据被分为两组,每组两行,而正常数据每组仅为一行。就可以找出ID重复的数据,甚至我们还可以知道它重复了几次!请看下面的SQL语句:
SELECT L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION, COUNT(*) ROW_COUNT
FROM PRODUCT L
JOIN PRODUCT R
ON L.ID = R.ID
GROUP BY L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
HAVING COUNT(*) > 1
返回结果:
IDPNAMEPRICENUMBERPDESCRIPTIONROW_COUNT
4Coco nut40.992000NULL2
4Orange15.995500NULL2

(所影响的行数为 2 行)
这种结构上的可扩展性也同样是子查询所不及的,同时它也会带来一些有趣的附效应,有好有坏,这就要到后面的章节讨论了。
同样,用
SELECT R.ID, L.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION, COUNT(*) ROW_COUNT
FROM PRODUCT L
JOIN PRODUCT R
ON L.PNAME = R.PNAME
GROUP BY R.ID, L.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
HAVING COUNT(*) > 1
语句,就可以找出PNAME列重复的数据及其重复次数:
ID PNAME PRICE NUMBER PDESCRIPTION ROW_COUNT
3Olive 25.224500NULL 2
6Olive 25.223000NULL 2

(所影响的行数为 2 行)
经过上面的两个例子,我们可以看到,更深入地了解关系型数据库的运行机制,熟练运用简单查询和联接查询,可以有效地提高程序的性能及可维护性,降低代码复杂度。何乐而不为呢?
InterBase中没有Money数据类型,所以在InterBase中创建PRODUCT表时,记得把PRICE字段定义为其它的类型,这里我用的是NUMERIC(8,4)。另外,InterBase中还有一个问题,执行以下语句删除重复数据时:
DELETE FROM PRODUCT
WHERE ID IN (SELECT ID
FROM PRODUCT
GROUP BY ID, PNAME, PRICE, NUMBER, PDESCRIPTION
HAVING COUNT(*) > 1)
理论来讲,它应当把两行“Apple”全都删掉,MS SQL Server2000就是这样做的。可它只删掉了其中一行!执行后的表中数据如下:
IDPNAMEPRICENUMBERPDESCRIPTION
1Apple12.00003000NULL
2Banana16.99007600NULL

显然,它在删除了一行数据后,又重新查询了数据表并重新决定下一行要删除的数据。对于关系型数据库来说,这不是一个好事,既不严谨,也不优美。不过具体到这个语句,倒是一个好事,我们只用一条删除命令就完成了本应分几次操作的数据合并。InterBase中,还有类似的一些地方,并没有像MS SQL Server那样,实现真正的集合操作。在以后的例子中我会随时提到。在实际工作中大家也要注意。
我使用的InterBase 6.0.1是一个可以免费获得的开放源码的数据库,而MS SQL Server是微软的掌上明珠,MS SQL Server7的设计者曾获得1998年的图灵奖。我不得不承认这个轻巧快捷的InterBase是一个让人赞叹的好东西,它实现了诸如级联更新这样一些MS SQL Server直至2000版才加入的强大功能,当然它也有其不尽如人意之处。不过考虑其性价比,我们真的不能要求更多了。另外建议新手借此机会了解一下临时表的使用。这里不多占篇幅了。

0 0

相关博文

我的热门文章

img
取 消
img