请教一个SQL语句

[jml]

在一个表VOD_HIS中，可以实现以下操作
SELECT film_name, COUNT(FILM_NAME) AS COUNT FROM VOD_HIS where ORDER_TIME BETWEEN 0：0：0 and 23：59：59 GROUP BY film_name ORDER BY COUNT DESC
该操作对表中film_name字段内容相同的记录进行统计，并对统计结果排行

现在我要实现另一个功能，在上面的统计结果中，我还要通过film_name查询另一个表FILM_INFO，使得上面的统计结果必须满足表FILM_INFO的FILM_TYPE_ID字段必须等于一个特定的值1，我的SQL语句是这样写的
SELECT film_name, COUNT(FILM_NAME) AS COUNT FROM VOD_HIS,FILM_INFO WHERE VOD_HIS.FILM_NAME=FILM_INFO.FILM_NAME AND FILM_TYPE_ID=1 GROUP BY film_name ORDER BY COUNT DESC

可是查询语句分析却是错误的，我是新手，请高手指点一二，谢了！

[LoveSQL]

这样试试
select * from (
SELECT film_name, COUNT(FILM_NAME) AS COUNT FROM VOD_HIS where ORDER_TIME BETWEEN '0：0：0' and '23：59：59' GROUP BY film_name
)a,VOD_HIS b
where a.FILM_NAME=b.FILM_NAME and b.FILM_TYPE_ID=1
ORDER BY COUNT DESC

[pengda1i]

SELECT film_name, COUNT(FILM_NAME) AS COUNT FROM VOD_HIS
where ORDER_TIME BETWEEN '0：0：0' and '23：59：59'
and exists (
select 1 from FILM_INFO
where FILM_NAME=VOD_HIS.FILM_NAME
and FILM_TYPE_ID=1
)
GROUP BY film_name

[pengda1i]

一楼的语句就怕结果产生重复

[jml]

LoveSQL(GG),好象不行，显示是列名FILM_TYPE_ID无效

[MagicYangCN]

你的在一楼的SQL语句的错误应该是
"列名 'FILM_NAME' 不明确。"
因为你的两个表中有同名的字段'FILM_NAME' ，你需要指定是那个表的'FILM_NAME' 字段

试试这个语句：
SELECT VOD_HIS.film_name, COUNT(VOD_HIS.film_name) AS COUNT FROM VOD_HIS,FILM_INFO WHERE VOD_HIS.FILM_NAME=FILM_INFO.FILM_NAME AND FILM_TYPE_ID=1 GROUP BY VOD_HIS.film_namee ORDER BY COUNT DESC

[MagicYangCN]

语句最好一行的VOD_HIS.film_namee多了一个e
应该是VOD_HIS.film_name

[jml]

MagicYangCN(魔术师·杨) ，试了下，可以实现，十分感谢！

[pengda1i]

晕

结果能对？