22,209
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO temp_sameSiteType_ck
( city ,
distance ,
sitecode ,
sitetype
)
SELECT t.city ,
t.distance ,
t.sitecode ,
t.sitetype
FROM samesitetypestaticdetail t
WHERE 1 = 1
AND trunc(INSERT_TIME) = trunc(to_date('2017.09.20',
'yyyy-mm-dd'))
AND ( ( t.sitetype = '1111'
AND t.distance > 1111
)
OR ( t.sitetype = '2222'
AND t.distance > 2222
)
OR ( t.sitetype = '4444'
AND t.distance > 4444
)
OR t.sitetype = '5555'
AND t.distance > 5555
OR ( t.sitetype = '3333'
AND t.distance > 3333
)
)
AND NOT EXISTS ( SELECT sitename
FROM SITE_SAME_WHITELIST b
WHERE b.sitename = t.sitecode )
--插入表中
INSERT INTO temp_sameSiteType_ck
(
city,
distance,
sitecode,
sitetype
)
--查询数据
SELECT t.city,
t.distance,
t.sitecode,
t.sitetype
FROM samesitetypestaticdetail t
WHERE 1 = 1
AND trunc(INSERT_TIME) = trunc(to_date('2017.09.20', 'yyyy-mm-dd'))
AND (
1 = 2
OR (t.sitetype = '1111' AND t.distance > 1111)
OR (t.sitetype = '2222' AND t.distance > 2222)
OR (t.sitetype = '4444' AND t.distance > 4444)
OR t.sitetype = '5555'
AND t.distance > 5555
OR (t.sitetype = '3333' AND t.distance > 3333)
)
AND NOT EXISTS (
SELECT sitename
FROM SITE_SAME_WHITELIST b
WHERE b.sitename = t.sitecode
)
这段代码格式化一下你就能看明白了,其实就是把查询出来的结果集插入表temp_sameSiteType_ck中。当满足
(
1 = 2
OR (t.sitetype = '1111' AND t.distance > 1111) --1
OR (t.sitetype = '2222' AND t.distance > 2222) --2
OR (t.sitetype = '4444' AND t.distance > 4444) --3
OR t.sitetype = '5555' --4
AND t.distance > 5555
OR (t.sitetype = '3333' AND t.distance > 3333) --5
)
这个上面五个其中任何一个,且满足 t.distance > 5555的时候,查询就会有数据。这时候就有数据插入表temp_sameSiteType_ck中。相反的如果都不满足这五个or,那么查询就会变成where 1=1 and 1=2。这个时候就不会有数据出来,也就不会有数据插入表temp_sameSiteType_ck中。所以1=2写不写都没多大关系吧。不明白为啥要写1=1和1=2进去,写了跟没写有啥区别啊