求一条SQL语句，应该、估计很简单-_-

[songjinxi123]

我有一个表
表名：table1
Column1
default.aspx
index.aspx
a1.rar
a2.rar
a3.exe
a4.jpg
show.html
default.aspx
index.aspx
......

(其他列省略了，主要是这个列)
我要统计Column1 中每个不一样的值出现的次数。
于是我按组搜索select count(*) AS Total,Column1 from GROUP BY Column1 ORDER BY Total DESC

问题来了，这样搜索出来包含了“default.aspx”“show.html”这些值，但是我不想要这些网页文件的次数，我只想要“a1.rar”这种非网页文件出现的次数，请问SQL该怎么写？

[songjinxi123]

刚刚那个SQL语句写错了
select count(*) AS Total,Column1 from table1 GROUP BY Column1 ORDER BY Total DESC

[soft_wsx]

吃了饭再说

[华夏小卒]

select count(*) AS Total,Column1
from table1
where charindex('.html',Column1)=0 and charindex('.aspx',Column1)=0
GROUP BY Column1 
ORDER BY Total DESC 

[kingfirsts]

应是这样写select count(*) AS Total,Column1 from table1 where Column1 not like "%html%" ... GROUP BY Column1 ORDER BY Total DESC

[SQL77]

SELECT COUNT(*) FROM TBALE1 WHERE COLUMN1 LIKE'%HTML%' OR LIKE'%ASPX%'

[songjinxi123]

where charindex('.html',Column1)=0 and charindex('.aspx',Column1)=0
和
not like "%html%"

哪个效率高点？

[soft_wsx]

第一个吧1

[guguda2008]

[Quote=引用 6 楼 songjinxi123 的回复:]
where charindex('.html',Column1)=0 and charindex('.aspx',Column1)=0
和
not like "%html%"

哪个效率高点？
[/Quote]
听说是一样的，用第一个吧

[soft_wsx]

很少用NOT LIKE

[Zoezs]

select count(*) AS Total,Column1 from table1 
    WHERE CHARINDEX('.HTML',Column1)<1 AND CHARINDEX('.ASPX',COLUMN1)<1
    GROUP BY Column1 ORDER BY Total DESC 

[feixianxxx]

没多大区别

[feixianxxx]

[Quote=引用 10 楼 zoezs 的回复:]
SQL codeselectcount(*)AS Total,Column1from table1WHERECHARINDEX('.HTML',Column1)<1ANDCHARINDEX('.ASPX',COLUMN1)<1GROUPBY Column1ORDERBY TotalDESC
[/Quote]

最好都用CHARINDEX 不要用LIKE

[SQL77]

[Quote=引用 6 楼 songjinxi123 的回复:]
where charindex('.html',Column1)=0 and charindex('.aspx',Column1)=0
和
not like "%html%"

哪个效率高点？
[/Quote]
没什么区别,的