可用分押宝游戏火热进行中...	专题改版：Java Web 专题

这个SQL搞得我头都昏了，兄弟们帮帮我。(100分)

楼主renliquan（狗狗）2005-06-01 09:46:13 在 MS-SQL Server / 基础类提问

我有两个表：

表MovHits：Hid字段 HitDate字段

表Mov：MovID字段 MovName字段 MovTitle字段 PicURL字段 MovPlayTime字段 MovContent字段

MovHits表用来记录电影的点击量，每点击一次电影就向MovHits表里写一个HID(对应电影MovID)和点击时间。
Mov表是电影表。

我现在想得到：
1、一个星期内电影点击排行榜(按点击量排序)？
2、一个月内电影电击排行榜(按点击量排序)？
3、一个星期内点击数占总点击数的百分比？
4、Mov.MovID为100的当天的点击数？
5、Mov.MovID为100的所有点击数占总点击数的百分比？

(结果除了要显示点击数还要要显示Mov里的所有字段) 问题点数：100、回复次数：9Top

1 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-06-01 10:01:33 得分 40

1、
select
a.MovID,
a.MovName,
a.MovTitle,
a.PicURL,
a.MovPlayTime,
a.MovContent,
isnull(count(b.HitDate),0)
from
Mov a
left join
MovHits b
on
a.MovID = b.Hid
where
datediff(wk,HitDate,getdate()) = 0
group by
a.MovID,a.MovName,a.MovTitle,a.PicURL,a.MovPlayTime,a.MovContent
order by
isnull(count(b.HitDate),0) desc

2、
select
a.MovID,
a.MovName,
a.MovTitle,
a.PicURL,
a.MovPlayTime,
a.MovContent,
isnull(count(b.HitDate),0)
from
Mov a
left join
MovHits b
on
a.MovID = b.Hid
where
datediff(mm,HitDate,getdate()) = 0
group by
a.MovID,a.MovName,a.MovTitle,a.PicURL,a.MovPlayTime,a.MovContent
order by
isnull(count(b.HitDate),0) descTop

2 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-06-01 10:06:47 得分 0

3、
select
a.MovID,
a.MovName,
a.MovTitle,
a.PicURL,
a.MovPlayTime,
a.MovContent,
percent = cast(isnull(count(b.HitDate),0)*100.0/(select isnull(count(*),1.0) from MovHits where Hid = a.MovID) as numeric(5,2))
from
Mov a
left join
MovHits b
on
a.MovID = b.Hid
where
datediff(wk,b.HitDate,getdate()) = 0
group by
a.MovID,a.MovName,a.MovTitle,a.PicURL,a.MovPlayTime,a.MovContent
order by
isnull(count(b.HitDate),0) desc

4、
select
a.MovID,
a.MovName,
a.MovTitle,
a.PicURL,
a.MovPlayTime,
a.MovContent,
isnull(count(b.HitDate),0)
from
Mov a
left join
MovHits b
on
a.MovID = b.Hid
where
datediff(dd,b.HitDate,getdate()) = 0
and
a.MovID = 100
group by
a.MovID,a.MovName,a.MovTitle,a.PicURL,a.MovPlayTime,a.MovContentTop

3 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-06-01 10:15:25 得分 0

5、
select
a.MovID,
a.MovName,
a.MovTitle,
a.PicURL,
a.MovPlayTime,
a.MovContent,
percent = cast(isnull(count(b.HitDate),0)*100.0/(select isnull(count(*),1.0) from MovHits) as numeric(5,2))
from
Mov a
left join
MovHits b
on
a.MovID = b.Hid
where
a.MovID = 100
group by
a.MovID,a.MovName,a.MovTitle,a.PicURL,a.MovPlayTime,a.MovContentTop

4 楼duanduan1122（俺村俺帅！！！）回复于 2005-06-01 10:22:14 得分 5

应该能解决了吧。Top

5 楼xluzhong（Ralph）回复于 2005-06-01 11:33:41 得分 55

1.
select a.*,b.hittimes
from mov a
inner join (
select hid,count(*) as hittimes
from movhits where datediff(wk,hitdate,getdate())=0
group by hid
)b
on a.movid=b.hid
order by b.hittimes desc
2.
select a.*,b.hittimes
from mov a
inner join (
select hid,count(*) as hittimes
from movhits where datediff(m,hitdate,getdate())=0
group by hid
)b
on a.movid=b.hid
order by b.hittimes descTop

6 楼xluzhong（Ralph）回复于 2005-06-01 11:40:31 得分 0

3.
select a.*,b.hittimes,c.sumhittimes,cast((b.hittimes*1.0/c.sumhittimes)*100 as nvarchar(10))+'%'
from mov a
inner join (
select hid,count(*) as hittimes
from movhits where datediff(wk,hitdate,getdate())=0
group by hid
)b
on a.movid=b.hid
inner join (
select hid,count(*) as sumhittimes
from movhits
group by hid
)c
on a.movid=c.hidTop

7 楼xluzhong（Ralph）回复于 2005-06-01 11:41:53 得分 0

4.
select a.*,b.hittimes
from mov a
inner join (
select hid,count(*) as hittimes
from movhits where datediff(d,hitdate,getdate())=0
group by hid
)b
on a.movid=b.hid
where movid='100'

5.
select a.*,b.hittimes,c.sumhittimes,cast((b.hittimes*1.0/c.sumhittimes)*100 as nvarchar(10))+'%'
from mov a
inner join (
select hid,count(*) as hittimes
from movhits where datediff(wk,hitdate,getdate())=0
group by hid
)b
on a.movid=b.hid
inner join (
select hid,count(*) as sumhittimes
from movhits
group by hid
)c
on a.movid=c.hid
where movid='100'Top

8 楼xluzhong（Ralph）回复于 2005-06-01 11:43:56 得分 0

5.---看错了,改一下
select a.*,b.hittimes,c.sumhittimes=(select count(*) from movhits),cast((b.hittimes*1.0/c.sumhittimes)*100 as nvarchar(10))+'%'
from mov a
inner join (
select hid,count(*) as hittimes
from movhits
group by hid
)b
on a.movid=c.hid
where movid='100'Top

9 楼renliquan（狗狗）回复于 2005-06-01 14:55:33 得分 0

xluzhong(Ralph)，第五个和第三个不对，报错：
第三个报错：
服务器: 消息 8115，级别 16，状态 2，行 1
将 expression 转换为数据类型 nvarchar 时发生算术溢出错误。
第五个报错：
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '=' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: ',' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 7
第 7 行: 'b' 附近有语法错误。

libin_ftsafe(子陌红尘) ，你的语句要报这个错：
服务器: 消息 206，级别 16，状态 2，行 1
操作数类型冲突: int 与 void type 不兼容
服务器: 消息 306，级别 16，状态 1，行 1
不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。

不过已经解决了1、2、4、这三个问题了。
Top