关于Oracle between and 是否包含边界问题
今天做了一个测试,发现一些有意思的事情:
##分组方法1
select CASE WHEN age IS NULL THEN '未知'
WHEN age BETWEEN 0 AND 59 THEN '0-59岁'
WHEN age BETWEEN 60 AND 64 THEN '60-64岁'
WHEN age BETWEEN 65 AND 69 THEN '65-69岁'
WHEN age BETWEEN 70 AND 74 THEN '70-74岁'
WHEN age >= 75 THEN '75岁以上'
END as nnd
##分组方法2
select CASE WHEN age IS NULL THEN '未知'
WHEN age BETWEEN 0 AND 60 THEN '0-60岁'
WHEN age BETWEEN 60 AND 65 THEN '60-65岁'
WHEN age BETWEEN 65 AND 70 THEN '65-70岁'
WHEN age BETWEEN 70 AND 75 THEN '70-75岁'
WHEN age >= 75 THEN '75岁以上'
END as nnd
##分组方法3
select CASE WHEN age IS NULL THEN '未知'
WHEN age BETWEEN 0 AND 59 THEN '0-59岁'
WHEN age BETWEEN 60 AND 65 THEN '60-65岁'
WHEN age BETWEEN 65 AND 70 THEN '65-70岁'
WHEN age BETWEEN 70 AND 75 THEN '70-75岁'
WHEN age >= 75 THEN '75岁以上'
END as nnd
1.首先可以看到三种方法的总计都是一样的,说明oracle对于某一个病例不会重复count,所以就算我们设置的分组边界有交叉,也只会算入其中的一组。
2.由分组2和分组3的结果可以看出,在分组有交叉的情况下(0-60,60-65),60岁的人群会优先分入前面那个0-60的组;而不会出现在60-65中。
3.另外,综上,我们可以相信,oracle的between and函数会包含前后边界,除非该边界被另外一个分组占用