select sum(isnull(part1,0)+isnull(part2,0)+isnull(part3,0)) from lzp01010
如果有N个part相加,一个一个相加也够累的。 最好还是循环相加。
动态SQL吧,用循环的话效率非常低,一条记录就循环N次。
DECLARE CUR CURSOR FOR SELECT C.NAME FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS O ON C.ID=O.ID AND O.XTYPE='U' AND O.NAME='LZP01010'OPEN CUR DECLARE @PART INT --PART字段类型 DECLARE @STRSQL NVARCHAR(4000) SET @STRSQL = 'SELECT ' FETCH NEXT FROM CUR INTO @PART WHILE @@FETCH_STATUS = 0 BEGIN SET @STRSQL = @STRSQL + 'ISNULL(' + @PART + ',0) + ' FETCH NEXT FROM CUR INTO @PART END SET @STRSQL = LEFT(@STRSQL,LEN(@STRSQL)-1) SET @STRSQL = @STRSQL + ' FROM LZP01010' EXEC SP_EXECUTESQL @STRSQL CLOSE CUR DEALLOCATE CUR
最好还是循环相加。
DECLARE CUR CURSOR FOR
SELECT C.NAME FROM SYSCOLUMNS C
INNER JOIN SYSOBJECTS O
ON C.ID=O.ID
AND O.XTYPE='U'
AND O.NAME='LZP01010'OPEN CUR
DECLARE @PART INT --PART字段类型
DECLARE @STRSQL NVARCHAR(4000)
SET @STRSQL = 'SELECT '
FETCH NEXT FROM CUR INTO @PART
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STRSQL = @STRSQL + 'ISNULL(' + @PART + ',0) + '
FETCH NEXT FROM CUR INTO @PART
END
SET @STRSQL = LEFT(@STRSQL,LEN(@STRSQL)-1)
SET @STRSQL = @STRSQL + ' FROM LZP01010'
EXEC SP_EXECUTESQL @STRSQL
CLOSE CUR
DEALLOCATE CUR