先谢谢您的关注,请您耐心点看完这是asp程序中,查询数据库的一个语句SELECT TOP 50 a.NID, b.NID, b.PPMC, SUM(a.VID),
CEILING(SUM(a.VID * (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)))
FROM USER_COMP_VIEW a INNER JOIN
USER_COMP b ON a.NID = b.ID
WHERE (DATEDIFF(m, a.VIEWTIME, GETDATE()) = 1) AND (b.VID > 0)
GROUP BY a.NID, b.PPMC, b.NID
ORDER BY SUM(a.VID * (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)) DESCUSER_COMP表是用户表
USER_COMP_VIEW表是用户的访问记录a.NID和b.ID相同,是用户的唯一ID
b.NID是用户的一个参数
b.VID是用户是否可用
b.PPMC是用户名将访问量a.VID根据b.YID的不同乘以不同的数字(称为综合指数),然后排序列出上个月的综合指数前50名
再asp里通过
Do While Not rs.EOF
response.write rs(0)是用户ID,rs(1)是个参数,rs(2)是用户名称,rs(3)是访问量,rs(4)是综合指数
rs.MoveNext
Loop
显示出来虽然效率不咋地,但好歹能用啊,可是现在问题又来了,要同时显示出上面这些用户大上个月(DATEDIFF(m, a.VIEWTIME, GETDATE()) = 2)的排名和访问量及综合指数这是这样实现的,还是再循环里:
Do While Not rs.EOF
访问量 = conn.execute("select sum(VID) from USER_COMP_VIEW where NID="&rs(0)&" and DateDiff(m,VIEWTIME,GETDATE())=2")(0) 排名 = conn.execute("select count(distinct NID) from USER_COMP_VIEW where NID in(select a.NID from USER_COMP_VIEW a INNER JOIN USER_COMP b ON a.NID = b.ID where DateDiff(m,a.VIEWTIME,GETDATE())=2 group by a.NID having sum(a.VID*(case b.YID ... end))>sum(" & 访问量 & "*(case b.YID ... end)))")(0) + 1 rs.MoveNext
Loop也能实现,可是效率太低了,表里有5万多条数据,要7秒多才能显示出来,还是本地测试,有没整合到一起的办法呢??我刚转到sql server,很多还不懂,特来向大家请教!!
CEILING(SUM(a.VID * (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)))
FROM USER_COMP_VIEW a INNER JOIN
USER_COMP b ON a.NID = b.ID
WHERE (DATEDIFF(m, a.VIEWTIME, GETDATE()) = 1) AND (b.VID > 0)
GROUP BY a.NID, b.PPMC, b.NID
ORDER BY SUM(a.VID * (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)) DESCUSER_COMP表是用户表
USER_COMP_VIEW表是用户的访问记录a.NID和b.ID相同,是用户的唯一ID
b.NID是用户的一个参数
b.VID是用户是否可用
b.PPMC是用户名将访问量a.VID根据b.YID的不同乘以不同的数字(称为综合指数),然后排序列出上个月的综合指数前50名
再asp里通过
Do While Not rs.EOF
response.write rs(0)是用户ID,rs(1)是个参数,rs(2)是用户名称,rs(3)是访问量,rs(4)是综合指数
rs.MoveNext
Loop
显示出来虽然效率不咋地,但好歹能用啊,可是现在问题又来了,要同时显示出上面这些用户大上个月(DATEDIFF(m, a.VIEWTIME, GETDATE()) = 2)的排名和访问量及综合指数这是这样实现的,还是再循环里:
Do While Not rs.EOF
访问量 = conn.execute("select sum(VID) from USER_COMP_VIEW where NID="&rs(0)&" and DateDiff(m,VIEWTIME,GETDATE())=2")(0) 排名 = conn.execute("select count(distinct NID) from USER_COMP_VIEW where NID in(select a.NID from USER_COMP_VIEW a INNER JOIN USER_COMP b ON a.NID = b.ID where DateDiff(m,a.VIEWTIME,GETDATE())=2 group by a.NID having sum(a.VID*(case b.YID ... end))>sum(" & 访问量 & "*(case b.YID ... end)))")(0) + 1 rs.MoveNext
Loop也能实现,可是效率太低了,表里有5万多条数据,要7秒多才能显示出来,还是本地测试,有没整合到一起的办法呢??我刚转到sql server,很多还不懂,特来向大家请教!!
这样只把大上个月(DATEDIFF(m, a.VIEWTIME, GETDATE()) = 2)的访问量及综合指数整合到sql里就行了
SUM(a.VID),
CEILING(SUM(a.VID * (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)))--大上个月的访问量和综合指数
,SUM(CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 2 THEN a.VID ELSE 0 END),
CEILING(SUM(
CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 2 THEN a.VID ELSE 0 END
* (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)))FROM USER_COMP_VIEW a INNER JOIN
USER_COMP b ON a.NID = b.ID
WHERE (DATEDIFF(m, a.VIEWTIME, GETDATE()) = 1) AND (b.VID > 0)
GROUP BY a.NID, b.PPMC, b.NID
ORDER BY SUM(a.VID * (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)) DESC
,SUM(CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 1 THEN a.VID ELSE 0 END),
CEILING(SUM(
CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 1 THEN a.VID ELSE 0 END
* (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)))--大上个月的访问量和综合指数
,SUM(CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 2 THEN a.VID ELSE 0 END),
CEILING(SUM(
CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 2 THEN a.VID ELSE 0 END
* (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)))FROM USER_COMP_VIEW a INNER JOIN
USER_COMP b ON a.NID = b.ID
WHERE (DATEDIFF(m, a.VIEWTIME, GETDATE()) BETWEEN 1 AND 2) AND (b.VID > 0)
GROUP BY a.NID, b.PPMC, b.NID--按上个月的综合指数排序
ORDER BY SUM(
CASE DATEDIFF(m, a.VIEWTIME, GETDATE()) WHEN 1 THEN a.VID ELSE 0 END
* (CASE b.YID WHEN 0 THEN 1 WHEN 1 THEN 1.2 WHEN 2 THEN 1.3
WHEN 3 THEN 1.4 WHEN 4 THEN 1.5 WHEN 5 THEN 1.6 WHEN 6 THEN 1.6 WHEN 7 THEN
1.7 WHEN 8 THEN 1.8 WHEN 9 THEN 1.9 WHEN 10 THEN 2 END)) DESC
WHEN 1 THEN 1.2
WHEN 2 THEN 1.3
WHEN 3 THEN 1.4
WHEN 4 THEN 1.5
WHEN 5 THEN 1.6
WHEN 0 THEN 1
WHEN 6 THEN 1.6
WHEN 7 THEN 1.7
WHEN 8 THEN 1.8
WHEN 9 THEN 1.9
WHEN 10 THEN 2
END简化为(b.YID+0.0)/10 +
CASE
WHEN b.YID>=1 AND b.YID<=5 THEN 1.1
ELSE 1
END
通过了,呵呵,是这样因为还要显示出排名趋势,也就是上个月的排名和大上个月的排名相比较显示up或者down,能不能把大上个月的排名也整合到里面呢?
WHEN 1 THEN 1.2
WHEN 2 THEN 1.3
WHEN 3 THEN 1.4
WHEN 4 THEN 1.5
WHEN 5 THEN 1.6
WHEN 0 THEN 1
WHEN 6 THEN 1.6
WHEN 7 THEN 1.7
WHEN 8 THEN 1.8
WHEN 9 THEN 1.9
WHEN 10 THEN 2
END这里的面的值不是固定的,可以随意修改,然后通过程序生成sql
感觉sql实在太强大了,而自己知道的仅仅是冰山一角,看看要好好研究一下咯~