有两个表:a表:id dam_id type
1 1 6
2 2 3
3 2 3
4 3 6
5 3 2
6 1 1
b表:dam_id time
1 yyyy
2 yyyyb和a 关联 ,怎么查询出表b中在时间yyyy范围内的,b表中type没有6的dam_id
1 1 6
2 2 3
3 2 3
4 3 6
5 3 2
6 1 1
b表:dam_id time
1 yyyy
2 yyyyb和a 关联 ,怎么查询出表b中在时间yyyy范围内的,b表中type没有6的dam_id
with a as
(
select 1 as id, 1 as dam_id, 6 as type from dual
union all
select 2 as id, 2 as dam_id, 3 as type from dual
union all
select 3 as id, 2 as dam_id, 3 as type from dual
union all
select 4 as id, 3 as dam_id, 6 as type from dual
union all
select 5 as id, 3 as dam_id, 2 as type from dual
union all
select 6 as id, 1 as dam_id, 1 as type from dual
),
b as
(
select 1 as dam_id, 'yyyy' as time from dual
union all
select 2 as dam_id, 'yyyy' as time from dual
)
select a.*, b.*
from a, b
where a.dam_id = b.dam_id
and b.time = 'yyyy'
and a.type <> 6;
ID DAM_ID TYPE DAM_ID TIME
---------- ---------- ---------- ---------- ----
2 2 3 2 yyyy
3 2 3 2 yyyy
6 1 1 1 yyyy
from a, b
where a.dam_id = b.dam_id
and b.time = 'yyyy'
and a.type <> 6;