select count(distinct main_domain) from spd_domain t where t.is_chinese_site = 0 and t.alexa_ranking =-1
结果 1635t条记录
select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from SPD_DOMAIN
where ALEXA = 0) t
where rn = 1
and t.IS_CHINESE_SITE = 0
and t.alexa_ranking = -1
结果61条记录同一张表,为什么两个差异这么大?表共有13万多记录
结果 1635t条记录
select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from SPD_DOMAIN
where ALEXA = 0) t
where rn = 1
and t.IS_CHINESE_SITE = 0
and t.alexa_ranking = -1
结果61条记录同一张表,为什么两个差异这么大?表共有13万多记录
select count(distinct main_domain)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from SPD_DOMAIN
where ALEXA = 0)
where
IS_CHINESE_SITE = 0
and alexa_ranking = -1
select count(distinct main_domain)--我还要取其它的信息,不只终计有多个,所以这个sql在程序里没有用
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0这两个结果是一样的,但我想有多个主域名,我只想取一个,不是应该加上rn=1吗
select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
row_number() over(partition by main_domain order by MAIN_DOMAIN) rn
from SPD_DOMAIN
)
where rn=1
and IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0
select count(distinct main_domain)
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0
改成这样了,两个结果还不对
-----------------
16SQL> select count(1) from (select distinct yy from dbzg_da); COUNT(1)
---------
16SQL> select count(*) from (select yy,row_number() over(partition by yy order by yy) rn from dbzg_Da)
where rn=1; COUNT(*)
---------
16SQL>
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
(distinct main_domain),--row_number() over(partition by main_domain order by MAIN_DOMAIN) rn
from SPD_DOMAIN
)
where rn=1
and IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0
select count(distinct main_domain)
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0你再试下!看结果一样不?
看了你的例子,我觉得我的row_nubmer用法 没错啊
为什么我的sql结果会不一样的,
会是哪出问题呢
select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
(distinct main_domain)--row_number() over(partition by main_domain order by MAIN_DOMAIN) rn
from SPD_DOMAIN
)
where rn=1
and IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0
这样报错啊 报缺少表达式的错
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
row_number() over(partition by main_domain order by MAIN_DOMAIN) rn
from SPD_DOMAIN
)
where rn=1
and IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0 --21841
select count(distinct main_domain)
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0 --22680
呵呵,不好意思,我的失误:
select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
(distinct main_domain) as domain,/**row_number() over(partition by main_domain order by MAIN_DOMAIN) rn*/
from SPD_DOMAIN
)
where /**rn=1 and*/
IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0;
select count(distinct main_domain)
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0;
-----------------
16SQL> select count(1) from (select distinct yy from dbzg_da); COUNT(1)
---------
16SQL> select count(*) from (select yy,row_number() over(partition by yy order by yy) rn from dbzg_Da)
where rn=1; COUNT(*)
---------
16SQL>
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
row_number() over(partition by main_domain order by MAIN_DOMAIN) rn
from SPD_DOMAIN
where IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0
)
where rn=1--------------------
select count(distinct main_domain)
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0
Mark,等待答案!
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN desc) rn
from SPD_DOMAIN
where ALEXA = 0
and IS_CHINESE_SITE = 0
and alexa_ranking = -1
) t
where rn = 1
--and t.IS_CHINESE_SITE = 0
--and t.alexa_ranking = -1改成这样
row_number() over(partition by main_domain order by MAIN_DOMAIN) rn
改为
row_number() over(partition by main_domain order by DOMAIN) rn
看看下面两个语句是否一样select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
ALEXA,
row_number() over(partition by main_domain order by DOMAIN) rn
from SPD_DOMAIN
where IS_CHINESE_SITE = 0
and alexa_ranking = -1
and ALEXA = 0)
where rn = 1select count(distinct main_domain)
from spd_domain t
where t.alexa_ranking = -1
and t.is_chinese_site = 0
and t.alexa = 0
lz,
理想情况下你那两种查询方式返回记录总数是一样的
请看以下分析:数据1(理想数据,第一条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 0 -1 0
test.com 1 -1 0
test.com 0 -1 1 数据2(非理想数据,第二条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 1 -1 0
test.com 0 -1 0
test.com 0 -1 1执行 row_number() over(partition by main_domain order by MAIN_DOMAIN) 之后返回数据1
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 0 -1 0 1
test.com 1 -1 0 2
test.com 0 -1 1 3 数据2
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 1 -1 0 1
test.com 0 -1 0 2
test.com 0 -1 1 3查询
select count(*)
from (select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from SPD_DOMAIN) tmp
where rn=1 and IS_CHINESE_SITE = 0 and alexa_ranking = -1 and ALEXA = 0 时,数据1有记录,数据2无记录。 而查询
select count(distinct main_domain) from spd_domain t where t.is_chinese_site = 0 and t.alexa_ranking =-1 and ALEXA=0
数据1,数据2都有记录。
修改如下:;with tmp
(
select * from SPD_DOMAIN where is_chinese_site = 0 and alexa_ranking =-1 and ALEXA=0
)select count(*)
from (
select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from tmp
) tmp
where rn=1
理想情况下这两种查询返回记录总数是一样的
请看以下分析:数据1(理想数据,第一条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 0 -1 0
test.com 1 -1 0
test.com 0 -1 1 数据2(非理想数据,第二条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 1 -1 0
test.com 0 -1 0
test.com 0 -1 1执行 row_number() over(partition by main_domain order by MAIN_DOMAIN) 之后返回数据1
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 0 -1 0 1
test.com 1 -1 0 2
test.com 0 -1 1 3 数据2
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 1 -1 0 1
test.com 0 -1 0 2
test.com 0 -1 1 3查询
select count(*)
from (select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from SPD_DOMAIN) tmp
where rn=1 and IS_CHINESE_SITE = 0 and alexa_ranking = -1 and ALEXA = 0 时,数据1有记录,数据2无记录。 而查询
select count(distinct main_domain) from spd_domain t where t.is_chinese_site = 0 and t.alexa_ranking =-1 and ALEXA=0
数据1,数据2都有记录。
修改如下:;with tmp
(
select * from SPD_DOMAIN where is_chinese_site = 0 and alexa_ranking =-1 and ALEXA=0
)select count(*)
from (
select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from tmp
) tmp
where rn=1