一个sql子查询作为过滤条件的例子(原创)

2007-05-08 14:58

请看下面的问题

数据表T1，结构如下：
ID(标志列，主键)      C1(日期列)                              C2(数值型)
1                               2007-05-01 00:00:00.000       7
2                               2007-05-02 00:00:00.000       3
3                               2007-05-04 00:00:00.000       2
4                               2007-05-03 00:00:00.000       6

现在将C2列的值按C1列（日期）从小到大的顺序一直往后加。请想法找出当C2列的值相加之和是16时的ID列的值。并且不能用存储过程和游标，只用一句单纯的SQL语句写出来~

要解决这个问题该怎么办呢？大家一开始可能会到想到“循环”、“游标”、“变量”之类的，但这里已经声明了“不能用存储过程和游标，只用一句单纯的SQL语句写出来”,事实上也确实不需要用存储过程和游标，用一条语句实现更简单。

思考：
如果我们可以这样写一条语句：
select id,current_sum from t1 order by c1 asc

current_sum表示按C1列（日期）从小到大的顺序一直往后加到当前位置的值，我们就可以找到问题的切入点。

那么要怎么得到按C1列（日期）从小到大的顺序一直往后加到当前位置的值呢？这就需要用到子查询，把这个子查询代替current_sum就可以了。子查询和父语句是有关联的，那么这个子查询要怎么和父语句关联呢？由于我们要按C1列（日期）从小到大的顺序一直往后加，所以C1字段(日期)是关联的纽带。于是我们可以写下这样一条语句：
select id,(select sum(C2) from t1 as it where it.C1 <= ot.C1) from t1 as ot    order by C1 asc

查询结果：
ID           currnet_sum
1            7
2            10
4            16
3            18

这个结果正确地得到了按C1列（日期）从小到大的顺序一直往后加到当前位置的值，比如id为4对应的currnet_sum是16，因为初始数据中,id为4的日期比id为3要小，所以id为4这条记录按日期从小到大的顺序应该排在第3，因此7+3+6=16。

那it.C1 <= ot.C1这个条件是怎么回事呢？为什么要这样做呢？前面已经讲过，在这里，子查询和父语句的关联需要用到C1这个字段。为什么是<=而不是=或其他运算符呢？因为<=表示小于或等于当前时间，于是可以得到当前时间以下(包括当前时间)的各个值之和。这样，我们便把id值，以及当前相加的值成功的查询出来了。

假设我们把这个查询结果当作一个表，命名为t2，然后再进行查询，问题是不是迎刃而解了呢？于是我们可以写下这样的语句：
select id from t2 where currnet_sum = 16

很明显，id将是4，可以成功查询。

接下来要做的事情和前面一样，用刚才那个子查询将currnet_sum替换掉，稍作改动，于是得到语句:
select id from t1 as ot where (select sum(C2) from t1 as it where it.C1 <= ot.C1)     = 16 order by C1 asc

查询结果：
ID
4

成功执行！^_^

到目前为止，这个问题已经基本解决了。如果把问题稍微地改一下，改成查询当C2列的值相加之和大于或等于而又最接近于15时的ID列的值又该怎么办呢？

很明显用=15这样的查询是无法实现的，因为我们加起来的值分别是7，10，16，18，没有15。这个时候我们需要稍微地改动一下查询条件，将=15改成>=15，然后我们得到下面这条语句：
select id from t1 as ot where (select sum(C2) from t1 as it where it.C1 <= ot.C1) >= 15 order by C1 asc

查询结果：
ID
4
3

这里我们得到两条结果，将所有大于或等于15的记录都列出来了，由于我们还有一个“又最接近于15”的条件，于是我们可以在select语句中加上top语句取第一记录以得到结果。语句如下：
select top 1 id from t1 as ot where (select sum(C2) from t1 as it where it.C1 <= ot.C1) >= 15 order by C1 asc

查询结果：
ID
4

通过前面的讲解，我们知道id为4对应的current_sum是16，16便是大于或等于而又最接近于15的值。这表明，我们的查询是正确的。

到此为止，这一问题已经全部解决。简单吧？多么简单的一条语句啊，什么游标啊，存储过程啊，统统不需要！重要的是思路，思路通了，问题就解决了。