sql查詢,投票的資料庫中如何統計票數並排名呢?
樓上的語句沒有毛病,不過摟主提供了userinfo表,這裡如果某個使用者沒有得票的話,就統計不上了,還是join一把比較好點。另外直接查詢的話結果體現不出來名次
select identity(int,1,1) as [名次],a。username as [使用者名稱],count(*) as [票數]
into #temporders
from userinfo a left join votes b
on a。username=b。username
group by a。username
order by [票數] desc
select *
from #temporders
order by [名次]
你好!
用外連線實現,如下:
create table person
(perno varchar(8) not null)
create table temp
(perno varchar(8) not null,
cou integer not null
)
insert into person
select ‘A ’
union
select ‘B’
union
select ‘C’
union
select ‘D’
insert into temp
select ‘A’,1
union all
select ‘A’,1
union
select ‘B’,1
union
select ‘C’,1
select * from temp
select a。perno,COUNT(b。perno)as ‘票數’ from person a left join temp b on a。perno = b。perno
group by a。perno;
顯示結果連包括0票的人員都會統計出來。
我的回答你還滿意嗎~~
用count以及rank等分析函式
select username,count(*) from votes group by username order by count(*) desc