设计的一套系统,可以对文章星级评分 最多5星
有评分表如下
userid, articleid, rating
1, 1, 5
2, 1, 3
3, 1, 3
在展示文章时希望显示5个星级各有多少人
需要得到如下数据,请教如何实现,似乎难点是为0也要显示出来
rating, count, percent
5, 1, 0.333333...
4, 0, 0
3, 2, 0.66666
2, 0, 0
1, 0, 0问下可以实现不?是在不行就在程序里处理了
有评分表如下
userid, articleid, rating
1, 1, 5
2, 1, 3
3, 1, 3
在展示文章时希望显示5个星级各有多少人
需要得到如下数据,请教如何实现,似乎难点是为0也要显示出来
rating, count, percent
5, 1, 0.333333...
4, 0, 0
3, 2, 0.66666
2, 0, 0
1, 0, 0问下可以实现不?是在不行就在程序里处理了
解决方案 »
- Microsoft Office Visio for Enterprise Architects 怎么进行数据库的双向工程?
- 如何根据sql server存储过程的返回的结果集创建一个跟结果集一样结构的临时表! 跪求了!
- 请问如何加入记录编号
- 关于使用函数的问题
- 求效率改进方案![寻找sql王(sql语句竞赛) ]姊妹篇!
- sql2005在路由器映射出去的问题。
- 这样的交叉表如何实现!!!
- 请教对数据库中某个字段数据进行加密/解密的解决方案
- 如何把字符传转换成二进制数
- 推荐一本务实的sql2000 书籍!~一定要实用,务实,详尽,易懂,把你绝的好的推荐一下!
- 查询结果值直接替换
- 求统计指定日期数量语句
select rating, count(*) as "count", count(*)/(select count(*) from tb )*100 as "percent"
from tb
group by rating
;
A.rating,
COUNT(*) AS [count],
5./COUNT(*) AS [percent]
FROM (
SELECT rating=number+1 FROM master.dbo.spt_values
WHERE type = 'p' AND number < 5
) AS A
LEFT JOIN tb AS B
ON A.rating = B.rating
GROUP BY A.rating
ORDER BY A.rating
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-21 15:33:45
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (userid INT,articleid INT,rating INT)
INSERT INTO @T
SELECT 1,1,5 UNION ALL
SELECT 2,1,3 UNION ALL
SELECT 3,1,3--SQL查询如下:SELECT
A.rating,
COUNT(B.rating) AS [count],
ISNULL(5./NULLIF((COUNT(B.rating)),0),0) AS [percent]
FROM (
SELECT rating=number+1 FROM master.dbo.spt_values
WHERE type = 'p' AND number < 5
) AS A
LEFT JOIN @T AS B
ON A.rating = B.rating
GROUP BY A.rating
ORDER BY A.rating/*
rating count percent
----------- ----------- ---------------------------------------
1 0 0.00000000000
2 0 0.00000000000
3 2 2.50000000000
4 0 0.00000000000
5 1 5.00000000000(5 行受影响)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-21 15:33:45
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (userid INT,articleid INT,rating INT)
INSERT INTO @T
SELECT 1,1,5 UNION ALL
SELECT 2,1,3 UNION ALL
SELECT 3,1,3--SQL查询如下:SELECT
A.rating,
COUNT(B.rating) AS [count],
ISNULL(NULLIF((COUNT(B.rating)),0)*1./(SELECT COUNT(*) FROM @T),0) AS [percent]
FROM (
SELECT rating=number+1 FROM master.dbo.spt_values
WHERE type = 'p' AND number < 5
) AS A
LEFT JOIN @T AS B
ON A.rating = B.rating
GROUP BY A.rating
ORDER BY A.rating/*
rating count percent
----------- ----------- ---------------------------------------
1 0 0.00000000000
2 0 0.00000000000
3 2 0.66666666666
4 0 0.00000000000
5 1 0.33333333333(5 行受影响)
*/
DECLARE @NUM INT
SET @NUM=1
WHILE @NUM<SELECT MAX(RATING) FROM TABLE1
BEGIN
INSERT TB SELECT @NUM
ENDSELECT
T.RATING,
ISNULL(COUNT(T.RATING),0) AS [COUNT],[PERCENT]=ISNULL(T.COUNT(RATING),0)/(SELECT COUNT(*) FROM TABLE1) FROM TB T LEFT JOIN TABLE1 T1
ON T.RATING=T1.RATING
多谢啊
#1的不对哦 要为0也显示出来哦
#2的不错 改下就可以实现了 count(b.rating)
不过不太理解a的from里的意思 有空查下
后面的还没看 等下看下 多谢各位
DECLARE @NUM INT
SET @NUM=1
WHILE @NUM<SELECT MAX(RATING) FROM TABLE1
BEGIN
INSERT TB SELECT @NUM
ENDSELECT
T.RATING,
ISNULL(COUNT(T.RATING),0) AS [COUNT],[PERCENT]=ISNULL(T.COUNT(RATING),0)/(SELECT COUNT(*) FROM TABLE1) FROM TB T LEFT JOIN TABLE1 T1
ON T.RATING=T1.RATINGGROUP BY T.RATING少了个GROUP BY 试试看??
if object_id('t') is not null
drop table t
create table t
(userid INT,articleid INT,rating INT)INSERT INTO t
SELECT 1,1,5 UNION ALL
SELECT 2,1,3 UNION ALL
SELECT 3,1,3
select * into #t from t
declare @count int
declare @num int
set @num = 1
while @num <= 5
begin
select @count = count(1) from #t where rating = @num
if @count <= 0
begin
insert into #t values (0,0,@num)
end
set @num = @num + 1
endselect rating, count(1) 'count',count(rating) * 1.00/(select count(1) from #t) as 'percent'
from #t
group by rating/**rating count percent
----------- ----------- ---------------------------
1 1 .1666666666666
2 1 .1666666666666
3 2 .3333333333333
4 1 .1666666666666
5 1 .1666666666666(所影响的行数为 5 行)
**/
既然没有的也要显示,把没有的补上就行了.当然是在临时表里.
declare @count int
declare @num int
set @num = 1
while @num <= 5
begin
select @count = count(1) from #t where rating = @num
if @count <= 0
begin
insert into #t values (0,0,@num)
end
set @num = @num + 1
endselect rating,
case count(1) when 1 then 0 else count(1) end 'count'
,
(case count(1) when 1 then 0 else count(1) end ) * 1.00/(select count(1) from #t) as 'percent'
from #t
group by rating/**
rating count percent
----------- ----------- ---------------------------
1 0 .0000000000000
2 0 .0000000000000
3 2 .3333333333333
4 0 .0000000000000
5 0 .0000000000000(所影响的行数为 5 行)
**/
忘case了,寒.......