select gid,date,s=sum(isnull(time00,0))+.. from 表 group by gid,date
try:Select Gid, N'所有' As Sid, [Date], SUM(Case IsNumeric(time00) When 0 Then 0 Else time00 End) As time00, SUM(Case IsNumeric(time01) When 0 Then 0 Else time01 End) As time01, SUM(Case IsNumeric(time02) When 0 Then 0 Else time02 End) As time02, --... SUM(Case IsNumeric(time23) When 0 Then 0 Else time23 End) As time23 From TableName Group By Gid,[Date]
用IsNumeric來判斷是否是數字。
晕,难道真的要把time00到time23都写一遍吗?
想偷懶的話,用動態語句 Declare @S Nvarchar(1000) Set @S='' Select @S=',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name From SysColumns Where ID=OBJECT_ID('TableName') And Name Like 'time%' Order By ColID Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From TableName Group By Gid,[Date]' EXEC(@S)
强人们啊,我把这条语句放上去 --------- Declare @S Nvarchar(1000) Set @S='' Select @S=',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name From SysColumns Where ID=OBJECT_ID('TableName') And Name Like 'time%' Order By ColID Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From TableName Group By Gid,[Date]' EXEC(@S)-------只查找time23的值啊,00-22不见了,还有,我不只是要查找总和,我还要查找单个的SID对应的值,就像一楼显示的那样,第段相同的GID的有一次汇总
--哈,丟了點東西,再測試下。Declare @S Nvarchar(1000) Set @S='' Select @S=@S+',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name From SysColumns Where ID=OBJECT_ID('TableName') And Name Like 'time%' Order By ColID Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From TableName Group By Gid,[Date]' EXEC(@S)
--建立測試環境 Create Table ServerOlNum (Gid Int, Sid Int, [Date] Varchar(10), time00 Int, time01 Int, time23 Int) Insert ServerOlNum Select 1, 1, '2006-05-24', 10, 5, 30 Union All Select 1, 2, '2006-05-24', 6, 3, 2 GO --測試 Declare @S Nvarchar(1000) Set @S='' Select @S=@S+',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name From SysColumns Where ID=OBJECT_ID('ServerOlNum') And Name Like 'time%' Order By ColID Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From ServerOlNum Group By Gid,[Date]' EXEC(@S) GO --刪除測試環境 Drop Table ServerOlNum --結果 /* Gid Gid Date time00 time01 time23 1 所有 2006-05-24 16 8 32 */
select gid,date,s=sum(isnull(time00,0))+..
from 表
group by gid,date
Gid,
N'所有' As Sid,
[Date],
SUM(Case IsNumeric(time00) When 0 Then 0 Else time00 End) As time00,
SUM(Case IsNumeric(time01) When 0 Then 0 Else time01 End) As time01,
SUM(Case IsNumeric(time02) When 0 Then 0 Else time02 End) As time02,
--...
SUM(Case IsNumeric(time23) When 0 Then 0 Else time23 End) As time23
From TableName
Group By Gid,[Date]
Declare @S Nvarchar(1000)
Set @S=''
Select @S=',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name
From SysColumns Where ID=OBJECT_ID('TableName') And Name Like 'time%' Order By ColID
Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From TableName Group By Gid,[Date]'
EXEC(@S)
---------
Declare @S Nvarchar(1000)
Set @S=''
Select @S=',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name
From SysColumns Where ID=OBJECT_ID('TableName') And Name Like 'time%' Order By ColID
Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From TableName Group By Gid,[Date]'
EXEC(@S)-------只查找time23的值啊,00-22不见了,还有,我不只是要查找总和,我还要查找单个的SID对应的值,就像一楼显示的那样,第段相同的GID的有一次汇总
Set @S=''
Select @S=@S+',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name
From SysColumns Where ID=OBJECT_ID('TableName') And Name Like 'time%' Order By ColID
Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From TableName Group By Gid,[Date]'
EXEC(@S)
我把表导为excel格式(不知应导哪种格式好)
下载:http://ad.hello178.com/1.xls
(把过虑广告关了就可下,应为有ad字符)
ServerOlNum为数据表,Sheet为应导出的结果表,(同一GameID有2行以上的就要求总)
先谢谢各位大侠
Create Table ServerOlNum
(Gid Int,
Sid Int,
[Date] Varchar(10),
time00 Int,
time01 Int,
time23 Int)
Insert ServerOlNum Select 1, 1, '2006-05-24', 10, 5, 30
Union All Select 1, 2, '2006-05-24', 6, 3, 2
GO
--測試
Declare @S Nvarchar(1000)
Set @S=''
Select @S=@S+',SUM(Case IsNumeric('+Name+') When 0 Then 0 Else '+Name+' End) As '+Name
From SysColumns Where ID=OBJECT_ID('ServerOlNum') And Name Like 'time%' Order By ColID
Select @S='Select Gid,N''所有'' As Sid,[Date]'+@S+' From ServerOlNum Group By Gid,[Date]'
EXEC(@S)
GO
--刪除測試環境
Drop Table ServerOlNum
--結果
/*
Gid Gid Date time00 time01 time23
1 所有 2006-05-24 16 8 32
*/