我的天

我知道有一个大表，我也有这个大表其中了一些数据的小表，我需要筛选出除了小表以外的数据，思路非常清晰，但实际上要用Excel里面的SQL实现却好像异常困难。在一些高级数据库里，有特殊的函数实现这个功能，有些用的是except，有些用的是minus，但是Excel里没有。如果可以用except或者是minus，这将意味着可以对大小表全列对比，但是如果用其他方法，只能选取其中一些。刚好我要操作的表有一列刚好是多个列信息的集合，所以用那一列作为标识刚好，但如果我根本没有那一列呢？难道我还得先造一列出来，然后再进行操作吗？无论是用not exists或者not in的方式去进行这种筛选,都只能选取其中的某一列操作。如果是用not in的方式，还得要注意，如果那个小表是空的时候，会导致筛选失效。如果用not exists的方式，则不需要考虑小表是空的情况。

一开始想实现这种功能，我想到的不是这两个，而是用count。先去计算那些有重复项的行，然后针对不同计数结果分别处理。就思路来说很清晰，但就实现来说，很啰嗦。但虽然啰嗦，还是能得出我想要的结果。第一天我用这种计数的方式得到了我想要的结果，但我依然觉得应该有一些更直接一点的方法，于是第2天我就想用not in或者not exists的方式去实现。

not in的方式总算是做到了，但是效率不高，感觉耗时是用计数方式的两倍。in和exists相比，我感觉exists的效率更高，但关键是实际上我使用的是not in和not exist，所以估计还是挺麻烦。虽然就语句来说，不过是多了一个not而已。exists的实现耗费了我不少时间，理论上我的写法没有任何问题，但关键是就会报错。折腾了一个下午都无果。晚上洗澡的时候，我突然想到可能是引用数据简写那里出了状况。在使用not exists子查询的时候。我依然是用经典的a和b简写，但实际上在这个子查询引用的数据里面就曾经出现过a和b。如果某一句查询里面的简写a和b是同一个层次的，没什么问题，但关键是子查询这种操作有递归的感觉，主查询跟子查询不是一个层次。VBA弹出来的报错窗口说某个简写可能指向多个数据。后来我觉得大概是因为之前我就已经有用过a这种简写，层层嵌套之下，exists的子查询不知道我指的到底是哪一层的a。洗完澡以后，我赶紧找文件测试，把主查询的a改为了之前从来没用过的c。果然，not exists子查询通过了！多次测试对比发现not in跟not exists的性能确有区别，not exists会快一点点,但是跟计数相比，好像计数依然是三者之中最快的。从好理解的角度而言，not exists最为复杂，最好理解的我觉得是计数，但是就语句使用的长短而言，not exists和not in最简单的。最后我采用的是not exists的方案，但是not in跟之前计数的方案我都只是把它们注释掉了，没有删除。

如果Excel里面的SQL能与时俱进，我能用一些那些高端的函数，我根本不需要这么大费周折。别人有现成的模块，连个线就能用，但我什么都没有，所以哪怕是一个螺丝，我都得从零开始手搓。

昨天说到库存查询，最后的展示方式是透视表，透视这个东西只要前面做对了，就可以实现，那就只是最后一个步骤而已，但是如何得到透视需要的所有东西呢？在做这个库存查询之前，如果我需要得到某数据在连续日期中的汇总，我会先把那些数据按日期分组，然后选定日期表里面其中一段连续日期，接着用左外的方式连接日期表和数据。但问题是在库存查询中，我最后的结果是透视的，这就意味着被透视的那些字段在没有被透视之前，是各自对应一段一样的日期表，如果最后透视出来的字段有5个，就意味着我有5段一样的日期表，我该怎么表达这个东西呢？

在解决这个多段一模一样的连续日期之前，我首先攻克的是累计数。在SQL里面实现累计数有好几种方式，但问题是不是每一个都适合在Excel的VBA里实现，比如窗口函数over在Excel里面就是不支持的，虽然窗口函数的效率是最高的。对同一个表进行子查询可以实现累计，但问题是这样生成的累计无法用在下一步的透视里使用，接着我在另外一个方案里面看到了笛卡尔积的方法，笛卡尔积得出来的结果跟子查询完全一样，但笛卡尔积出来的结果可以用在下一步的透视里，而且同样的数据，笛卡尔积的运行效率比子查询高（可能是我测试的数据少？）。这个累计数的问题，我在Power Pivot里也研究过。我觉得PP的解决方案跟子查询有点类似。在计算累计数的时候，PP的效率很高。在用PP实现累计数之前，我曾经用Power Query实现累计数。我的PQ解决办法是先生成变化数，然后按日期把变化数累加起来。接下来用日期表跟这个数据左外连接，如果不是天天都有变化数，那么这个合并后的累计肯定会有空行，这个时候再用向下填充的方式补全。用这样的方法在PQ里的确是可以计算出每天的库存，但是效率非常低，所以在那以后，当我要做累计库存，我会直接放弃PQ选择PP。现在我已经掌握了在VBA里用SQL的方法实现。在筛选的日期不多，以及最后被透视出来的字段不多的情况下，效率挺高。比如最终我只需要展示一个月的数据。运行时间通常不会超过0.5秒。我个人觉得1秒是一个分水岭，如果1秒以上才出结果的话，我觉得这需要等待，尤其是运行时间超过2秒，我觉得那得优化了。低于0.5秒的运行时间对我来说几乎是无感的，我可以接受这种方案。

在累计数可以实现之后我要继续研究怎么按照条件需求把日期表捆绑上去。折腾了好长一段时间，都是没什么结果，于是我就去吃午饭了，在去吃午饭的路上，我突然想到，要把这个日期表扩充开来，实际上我不就是要做一个按条件笛卡尔积吗？所以我需要进行左外连接左边的部分，在这个情况下就不是一个普通的日期表，而是被透视项和日期表进行笛卡尔积的东西。笛卡尔积这个东西，如果数据很多会是一个噩梦，甚至会让电脑崩溃，所以所有教程都会告诉你，如果你要的不是这种东西，尽量不要做这种操作。但思前想后，我发现我正是需要这种东西。吃过午饭后，我赶紧去测试我的想法，果然，用笛卡尔积的结果再加后续的操作，我就能生成我需要的东西，并最终能以透视的方式展示出来。

我感觉现在当我把一些最基础的东西用熟练了以后，渐渐地我体会到了一些其实你明明知道，但是你却完全没有料到可以这么用的方法。

既然人可以通过某些逻辑得到某些结果，那么我应该可以按照这些逻辑生成一些自动化的方法，在这个时候，我最讨厌例外情况。