表结构如下:
film表:记录了一些影片的基本信息,
id , classid, fromid ,filmname , count .....class表 类别ID对应的类别名字
classid , classnamefrom表 影片产地 ID 对应的产地名
fromid , fromnamedetailedcounter表 详细的访问记录。
id,filmid ,ipaddess ,counterdatetime
现在想在某个电影产地列表(类别列表)中显示当前产地(类别)最近30天(不是本月)的点击排行榜
比如。当前用户如果在查看类别是喜剧片,那么就列出喜剧片的点击排行榜
如果查看的是产地是 港台 那么就显示港台的排行榜。。
film表:记录了一些影片的基本信息,
id , classid, fromid ,filmname , count .....class表 类别ID对应的类别名字
classid , classnamefrom表 影片产地 ID 对应的产地名
fromid , fromnamedetailedcounter表 详细的访问记录。
id,filmid ,ipaddess ,counterdatetime
现在想在某个电影产地列表(类别列表)中显示当前产地(类别)最近30天(不是本月)的点击排行榜
比如。当前用户如果在查看类别是喜剧片,那么就列出喜剧片的点击排行榜
如果查看的是产地是 港台 那么就显示港台的排行榜。。
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
上面的回复中:
group by a.filmid
应该为:
group by a.filmid,b.filmname,c.classname,d.fromname
试着改了下。现在如下:
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语句脑袋大了。。能否再帮忙一次?
我想在加上一个条件。就是符合以上所有条件外还要符合ip = xx.xx.xx.xx的条件。不知道如何做?
(
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
得到错误
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 语句 。不知道这段语句中还有什么可以优化的么?
Msg 306, Level 16, State 2, Line 1
不能比较或排序 text、ntext 和 image 数据类型,除非使用 IS NULL 或 LIKE 运算符。
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))
order by b.counts DESC