数据库表中有字段为A和B两个字段,字段中的数据为如下:
1 3
2 7
8 12
4 9在程序中,传递“4”和“5”两个值, 请问我能用什么语句才能判断出与A、B两个字段有交集的sql语句呢?
我需要的结果是能查询到:
2 7
4 9
1 3
2 7
8 12
4 9在程序中,传递“4”和“5”两个值, 请问我能用什么语句才能判断出与A、B两个字段有交集的sql语句呢?
我需要的结果是能查询到:
2 7
4 9
and val2 between A and B
FROM test
where start between 4 and 5
and end between 4 and 5不是我想要的结果啊!
FROM test
where startbetween 4 and 5
and end between 4 and 5 结果出来的不对呢!
------这个意思吗
SQL> with tablea as
2 (
3 select 1 a, 3 b from dual union all
4 select 2 a, 7 b from dual union all
5 select 8 a, 12 b from dual union all
6 select 4 a, 9 b from dual
7 )
8 select *
9 from tablea
10 where (4 between a and b)
11 and (5 between a and b)
12 ; A B
---------- ----------
2 7
4 9SQL>
2 (
3 select 1 a, 3 b
4 from dual
5 union all
6 select 2 a, 7 b
7 from dual
8 union all
9 select 8 a, 12 b
10 from dual
11 union all
12 select 4 a, 9 b from dual
13 )
14 select *
15 from tablea
16 where (3 between a and b)
17 or (5 between a and b)
18 ; A B
---------- ----------
1 3
2 7
4 9SQL>
SQL> with tablea as
2 (
3 select 1 a, 3 b
4 from dual
5 union all
6 select 2 a, 7 b
7 from dual
8 union all
9 select 8 a, 12 b
10 from dual
11 union all
12 select 4 a, 9 b from dual
13 )
14 select *
15 from tablea
16 where (4 between a and b)
17 or (5 between a and b)
18 ; A B
---------- ----------
2 7
4 9SQL>
with tablea as
(
select 1262275200 a, 1278000000 b from dual union all
select 1262534400 a, 1262538000 b from dual
)
select * from tablea where (1262275200 between a and b) or (1278172800 between a and b)
为什么只出现一条数据呢!
with tablea as
(
select 2 a, 6 b from dual union all
select 5 a, 6 b from dual union all
select 3 a, 5 b from dual
)
select * from tablea where (1 between a and b) or (6 between a and b)
数据出现将不正确呢
SQL> with tablea as
2 (
3 select 1262275200 a, 1278000000 b from dual union all
4 select 1262534400 , 1262538000 from dual
5 )
6 select a,b from tablea
7 where (1262275200-a>=0 or b-1262275200>=0) or (1278172800-a>=0 or b-1278172800>=0)
8 /
A B
---------- ----------
1262275200 1278000000
1262534400 1262538000
你的逻辑你自己没理清
我写的 只要满足 a,b 之间一个就可以了
Oraclefans_ 写的是满足 a,b两个
SELECT CALENDAR.EXT_ID,
CALENDAR.EXT_TITLE,
CALENDAR.EXT_START,
CALENDAR.EXT_END,
CALENDAR.EXT_ALLDAY,
CLASS.EXT_COLOR,
CALENDAR.EXT_URL
FROM EXT_CALENDAR CALENDAR
INNER JOIN EXT_CALENDAR_CLASS CLASS ON CALENDAR.EXT_CLASSNAME =
CLASS.EXT_CLASS
WHERE CLASS.EXT_DISPLAY = 1
AND CALENDAR.EXT_OWNER = 'QIYU'
AND ((CALENDAR.ext_end >= '1290873600' and CALENDAR.ext_start < '1290873600') or
(CALENDAR.ext_start <= '1293897600' and CALENDAR.ext_end > '1293897600') or
(CALENDAR.ext_start >= '1290873600' and CALENDAR.ext_end <= '1293897600'))
ORDER BY CALENDAR.EXT_PRIORITY