如何对三个或更多int列求和 [英] How To Sum Three or More int Columns

问题描述

我有一张桌子. SID声明为nvarchar，其余列声明为int数据类型. int列随机填充有1、2和3的不同值.我的挑战是如何创建一个视图或子过程，以逐行将1s，2s和3s相加以产生单独的结果列.

I have a Table. The SID is declared nvarchar and the rest of the columns are declared with int datatypes. The int columns are populated with varying values of 1, 2 & 3 at random. My challenge is how to create a view or subprocedure that will add up the 1s, the 2s and the 3s Row by Row to produce separate result columns.

推荐答案

不清楚，但我已经举了一个例子.

我使用 PIVOT [

Still is not clear, but i''ve made an example.

I use PIVOT[^] statement to count.

Copy below code into MS SQK Managment Studio and paste into new query, than run.

IF NOT OBJECT_ID(N'#Weather') IS NULL 
	DROP TABLE #Weather

CREATE TABLE #Weather ([SID] VARCHAR(30), [TimeOfDay] VARCHAR(30), [Weather] VARCHAR(30))

INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Morning','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Evening','Dew Cover')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Morning','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Evening','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Morning','Dew Cover')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Morning','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Evening','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Morning','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Evening','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Morning','Dew Cover')

DECLARE @cols VARCHAR(200)
DECLARE @dt VARCHAR(400)
DECLARE @pt VARCHAR(1000)

SET @cols = '[Dew Cover],[Rainfall],[Sun]'
SET @dt = 'SELECT * ' +
		'FROM #Weather '
SET @pt = 'SELECT [SID], [TimeOfDay], ' + @cols + ' ' + 
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT (COUNT([Weather]) FOR [Weather] IN(' + @cols + ')) AS PT ' + 
		'ORDER BY PT.[SID]'
EXEC( @pt)

DROP TABLE #Weather

如有任何疑问，请致电;)

If you have any question, call ;)

这篇关于如何对三个或更多int列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！