优化SQL语句的问题

[videowawa]

表TABLENAME，列MOVALUE M1VALUE …… M11VALUE，staticdate,stationid
需求是
SELECT COUNT(M0VALUE) FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid
SELECT COUNT(M1VALUE) FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid

……
SELECT COUNT(M11VALUE) FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid

然后在把这11条值相加。
但月份有3个，stationid有多个。这样的话就需要执行N*11次SQL语句，这样性能太低了。有没有什么好办法可以不用分11次去执行，一次就可以把这11列求出并相加？
不知道我表达是否够明白，望各位大侠赐教啊！

[SQL77]

SELECT COUNT(M0VALUE)+COUNT(M1VALUE) +COUNT(M11VALUE) ..............
FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid 

???????

[黄_瓜]

[code=SQL]
--try
SELECT COUNT(M1VALUE)+COUNT(M1VALUE)+COUNT(M2VALUE)....COUNT(M11VALUE) FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid 

[/code]

[丰云]

SELECT COUNT(*) FROM TABLENAME
WHERE M0VALUE!=0
AND M0VALUE IS NOT NULL
and month(staticdate) in (@month1 ,(@month2 ,(@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)

[丰云]

上面的有多余的括号。。。

SELECT COUNT(*) FROM TABLENAME
WHERE M0VALUE!=0
AND M0VALUE IS NOT NULL
and month(staticdate) in (@month1 ,@month2 ,@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)

[videowawa]

写错了，不全是 M0VALUE!=0，
应该
SELECT COUNT(M0VALUE) FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid
SELECT COUNT(M1VALUE) FROM TABLENAME WHERE M1VALUE!=0 AND M1VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid

……
SELECT COUNT(M11VALUE) FROM TABLENAME WHERE M11VALUE!=0 AND M11VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid

[丰云]

SELECT COUNT(*) FROM TABLENAME
WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL
AND M1VALUE!=0 AND M1VALUE IS NOT NULL
.
.
.
AND M11VALUE!=0 AND M11VALUE IS NOT NULL
and month(staticdate) in (@month1 ,(@month2 ,(@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)

[SQL77]

那就这样啦,直接加起来可能还快点了,

SELCT

SUM(CASE WHEN M0VALUE!=0 AND M0VALUE IS NOT NULL THEN 1 ELSE 0 END)+
SUM(CASE WHEN M0VALUE1!=0 AND M0VALUE1 IS NOT NULL THEN 1 ELSE 0 END)...

FROM TB
WHERE month(staticdate)=@month and stationid = @stationid

[SQL77]

[Quote=引用 7 楼 sql77 的回复:]
那就这样啦,直接加起来可能还快点了,

SELCT

SUM(CASE WHEN M0VALUE!=0 AND M0VALUE IS NOT NULL  THEN 1 ELSE 0 END)+
SUM(CASE WHEN M0VALUE1!=0 AND M0VALUE1 IS NOT NULL  THEN 1 ELSE 0 END)...

FROM TB
WHERE month(staticdate)=@month and stationid = @stationid
[/Quote]
我的意思是可能就你直接那样还快点,测试一下看看

[videowawa]

SELECT COUNT(*) FROM TABLENAME
WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL
AND M1VALUE!=0 AND M1VALUE IS NOT NULL
.
.
.
AND M11VALUE!=0 AND M11VALUE IS NOT NULL
and month(staticdate) in (@month1 ,(@month2 ,(@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)

这样算出来的应该是符合M1VALUE!=0 AND M1VALUE IS NOT NULL
.
.
.
AND M11VALUE!=0 AND M11VALUE IS NOT NULL
and month(staticdate) in (@month1 ,(@month2 ,(@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)条件的有多少列，而不是M0VALUE+M1VALUE+……+M11VALUE了呀

[videowawa]

谢谢SQL77兄，我先试试看

[丰云]

你是要M0VALUE+M1VALUE+……+M11VALUE
还是要SUM(M0VALUE）+ SUM(M1VALUE）+ ....+ SUM(M11VALUE）
??

[videowawa]

需要M0VALUE+M1VALUE+……+M11VALUE

[videowawa]

count(M0VALUE)+count(M1VALUE)+……+count(M11VALUE)

[丰云]

[Quote=引用 8 楼 sql77 的回复:]
引用 7 楼 sql77 的回复:
那就这样啦,直接加起来可能还快点了,

SELCT

SUM(CASE WHEN M0VALUE!=0 AND M0VALUE IS NOT NULL  THEN 1 ELSE 0 END)+
SUM(CASE WHEN M0VALUE1!=0 AND M0VALUE1 IS NOT NULL  THEN 1 ELSE 0 END)...

FROM TB
WHERE month(staticdate)=@month and stationid = @stationid

我的意思是可能就你直接那样还快点,测试一下看看
[/Quote]
Good!