或者构造一个完整表来关联 不过效率会降很多 with t1 as ( select 'A' c1,'20120501' c2 from dual union all select 'A' c1,'20120502' c2 from dual union all select 'A' c1,'20120503' c2 from dual union all select 'B' c1,'20120501' c2 from dual union all select 'B' c1,'20120503' c2 from dual )select c1,c2 from (select distinct c1 from t1) full join (select distinct c2 from t1) b on 1=1 order by c1,c2 c1 c2 -------------------------- 1 A 20120501 2 A 20120502 3 A 20120503 4 B 20120501 5 B 20120502 6 B 20120503
with t as (select 'A' name ,20120501 date1 from dual union all select 'A' name ,20120502 from dual union all select 'A' name ,20120503 from dual union all select 'B' name ,20120501 from dual union all select 'B' name, 20120503 from dual ) select * from ( select t1.*,(t1.date1-t1.last) b from (select t.*,lag(t.date1,1,0)OVER(partition by name ORDER BY date1) last from t)t1) where b<>1 ;
补充下,这样得到的结果是: name date1 last b 1 A 20120501 0 20120501 2 B 20120501 0 20120501 3 B 20120503 20120501 2起始值会被查出来,可以稍微修改下上面sql把它去掉,last<>0
with t as (select 'A' name ,20120501 date1 from dual union all select 'A' name ,20120502 from dual union all select 'A' name ,20120503 from dual union all select 'B' name ,20120505 from dual union all select 'B' name, 20120506 from dual union all select 'B' name, 20120511 from dual union all select 'B' name, 20120512 from dual )select * from t where not exists( select 1 from t a where t.date1-a.date1 =-1 )or not exists( select 1 from t a where t.date1-a.date1 =1 ) ---能保证你看出来,但不能自动加上
问个问题:怎样查看with 生成的临时列表的列类型?附一下:lag的语法: The syntax for the lag function is:lag ( expression [, offset [, default] ] ) over ( [ query_partition_clause ] order_by_clause )expression is an expression that can contain other built-in functions, but can not contain any analytic functions.offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.
借用下楼上的数据,下面的方法可以解决楼主给出的数据出现的问题,如果实际使用的话,可以根据具体数据和需求进行完善。 with t1 as (select 'A' c1, '20120501' c2 from dual union all select 'A' c1, '20120502' c2 from dual union all select 'A' c1, '20120503' c2 from dual union all select 'B' c1, '20120501' c2 from dual union all select 'B' c1, '20120503' c2 from dual) select t2.c1, (t2.c2 + t2.c3) / 2 遗漏的日期 from (select t1.*,nvl(lead(c2, 1) over(partition by c1 order by c2), c2) c3 from t1) t2 where t2.c3 - t2.c2 > 1;
表明t 两列id,dateselect destinct(a.name) from t a left join t b on a.date=b.id and a.id='A' and b.id='B' where b.id is null and b.date is null
表明t 两列id,dateselect destinct(a.name) from t a left join t b on a.date=b.id and a.id='A' and b.id='B' where b.id is null and b.date is null
不好意思还要在where里加上a.id='A'select destinct(a.name) from t a left join t b on a.date=b.id and a.id='A' and b.id='B' where a.id='A'and b.id is null and b.date is null
select code from Aminusselect code from B
A 20120501 A 20120502 A 20120503 B 20120501 B 20120503 现在少 B 20120502 这条记录,怎么能用sql查询出来?
select date from table where col='A' minus select date from table where col='B';
(
select 'A' c1,'20120501' c2 from dual
union all
select 'A' c1,'20120502' c2 from dual
union all
select 'A' c1,'20120503' c2 from dual
union all
select 'B' c1,'20120501' c2 from dual
union all
select 'B' c1,'20120503' c2 from dual
)select c1,c2 from
(select distinct c1 from t1)
full join (select distinct c2 from t1) b
on 1=1
order by c1,c2 c1 c2
--------------------------
1 A 20120501
2 A 20120502
3 A 20120503
4 B 20120501
5 B 20120502
6 B 20120503
union all
select 'A' name ,20120502 from dual
union all
select 'A' name ,20120503 from dual
union all
select 'B' name ,20120501 from dual
union all
select 'B' name, 20120503 from dual )
select * from ( select t1.*,(t1.date1-t1.last) b from (select t.*,lag(t.date1,1,0)OVER(partition by name ORDER BY date1) last from t)t1)
where b<>1
;
name date1 last b
1 A 20120501 0 20120501
2 B 20120501 0 20120501
3 B 20120503 20120501 2起始值会被查出来,可以稍微修改下上面sql把它去掉,last<>0
union all
select 'A' name ,20120502 from dual
union all
select 'A' name ,20120503 from dual
union all
select 'B' name ,20120505 from dual
union all
select 'B' name, 20120506 from dual
union all
select 'B' name, 20120511 from dual
union all
select 'B' name, 20120512 from dual )select * from t where not exists(
select 1 from t a where t.date1-a.date1 =-1
)or not exists(
select 1 from t a where t.date1-a.date1 =1
)
---能保证你看出来,但不能自动加上
问个问题:怎样查看with 生成的临时列表的列类型?附一下:lag的语法:
The syntax for the lag function is:lag ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )expression is an expression that can contain other built-in functions, but can not contain any analytic functions.offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.
with t1 as
(select 'A' c1, '20120501' c2 from dual
union all
select 'A' c1, '20120502' c2 from dual
union all
select 'A' c1, '20120503' c2 from dual
union all
select 'B' c1, '20120501' c2 from dual
union all
select 'B' c1, '20120503' c2 from dual)
select t2.c1, (t2.c2 + t2.c3) / 2 遗漏的日期
from (select t1.*,nvl(lead(c2, 1) over(partition by c1 order by c2), c2) c3
from t1) t2
where t2.c3 - t2.c2 > 1;
from t a left join t b on a.date=b.id and a.id='A' and b.id='B'
where b.id is null and b.date is null
from t a left join t b on a.date=b.id and a.id='A' and b.id='B'
where b.id is null and b.date is null
from t a left join t b on a.date=b.id and a.id='A' and b.id='B'
where a.id='A'and b.id is null and b.date is null
A 20120502
A 20120503
B 20120501
B 20120503
现在少 B 20120502 这条记录,怎么能用sql查询出来?
select date from table where col='A'
minus
select date from table where col='B';