实测数据:CREATE TABLE T125 ( NO VARCHAR2(20), Status NUMBER(4), MyDate DATE );INSERT INTO T125 VALUES('001', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T125 VALUES('001', -1, to_date('2011-01-02 10:00:01', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T125 VALUES('002', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T125 VALUES('002', -1, to_date('2011-01-03 10:00:01', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T125 VALUES('003', 1, to_date('2011-02-11 10:00:01', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T125 VALUES('003', -1, to_date('2011-02-11 14:11:01', 'YYYY-MM-DD HH24:MI:SS')); 测试结果:
With tb_data As (Select Null "NO", Null "STATUS", Null "DATE" From dual Where 1 = 2 Union All Select '001', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual Union All Select '001', -1, to_date('2011-01-02 10:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual Union All Select '002', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual Union All Select '002', -1, to_date('2011-01-03 10:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual Union All Select '003', 1, to_date('2011-02-11 10:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual Union All Select '003', -1, to_date('2011-02-11 14:11:01', 'YYYY-MM-DD HH24:MI:SS') From dual ) Select * From (Select * From tb_data pivot(Max("DATE") For status In(1 "1", -1 "-1"))) Where trunc("1") = trunc("-1"); 用了Oracle 11g的新特性
tb 你的表…… 相同no和to_char(date,'YYYYMMDD')日期,有status的值等于1或者-1重复是 sql如下: select no from( select no, status, to_char(date,'YYYYMMDD') date from tb group by no, status,to_char(date,'YYYYMMDD') ) group by no,d having sum(status)=0 相同no和to_char(date,'YYYYMMDD')日期,status的只有1或者-1不重复 sql如下: select no from tb group by no,to_char(date,'YYYYMMDD') having sum(status)=0
create table atd.test_20120216_3(no char(3),status char(2),dt timestamp); alter table atd.test_20120216_3 alter status set data type char(3); insert into atd.test_20120216_3 values ('001','1','2011-1-1 00:00:01') ,('001','-1','2011-1-2 10:00:01') ,('002','1','2011-1-1 00:00:01') ,('002','-1','2011-1-3 10:00:01') ,('003','1','2011-2-11 10:00:01') ,('003','-1','2011-2-11 14:11:01'); select * from atd.test_20120216_3; select no,substr(char(dt),1,10) from atd.test_20120216_3 group by no,substr(char(dt),1,10) having count(*)>1
(
NO VARCHAR2(20),
Status NUMBER(4),
MyDate DATE
);INSERT INTO T125 VALUES('001', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T125 VALUES('001', -1, to_date('2011-01-02 10:00:01', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T125 VALUES('002', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T125 VALUES('002', -1, to_date('2011-01-03 10:00:01', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T125 VALUES('003', 1, to_date('2011-02-11 10:00:01', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO T125 VALUES('003', -1, to_date('2011-02-11 14:11:01', 'YYYY-MM-DD HH24:MI:SS'));
测试结果:
(Select Null "NO", Null "STATUS", Null "DATE" From dual Where 1 = 2
Union All Select '001', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual
Union All Select '001', -1, to_date('2011-01-02 10:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual
Union All Select '002', 1, to_date('2011-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual
Union All Select '002', -1, to_date('2011-01-03 10:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual
Union All Select '003', 1, to_date('2011-02-11 10:00:01', 'YYYY-MM-DD HH24:MI:SS') From dual
Union All Select '003', -1, to_date('2011-02-11 14:11:01', 'YYYY-MM-DD HH24:MI:SS') From dual
)
Select *
From (Select * From tb_data pivot(Max("DATE") For status In(1 "1", -1 "-1")))
Where trunc("1") = trunc("-1");
用了Oracle 11g的新特性
相同no和to_char(date,'YYYYMMDD')日期,有status的值等于1或者-1重复是
sql如下:
select no from(
select no, status, to_char(date,'YYYYMMDD') date
from tb
group by no, status,to_char(date,'YYYYMMDD')
)
group by no,d
having sum(status)=0
相同no和to_char(date,'YYYYMMDD')日期,status的只有1或者-1不重复
sql如下:
select no from tb
group by no,to_char(date,'YYYYMMDD')
having sum(status)=0
create table atd.test_20120216_3(no char(3),status char(2),dt timestamp);
alter table atd.test_20120216_3 alter status set data type char(3);
insert into atd.test_20120216_3 values
('001','1','2011-1-1 00:00:01')
,('001','-1','2011-1-2 10:00:01')
,('002','1','2011-1-1 00:00:01')
,('002','-1','2011-1-3 10:00:01')
,('003','1','2011-2-11 10:00:01')
,('003','-1','2011-2-11 14:11:01');
select * from atd.test_20120216_3;
select no,substr(char(dt),1,10) from atd.test_20120216_3 group by no,substr(char(dt),1,10) having count(*)>1