sql小bug

①
SELECT * FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= '2016-01'
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= '2016-12-03'

AND CITYCODE = '110000'

②
SELECT * FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= '2016-01'
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM-DD') <= '2016-12-03'

AND CITYCODE = '110000'

注意①和②两个sql语句中TO_CHAR(A.TIMEPOINT,'YYYY-MM') 和TO_CHAR(A.TIMEPOINT,'YYYY-MM-DD') 上面的sql语句格式化没有加上-dd参数,那么格式化后后面显示的是

2016年12月全年的数据,下面的sql加上了DD参数,TO_CHAR(A.TIMEPOINT,'YYYY-MM-DD') <= '2016-12-03',这个显示的数据就是截止到2016-12-03的数据,所以注意格式化的问题!!!

检查上面的sql比下面的sql多哪些数据的时候,注意的是MINUS函数的应用

SELECT * FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN('优','良')

AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= '2016-01'
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= '2016-12-03'
AND CITYCODE = '110000'
MINUS (
SELECT *  FROM TENV.AIR_CITYDAYAQI_PUBLISH P 
WHERE P.QUALITY IN('优','良') 
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM') >= TO_CHAR(TO_DATE(CONCAT(YEAR(SYSDATE),'-01'),'YYYY-MM') - 1 YEAR,'YYYY-MM')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM-DD') <= TO_CHAR((SYSDATE-1 DAY) -1 YEAR,'YYYY-MM-DD')
AND P.CITYCODE = '110000'
)

这样显示的就是上面的语句比下面的语句多出来的数据了,上面显示的差集的值!!!

下面语句显示的是交集的值
SELECT * FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= '2016-01'
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM-DD') <= '2016-12-03'
AND CITYCODE = '110000'
INTERSECT (
SELECT *  FROM TENV.AIR_CITYDAYAQI_PUBLISH P 
WHERE P.QUALITY IN('优','良') 
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM') >= TO_CHAR(TO_DATE(CONCAT(YEAR(SYSDATE),'-01'),'YYYY-MM') - 1 YEAR,'YYYY-MM')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM-DD') <= TO_CHAR((SYSDATE-1 DAY) -1 YEAR,'YYYY-MM-DD')
AND P.CITYCODE = '110000'
)

下面UNION 函数无重复并集

SELECT * FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= '2016-01'
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= '2016-12-03'
AND CITYCODE = '110000'
UNION (
SELECT *  FROM TENV.AIR_CITYDAYAQI_PUBLISH P 
WHERE P.QUALITY IN('优','良') 
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM') >= TO_CHAR(TO_DATE(CONCAT(YEAR(SYSDATE),'-01'),'YYYY-MM') - 1 YEAR,'YYYY-MM')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM-DD') <= TO_CHAR((SYSDATE-1 DAY) -1 YEAR,'YYYY-MM-DD')
AND P.CITYCODE = '110000'
)

下面UNION  ALL函数有重复并集

SELECT * FROM TENV.AIR_CITYDAYAQI_PUBLISH A WHERE A.QUALITY IN('优','良')
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') >= '2016-01'
AND TO_CHAR(A.TIMEPOINT,'YYYY-MM') <= '2016-12-03'
AND CITYCODE = '110000'
UNION ALL (
SELECT *  FROM TENV.AIR_CITYDAYAQI_PUBLISH P 
WHERE P.QUALITY IN('优','良') 
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM') >= TO_CHAR(TO_DATE(CONCAT(YEAR(SYSDATE),'-01'),'YYYY-MM') - 1 YEAR,'YYYY-MM')
AND TO_CHAR(P.TIMEPOINT,'YYYY-MM-DD') <= TO_CHAR((SYSDATE-1 DAY) -1 YEAR,'YYYY-MM-DD')
AND P.CITYCODE = '110000'
)

1

、

UNION 

(

无重并集

)

：

当执行

UNION 

时，

自动去掉结果集中的重复行

，并以第

一列的结果进行升序排序。

 

2

、

UNION ALL (

有重并集

)

：

不去掉重复行，

并且不对结果集进行排序。

UNION 函数以第一列的结果升序排序,UNION ALL不对结果集排序