select distinct a1 from C1 join YU using (a1) where C1.E14=1 and C1.B16=0 and YU.k7>0
在oracle 11g中测试过了 不知道在其他版本下可以运行不
with c1 as( select 1 A, '200901' S, 1 Z, 0 T from dual union all select 1 A, '201001' S, 4 Z, 1 T from dual union all select 2 A, '200912' S, 1 Z, 0 T from dual union all select 2 A, '201001' S, 1 Z, 1 T from dual), yu as( select 1 A, 0 Y from dual union all select 2 A, 2 Y from dual)SELECT DISTINCT a FROM c1, yu WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND yu.y > 0 AND c1.a = yu.a;
--上面有误,这个 with c1 as( select 1 A, '200901' S, 1 Z, 0 T from dual union all select 1 A, '201001' S, 4 Z, 1 T from dual union all select 2 A, '200912' S, 1 Z, 0 T from dual union all select 2 A, '201001' S, 1 Z, 1 T from dual), yu as( select 1 A, 0 Y from dual union all select 2 A, 2 Y from dual)SELECT DISTINCT c1.a FROM c1, yu WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND yu.y > 0 AND c1.a = yu.a;
请问DA虾:条件2009年度Z=1 2010年度T=0 怎么查询
你这个是条件是 2009年度Z=1 或者 2010年度T=0 吧是就这样(s是字符型) ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0))
亲们。大虾可惜没得出来结果 输出为: A 2 这个数据库几万个编号,每个编号每年12条记录。 记录全在C1表,YU表里也是每年12条
with tt as (select 1 A,to_date('200901','yyyymm') s,1 z,0 t from dual union all select 1,to_date('201001','yyyymm'),4,1 from dual union all select 2,to_date('200912','yyyymm'),1,0 from dual union all select 2,to_date('201001','yyyymm'),1,1 from dual ), t1 as ( select 1 a,0 y from dual union all select 2,2 from dual )select distinct tt.a from tt,t1 where (tt.a,'2009') in(select a,to_char(s,'yyyy') from tt where z =1) and (tt.a,'2010') in(select a,to_char(s,'yyyy') from t1 where t =0) and tt.a = t1.a and t1.y >0;
你要用这部分去查询啊,上面那部分只是构造的例子数据 你不能完全照搬的 SELECT DISTINCT c1.a FROM c1, yu WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND yu.y > 0 AND c1.a = yu.a;
出来有很多结果,但是同一编号2009年度出现了Z=1还有Z=2.然而2010年度T<>0
tangren仁兄写的已经很明白了 with c1 as( select 1 A, '200901' S, 1 Z, 0 T from dual union all select 1 A, '201001' S, 4 Z, 1 T from dual union all select 2 A, '200912' S, 1 Z, 0 T from dual union all select 2 A, '201001' S, 1 Z, 1 T from dual), yu as( select 1 A, 0 Y from dual union all select 2 A, 2 Y from dual) 这部分是在构建你列举的例子(构建出两个‘临时表’),以便后边的sql语句查询只用 如果: SELECT DISTINCT c1.a FROM c1, yu WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND yu.y > 0 AND c1.a = yu.a; 这个语句不明白,看这个怎么样: SELECT DISTINCT c1.a FROM c1 left join yu on c1.a = yu.a WHERE ((c1.s LIKE '2009%' AND c1.z = 1) OR (c1.s LIKE '2010%' AND c1.t = 0)) AND yu.y > 0 ; 这个如果还不明白,那就把真的字段贴出来吧!!!
select distinct a1
from C1 join YU
using (a1)
where C1.E14=1 and C1.B16=0 and YU.k7>0
select 1 A, '200901' S, 1 Z, 0 T from dual union all
select 1 A, '201001' S, 4 Z, 1 T from dual union all
select 2 A, '200912' S, 1 Z, 0 T from dual union all
select 2 A, '201001' S, 1 Z, 1 T from dual),
yu as(
select 1 A, 0 Y from dual union all
select 2 A, 2 Y from dual)SELECT DISTINCT a
FROM c1, yu
WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND
yu.y > 0 AND
c1.a = yu.a;
with c1 as(
select 1 A, '200901' S, 1 Z, 0 T from dual union all
select 1 A, '201001' S, 4 Z, 1 T from dual union all
select 2 A, '200912' S, 1 Z, 0 T from dual union all
select 2 A, '201001' S, 1 Z, 1 T from dual),
yu as(
select 1 A, 0 Y from dual union all
select 2 A, 2 Y from dual)SELECT DISTINCT c1.a
FROM c1, yu
WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND
yu.y > 0 AND
c1.a = yu.a;
2009年度Z=1 或者 2010年度T=0 吧是就这样(s是字符型)
((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0))
但是这样不好,如果s上有索引,则用不上(除非另建函数索引),
影响效率!!
输出为:
A
2
这个数据库几万个编号,每个编号每年12条记录。
记录全在C1表,YU表里也是每年12条
(select 1 A,to_date('200901','yyyymm') s,1 z,0 t from dual
union all
select 1,to_date('201001','yyyymm'),4,1 from dual
union all
select 2,to_date('200912','yyyymm'),1,0 from dual
union all
select 2,to_date('201001','yyyymm'),1,1 from dual
),
t1 as
(
select 1 a,0 y from dual
union all
select 2,2 from dual
)select distinct tt.a from tt,t1
where (tt.a,'2009') in(select a,to_char(s,'yyyy') from tt where z =1)
and (tt.a,'2010') in(select a,to_char(s,'yyyy') from t1 where t =0)
and tt.a = t1.a and t1.y >0;
你不能完全照搬的
SELECT DISTINCT c1.a
FROM c1, yu
WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND
yu.y > 0 AND
c1.a = yu.a;
with c1 as(
select 1 A, '200901' S, 1 Z, 0 T from dual union all
select 1 A, '201001' S, 4 Z, 1 T from dual union all
select 2 A, '200912' S, 1 Z, 0 T from dual union all
select 2 A, '201001' S, 1 Z, 1 T from dual),
yu as(
select 1 A, 0 Y from dual union all
select 2 A, 2 Y from dual)
这部分是在构建你列举的例子(构建出两个‘临时表’),以便后边的sql语句查询只用
如果:
SELECT DISTINCT c1.a
FROM c1, yu
WHERE ((s LIKE '2009%' AND z = 1) OR (s LIKE '2010%' AND t = 0)) AND
yu.y > 0 AND
c1.a = yu.a;
这个语句不明白,看这个怎么样:
SELECT DISTINCT c1.a
FROM c1 left join yu on c1.a = yu.a WHERE ((c1.s LIKE '2009%' AND c1.z = 1) OR (c1.s LIKE '2010%' AND c1.t = 0)) AND
yu.y > 0 ;
这个如果还不明白,那就把真的字段贴出来吧!!!