现在也有个数据统计的问题困绕着我,
有如下的数据
sdate encode count
-------- -------- ------
2006-1-1 1001 5
2006-1-1 1002 11
2006-1-2 1002 7
2006-1-2 1003 1
2006-1-3 1003 5
2006-1-3 1003 21
2006-1-4 1001 18
2006-1-4 1002 10现在想要得到如下形式的数据
sdate encode
-------- -------
2006-1-2 1001
2006-1-3 1001
2006-1-3 1002我要得到的是encode在哪几天里没有销售,
求SQL语句,要考虑性能问题(表里有几百万条记录,而且每天都在增长),
分不够可以另外开帖,
请达人解惑!
有如下的数据
sdate encode count
-------- -------- ------
2006-1-1 1001 5
2006-1-1 1002 11
2006-1-2 1002 7
2006-1-2 1003 1
2006-1-3 1003 5
2006-1-3 1003 21
2006-1-4 1001 18
2006-1-4 1002 10现在想要得到如下形式的数据
sdate encode
-------- -------
2006-1-2 1001
2006-1-3 1001
2006-1-3 1002我要得到的是encode在哪几天里没有销售,
求SQL语句,要考虑性能问题(表里有几百万条记录,而且每天都在增长),
分不够可以另外开帖,
请达人解惑!
都对应sdate的表,然后可以用集合相减(minus)来求出结果。当然也可以适用语句,有点麻烦,且效率肯定不会很高
insert into @t select '2006-1-1',1001,5
insert into @t select '2006-1-1',1002,11
insert into @t select '2006-1-2',1002,7
insert into @t select '2006-1-2',1003,1
insert into @t select '2006-1-3',1003,5
insert into @t select '2006-1-3',1003,21
insert into @t select '2006-1-4',1001,18
insert into @t select '2006-1-4',1002,10select
a.sdate,b.encode
from
(select distinct sdate from @t) a,
(select distinct encode from @t) b
where
not exists(select 1 from @t where sdate=a.sdate and encode=b.encode)
order by
b.encode,a.sdate/*sdate encode
------------------------------------------------------ -----------
2006-01-02 00:00:00.000 1001
2006-01-03 00:00:00.000 1001
2006-01-03 00:00:00.000 1002
2006-01-01 00:00:00.000 1003
2006-01-04 00:00:00.000 1003
*/
-----------------------------------------------------------------------------------------------
create table t(sdate varchar2(10),encode numeric,count numeric)
insert into t values('2006-1-1',1001,5 )
insert into t values('2006-1-1',1002,11)
insert into t values('2006-1-2',1002,7 )
insert into t values('2006-1-2',1003,1 )
insert into t values('2006-1-3',1003,5 )
insert into t values('2006-1-3',1003,21)
insert into t values('2006-1-4',1001,18)
insert into t values('2006-1-4',1002,10)select
a.sdate,b.encode
from
(select distinct sdate from t) a,
(select distinct encode from t) b
where
not exists(select * from t where sdate=a.sdate and encode=b.encode)
order by
b.encode,a.sdate