表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列求出并相加?
不知道我表达是否够明白,望各位大侠赐教啊!
需求是
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列求出并相加?
不知道我表达是否够明白,望各位大侠赐教啊!
FROM TABLENAME WHERE M0VALUE!=0 AND M0VALUE IS NOT NULL and month(staticdate)=@month and stationid = @stationid ???????
--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]
WHERE M0VALUE!=0
AND M0VALUE IS NOT NULL
and month(staticdate) in (@month1 ,(@month2 ,(@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)
WHERE M0VALUE!=0
AND M0VALUE IS NOT NULL
and month(staticdate) in (@month1 ,@month2 ,@month3 )
and stationid in (@stationid1, @stationid2,@stationid3,..)
应该
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
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,..)
SUM(CASE WHEN M0VALUE1!=0 AND M0VALUE1 IS NOT NULL THEN 1 ELSE 0 END)...FROM TB
WHERE month(staticdate)=@month and stationid = @stationid
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了呀
还是要SUM(M0VALUE)+ SUM(M1VALUE)+ ....+ SUM(M11VALUE)
??