那些可能让你脑洞大开的查询

roych

在谈查询之前，先给大家出一道题目。以表A为数据源，建立一个日期字段条件为今年全年的查询。很简单是吧？

select * from 表A where year(日期)=2015;

上面这种写法的有木有？这并不能说错。不过，如果日期字段设置了索引的话，这就不能加以利用了。——索引的作用，类似于目录。——从目录页码里找需要的内容快，还是一页页翻看快呢？我想，这是不言自明的吧。所以，更有效率的写法应该是这样：

select * from 表A where 日期 between #2015/1/1# and #2015/12/31#;

一般来说，在创建条件子句时，应考虑以下两个通用规则：

• 避免使用NOT
• 将字段放在运算符左边
  

为什么说第一个语句效率低，看出来了吗？——运算符左边的并不是字段“日期”，而是参与预算的“year(日期）”，不符合第二条规则。掌握这条之后，把条件改成：查询最近七天的数据。相信大家应该知道怎么写了吧？

写成这样的该打PP：select * from 表A where dateDiff("d",日期,Date())<=7

除了上面提到的between之外，还有以下运算符都是查询效率较高的：=、>、<、>=、<=，like+通配符，例如，like "*Roych*"。

而对应的低效率子句是这些：<>（不等于）、in、like+其它条件（例如，like "Roych"效率低于="Roych"）和not（not不完全是这样，这在后面说）

roych

说到not in，可能会扯出一段陈年往事来。几年前，一个版友提出一个问题：为什么执行左联接删除查询时，总是弹出以下对话框：

在那之前我刚好也遇到过这个问题，后来用子查询完成的，于是便贴上这样的语句：

DELETE * FROM 用户动作 WHERE 节点地址 Not In (select 节点地址 from [设备数据]);

正想躲在角落里嘚瑟时，todaynew提出这查询效率较低，应改为域函数查询，例如：

DELETE * FROM 用户动作 WHERE DCount("节点地址","设备总表","节点地址='" & [节点地址] & "'")=0;

对比低效率规则，我们的语句各自符合一条。当然，根据前面的介绍，他的语句要好一些，——因为“=”的效率要比“in”高。

为什么说in的效率低呢，那是因为用in的话，每条记录都要去子查询里检索一番，比较的次数相当于两个数据源总记录条数的乘积。而用Dcount效率相对高一些，是因为这相当于进行一次域函数运算，然后再查询。当然，数据量特别多的时候，弊端也是相当明显的。

我们不妨做一个大胆的假想：计算语句效率要比非计算语句的高。那么如果还用not，能不能实现效率高一点的查询呢？

答案是可以的。这就要说说神一样存在着的谓词exists了。它低调得让人感觉不到它的存在，——Access内置函数是找不到的，以至于我一直以为它只存在于另一时空——SQL Server之中。和in不同的是，它返回一个布尔值。

按设想，not exists的效率通常会比not in高。当然，各种情况下并不完全一致，最好还是测试下。

DELETE * FROM 用户动作 WHERE not Exists (select * from 设备数据 where 用户动作.节点地址=设备数据.节点地址);

又到提问时间了。今天的问题是：还有没有比这些更高效的查询呢？

——有，那就是前面版友提出的左联接查询。——这也是todaynew后面给出的语句。

——打住！前面不是报错了吗？

是的。那为什么会报错呢？我们先来看看这两个表的关联字段。左表的节点地址是多个重复值，右表的也是多个重复值。这样建立起来的关系是什么样的关系？多对多？——如果你这么说，我很怀疑你的access可能是语文老师教的，——多对多的关系是由至少两组一对多关系传递而生成。

所以，说到底，还是表设计的问题。——不懂的又可以去看浅谈范式了。我们这里先抛开这不谈，回到问题上。根据我们前面的剖析，很容易找到解决办法。

先把父表（用户动作）复制一份出来，将节点地址设置为索引（有重复），保存为“用户动作1”。然后把子查询（设备数据）的节点地址复制出来，新建的关系当然不能再像前面那样混乱了，为此，我们只能把唯一值复制出来，于是便有“设备总表”，最后将设备总表的节点地址设置为主键，便可以建立好这个一对多的关系了。至此，我们有：

DELETE 用户动作1.* FROM 用户动作1 LEFT JOIN 设备总表 ON 用户动作1.节点地址 = 设备总表.节点地址 WHERE 设备总表.节点地址 Is Null;

roych

现在我们来看看另一个问题：往设备总表2中插入，设备总表1中有而设备总表2没有的记录，并更新日期为昨天。我们通常的做法是使用insert语句，同时将指定字段设置为某个值，例如：

1. INSERT INTO 设备总表1 ( 销售日, 节点地址 ) SELECT Date()-1 AS 销售日, 设备总表2.节点地址 FROM 设备总表2 LEFT JOIN 设备总表1 ON 设备总表2.节点地址 = 设备总表1.节点地址 WHERE 设备总表1.节点地址 Is Null;

复制代码

由此完成追加和更新的操作。不过，大家有没有想过另一种做法呢？例如，用更新查询来完成上述操作？这就是让人脑洞大开的upsert查询了：

1. UPDATE 设备总表2 LEFT JOIN 设备总表1 ON 设备总表2.节点地址 = 设备总表1.节点地址 SET 设备总表1.销售日 = Date()-1, 设备总表1.节点地址 = [设备总表2].[节点地址] WHERE 设备总表1.节点地址 Is Null ;

复制代码

怎么理解呢？我们不妨先来看这一段：

1. SELECT 设备总表1.销售日, 设备总表1.节点地址, * FROM 设备总表2 LEFT JOIN 设备总表1 ON 设备总表2.节点地址 = 设备总表1.节点地址 WHERE 设备总表1.节点地址 Is Null;

复制代码

左联接查询在得到的是设备总表2中与设备总表1不同的记录，即对于设备总表1有而设备总表2没有的记录，会自动生成空白记录，那么当它改为更新查询时，不言而喻，那就是将空白记录更新为相应数据了。

于是upsert查询可以理解成：先将空白记录追加进去，再进行更新，从而一步到位完成了追加查询与更新查询。——当然，这需要和前面的delete语句一样，建立先建立好一对一或者一对多关系才可以运行。

从这几个例子中体现出来的，表间关系的重要性，我想，应该是不言自明了吧？

轻风

受教

夏乐

学习{:soso_e112:}

好运牛

受教

风中漫步

好经验,谢谢分享

purplerose

谢谢ROYCH 占用自已的宝贵时间，花心血为大家写出这样在任何地方都无法找到的实例帖子，大家要好好看，好好学，以报答ROYCH的付出，在此衷心地感谢ROYCH！！希望他能多出一些这类的好帖子。{:soso_e160:}{:soso_e183:}

zpy2

roych 发表于 2015-7-21 16:24
现在我们来看看另一个问题：往设备总表2中插入，设备总表1中有而设备总表2没有的记录，并更新日期为昨天。 ...

INSERT INTO 设备总表1 ( 销售日, 节点地址 ) SELECT Date()-1 AS 销售日, 设备总表2.节点地址 FROM 设备总表2 LEFT JOIN 设备总表1 ON 设备总表2. 节点地址 = 设备总表1.节点地址 WHERE 设备总表1.节点地址 Is Null;

这句是插入到 设备总表1？

问题题是 向 设备总表2 插入…?

zpy2

roych 发表于 2015-7-21 16:23
说到not in，可能会扯出一段陈年往事来。几年前，一个版友提出一个问题：为什么执行左联接删除查询时，总是 ...

DELETE * FROM 用户动作 WHERE 节点地址 Not In (select 节点地址 from [设备数据]);
这句可能性能弱一点，但最短最直观，我喜欢。