按我的理解,OVER是用于对聚合函数进行分区或排序的,但是一个空的OVER()有什么用呢?下面的2句得出的结果不一样,求高人解答.先谢过哈
1. select lastname, (COUNT(*)*1.0)/COUNT(*) as percentage from CurrentStudents group by lastname
2. select lastname, (COUNT(*)*1.0)/COUNT(*) over() as percentage from CurrentStudents group by lastname
1. select lastname, (COUNT(*)*1.0)/COUNT(*) as percentage from CurrentStudents group by lastname
2. select lastname, (COUNT(*)*1.0)/COUNT(*) over() as percentage from CurrentStudents group by lastname
COUNT(*),
COUNT(*) over(),
(COUNT(*)*1.0)/COUNT(*) as percentage from CurrentStudents group by lastname
这样你看看就明白了
你直接over可以不用再后面加group by.
drop table tb
go
create table tb(id int identity(1,1) primary key,name1 varchar(10))
insert into tb(name1)
select 'zzzz' union all
select 'zzzz' union all
select 'zzgg' union all
select 'zzgg' union all
select 'zzgg' union all
select 'zzgg' union all
select 'gggg' union all
select 'gggg' union all
select 'gggg' union all
select 'zzzz'
select count(*),count(*) over() from tb group by name1
结果:
3 3
4 3
3 3
row_number()
dense_rank()
排名函数都需要
運行完他的代碼后在,,
insert into tb(name1)
select 'zz11' union all
select 'zz11'
select count(*),count(*) over() from tb group by name1
再看結果
還不懂再添加,,,不同行,,,
2. select lastname, (COUNT(*)*1.0)/COUNT(*) over() as percentage from CurrentStudents group by lastname 应该理解为
2. select lastname, (COUNT(*)*1.0)/(COUNT(*) over()) as percentage from CurrentStudents group by lastname 这样就清楚了,谢谢各位!