求解一条电影网站的月电影点击统计Sql语句

[boyyao]

表结构如下：
film表：记录了一些影片的基本信息，
id , classid, fromid ,filmname , count .....

class表 类别ID对应的类别名字
classid ， classname

from表 影片产地 ID 对应的产地名
fromid ， fromname

detailedcounter表 详细的访问记录。
id，filmid ，ipaddess ，counterdatetime


现在想在某个电影产地列表（类别列表）中显示当前产地（类别）最近30天（不是本月）的点击排行榜
比如。当前用户如果在查看类别是喜剧片，那么就列出喜剧片的点击排行榜
如果查看的是产地是 港台 那么就显示港台的排行榜。。

[hellowork]

SELECT a.filmid,b.filmname,c.classname,d.fromname ,count(*) as counts
FROM detailedcounter as a
left join film as b on a.filmid = b.id
left join class as c on b.classid = c.classid
left join [from] as d on b.fromid = d.fromid
WHERE datediff(dd,a.counterdatetime,getdate()) < 30
and c.classname = '喜剧'
and d.fromname = '港台'
group by a.filmid
order by counts DESC

[hellowork]

抱歉,更正一下.
上面的回复中:
group by a.filmid
应该为:
group by a.filmid,b.filmname,c.classname,d.fromname

[boyyao]

非常感谢。。问题基本解决了。。当时为了提问方便。数据库字段等都简略了。我
试着改了下。现在如下：
SELECT DetailedCounterbyIP.film_id,films.film_name,film_class.caption,from_list.film_from ,count(*) as counts
FROM DetailedCounterbyIP as DetailedCounterbyIP
left join films as films on DetailedCounterbyIP.film_id = films.film_id
left join film_class as film_class on films.film_class = film_class.id
left join from_list as from_list on films.film_from = from_list.id
WHERE datediff(dd,DetailedCounterbyIP.counterdatetime,getdate()) < 30
and film_class.caption = '喜剧'
and from_list.film_from = '港台'
group by DetailedCounterbyIP.film_id,films.film_name,film_class.caption,from_list.film_from
order by counts DESC

但是我是sql 菜鸟。。平时都用写很基本的sql语句。
我想要的结果是
列出符合条件的 films表中的所有字段的数据。也就是类似 top 10 * 这样的方法，并且不想要把影片类型和影片产地关联起来。直接输出id号。

看着那么复杂的sql语句脑袋大了。。能否再帮忙一次？

[boyyao]

另外。。还有一个问题。就是DetailedCounterbyIP 表中有一个ip字段。
我想在加上一个条件。就是符合以上所有条件外还要符合ip = xx.xx.xx.xx的条件。不知道如何做？

[boyyao]

再次感谢 hellowork 。。

我用你再次提供的语句在sql查询里面查询。
得到错误
Msg 4104, Level 16, State 1, Line 18
无法绑定由多个部分组成的标识符 "films.film_class"。
Msg 4104, Level 16, State 1, Line 18
无法绑定由多个部分组成的标识符 "films.film_from"。


films.film_class和films.film_from 是films中的两个字段没错的。

不过我已经用其他的方法完成了我的要求。我只是想请教一下而已。我现在用如下语句基本达到了我的要求。

/* 需要什么字段手工加入，并且在Group by中也加入 */
SELECT top 10 films.film_class,films.film_player ,DetailedCounterbyIP.film_id,films.film_name,film_class.caption,
from_list.film_from ,count(*) as counts
FROM DetailedCounterbyIP as DetailedCounterbyIP
left join films as films on DetailedCounterbyIP.film_id = films.film_id
left join film_class as film_class on films.film_class = film_class.id
left join from_list as from_list on films.film_from = from_list.id
WHERE datediff(dd,DetailedCounterbyIP.counterdatetime,getdate()) < 30
and film_class.caption = '喜剧'
and from_list.film_from = '港台'
AND ((films.is_private = 0) OR
(films.film_ID IN (SELECT films_ID FROM netbar_private WHERE (netbar_id = 2)))) AND (films.film_ID NOT IN
(SELECT films_ID FROM netbar_exclude WHERE (netbar_id = 2)))
group by DetailedCounterbyIP.film_id,films.film_name,film_class.caption,from_list.film_from,films.film_player,films.film_class
order by counts DESC

我还想问的是。我在后面加入了一个 and 语句 。不知道这段语句中还有什么可以优化的么？

[boyyao]

又遇一个问题。我想在我现在准备要用的语句当中SELECT top 10 后面加入一个films.film_gut字段.并且在gorup by中也加入了films.film_gut 但是提示
Msg 306, Level 16, State 2, Line 1
不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。

[hellowork]

select top 10 films.* from films inner join /*点击率前10名的电影信息*/
(
SELECT DetailedCounterbyIP.film_id,count(*) as counts
FROM DetailedCounterbyIP as DetailedCounterbyIP
left join film_class as film_class on films.film_class = film_class.id
left join from_list as from_list on films.film_from = from_list.id
WHERE datediff(dd,DetailedCounterbyIP.counterdatetime,getdate()) < 30
and film_class.caption = '喜剧'
and from_list.film_from = '港台'
AND DetailedCounterbyIP.ipaddess = 'xx.xx.xx.xx' /*在此指定IP地址*/
group by DetailedCounterbyIP.film_id
) as t on films.film_id = t.film_id
order by t.counts DESC

[hellowork]

建议楼主养成使用表别名的习惯.这样试试:
select top 10 a.film_class,a.film_player,b.film_id,a.film_name,c.caption,d.film_from,b.counts
from films as a
inner join
(select film_id,count(*) as counts from DetailedCounterbyIP
where datediff(dd,counterdatetime,getdate()) < 30
group by film_id) as b
on a.film_id = b.film_id
left join film_class as c on a.film_class = c.id
left join from_list as d on a.film_from = d.id
WHERE c.caption = '喜剧'
and d.film_from = '港台'
and (a.is_private = 0) OR
(a.film_ID IN (SELECT films_ID FROM netbar_private WHERE (netbar_id = 2))) AND (a.film_ID NOT IN
(SELECT films_ID FROM netbar_exclude WHERE (netbar_id = 2) AND films_ID IS NOT NULL))

[hellowork]

抱歉,落了下面一行排序的代码:
order by b.counts DESC

[boyyao]

非常感谢。。区区20分。聊表心意。。谢谢了。