一个小问题，请有经验的朋友帮我看看是怎么回事？谢谢
我有个SQL语句总是不能返回全部的查询结果，我正百思不得其解的时候，
我偶然把SQL语句输入错了，却发现有问题如下：
SELECT *
FROM (SELECT VOD_HIS.film_name, COUNT(VOD_HIS.film_name) AS COUNT
FROM VOD_HIS
GROUP BY VOD_HIS.film_name) a, VOD_HIS b
WHERE a.FILM_NAME = b.FILM_NAME AND a.FILM_NAME = 1
ORDER BY COUNT DESC
执行的时候出现这样的错误提示：
。。。[SQL Server] 将nvarchar 值‘超能塞豆窿1’转换为数据类型为int的列时发生语法错误
超能塞豆窿1就是一个VOD_HIS.film_name
而我前面正确的SQL语句就是不能查到这个“超能塞豆窿1”的记录，请教大家是什么原因呢？这个错误的提示是否提示了我前面不能返回该查询记录的原因呢？
问题点数：20、回复次数：8Top
1 楼jml（飞翔）回复于 2004-08-01 11:25:17 得分 0
抱歉！我真是昏头了，前面的问题无效，改问下面这个问题：
在一个表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
可是查询语句分析却是错误的，我是新手，请高手指点一二，谢了！
Top
2 楼zjcxc（邹建）回复于 2004-08-01 11:25:52 得分 5
SELECT *
FROM (SELECT VOD_HIS.film_name, COUNT(VOD_HIS.film_name) AS COUNT
FROM VOD_HIS
GROUP BY VOD_HIS.film_name) a, VOD_HIS b
WHERE a.FILM_NAME = b.FILM_NAME AND a.FILM_NAME like '%1%'
ORDER BY COUNT DESCTop
3 楼jml（飞翔）回复于 2004-08-01 11:27:20 得分 0
有网友给我提供了下面的两种SQL语句，可是都不能查询完全，总是会漏掉记录，请高手指点！！！
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
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
Top
4 楼zjcxc（邹建）回复于 2004-08-01 11:28:16 得分 5
--这个语句会是正确的?? 不信
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 DESCTop
5 楼jml（飞翔）回复于 2004-08-01 11:31:22 得分 0
zjcxc(邹建) ：你好！这个语句有什么问题呢？可是我确实实现了的啊Top
6 楼tyrone98（林林）回复于 2004-08-01 11:40:36 得分 5
能用ORDER BY COUNT DESC吗，我没有试过，不知道行不行，好象要写成COUNT(FILM_NAME)才行吧.Top
7 楼guxing（孤星）回复于 2004-08-01 11:42:16 得分 5
要写成COUNT(FILM_NAME)才行Top
8 楼jml（飞翔）回复于 2004-08-01 11:52:49 得分 0
各位大侠，请帮我看看这样的语句为什么会漏掉记录好吗？
SELECT film_name, COUNT(FILM_NAME) AS COUNT
FROM VOD_HIS
WHERE EXISTS
(SELECT 1
FROM FILM_INFO
WHERE FILM_NAME = VOD_HIS.FILM_NAME AND FILM_TYPE_ID = 1)
GROUP BY film_name
ORDER BY COUNT DESC
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
Top
