如题： 有两个表一个为报警表 一个产品表
需要实现 按产品类型和产品编号 根据时间段 统计报警状态每天发生的次数
表结构如下：
/*
UnitID 产品编号
alarmID 报警状态
alarmName 报警名称
*/
drop table TbAlarm
CREATE TABLE TbAlarm (time datetime,UnitID int,alarmID int,alarmName varchar(20),alarmDescrise varchar(100))
insert TbAlarm
select '2010-08-02 14:10:36',129,1,'正常','说明：正常运行' union all
select '2010-08-02 14:11:26',129,2,'停止','说明：停止运行'union all
select '2010-08-04 14:11:16',129,3,'暂停','说明：暂停运行' union all
select '2010-08-05 15:10:36',129,5,'自动维护','自动维护' union all
select '2010-08-08 10:10:36',129,6,'手动维护','手动维护' union all
select '2010-08-12 14:10:36',129,8,'与服务器断开','与服务器断开' union all
select '2010-08-02 12:10:36',130,1,'正常','说明：正常运行'union all
select '2010-08-02 14:10:36',130,7,'电池已满','电池已满'union all
select '2010-08-05 10:10:36',130,8,'与服务器断开','与服务器断开'union all
select '2010-08-08 8:10:36',130,9,'短路','短路'union all
select '2010-08-10 11:10:36',130,10,'接入','接入'union all
select '2010-08-12 21:10:36',130,12,'复位','复位'union all
select '2010-08-02 07:10:36',131,2,'停止',''
drop table Device
CREATE TABLE Device (DeviceID int,DeviceName varchar(8),DType varchar(20))
insert Device
select 115,'产品D','D21型' union all
select 129,'产品A','A23C型' union all
select 130,'产品A2','A23D型' union all
select 131,'产品J','J3F型' union all
select 132,'产品D','D1K型' union all
select 133,'产品C','C12型' union all
select 134,'产品B','B2F型' union all
select 135,'产品G','GK3型'
我之前写了一个 不区分产品类型 将所有状态写死的 统计
select convert(varchar(10),[time],23) as'日期' ,
sum(case AlarmID when '1' then 1 else 0 end)as'正常(次)',
sum(case AlarmID when '2' then 1 else 0 end)as'停止(次)',
sum(case AlarmID when '3' then 1 else 0 end)as'暂停(次)',
sum(case AlarmID when '5' then 1 else 0 end)as'自动维护(次)',
sum(case AlarmID when '6' then 1 else 0 end)as'手动维护(次)',
sum(case AlarmID when '7' then 1 else 0 end)as'电池已满(次)',
sum(case AlarmID when '8' then 1 else 0 end)as'与服务器断开(次)',
sum(case AlarmID when '9' then 1 else 0 end)as'短路(次)',
sum(case AlarmID when '10' then 1 else 0 end)as'接入(次)',
sum(case AlarmID when '12' then 1 else 0 end)as'复位(次)',
count(AlarmID) as '总计' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where [time] between'2010-8-2' and '2010-8-13' and unitID =129
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
统计结果如下：
日期 正常(次) 停止(次) 暂停(次) 自动维护(次) 手动维护(次) 电池已满(次)
与服务器断开(次) 短路(次) 接入(次) 复位(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 0 0 0 0 2
2010-08-04 0 0 1 0 0 0 0 0 0 0 1
2010-08-05 0 0 0 1 0 0 0 0 0 0 1
2010-08-08 0 0 0 0 1 0 0 0 0 0 1
2010-08-12 0 0 0 0 0 0 1 0 0 0 1
根据类型查询报警状态
SELECT TOP (100) PERCENT dbo.TbAlarm.alarmID,dbo.TbAlarm.alarmName
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = 'A23D型')
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
alarmID alarmName
----------- --------------------
1 正常
7 电池已满
8 与服务器断开
9 短路
10 接入
12 复位
需要实现的效果
如 2010-08-02 到2010-08-13 类型为'A23C型' 编号为 129
统计结果如下：
日期 正常(次) 停止(次) 暂停(次) 自动维护(次) 手动维护(次) 与服务器断开(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 2
2010-08-04 0 0 1 0 0 0 1
2010-08-05 0 0 0 1 0 0 1
2010-08-08 0 0 0 0 1 0 1
2010-08-12 0 0 0 0 0 1 1
如 2010-08-02 到2010-08-13 类型为 'A23D型' 编号为 130
统计结果如下：
日期 正常(次) 电池已满(次) 与服务器断开(次) 短路(次) 接入(次) 复位(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 2
2010-08-05 0 0 1 0 0 0 1
2010-08-08 0 0 0 1 0 0 1
2010-08-10 0 0 0 0 1 0 1
2010-08-12 0 0 0 0 0 1 1
16 个解决方案
#1
select convert(varchar(10),[time],23) as'日期' ,UnitID,
sum(case AlarmID when '1' then 1 else 0 end)as'正常(次)',
sum(case AlarmID when '2' then 1 else 0 end)as'停止(次)',
sum(case AlarmID when '3' then 1 else 0 end)as'暂停(次)',
sum(case AlarmID when '5' then 1 else 0 end)as'自动维护(次)',
sum(case AlarmID when '6' then 1 else 0 end)as'手动维护(次)',
sum(case AlarmID when '7' then 1 else 0 end)as'电池已满(次)',
sum(case AlarmID when '8' then 1 else 0 end)as'与服务器断开(次)',
sum(case AlarmID when '9' then 1 else 0 end)as'短路(次)',
sum(case AlarmID when '10' then 1 else 0 end)as'接入(次)',
sum(case AlarmID when '12' then 1 else 0 end)as'复位(次)',
count(AlarmID) as '总计' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.UnitID = dbo.Device.DeviceID
where [time] between'2010-8-2' and '2010-8-13'
group by convert(varchar(10),[time],23) ,UnitID
Order by UnitID,convert(varchar(10),[time],23) asc
不写死要怎么写？动态的？
#2
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + CONVERT(VARCHAR,T.AlarmID) from (select TbAlarm.* FROM TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where [time] between'2010-8-2' and '2010-8-13' and Device.dtype = 'A23D型')T group by CONVERT(VARCHAR,T.AlarmID)
set @sql = '[' + @sql + ']'
exec ('select * from (select time,unitid,alarmid from TbAlarm) a
pivot (max(unitid) for AlarmID in (' + @sql + ')) b')
try
lz结合一下自己的应用，然后case一下列名即可
#3
要的效果是?
#4
DECLARE @unitID INT
DECLARE @begin DATETIME
DECLARE @end DATETIME
SET @unitID = 129
SET @begin = '2010-8-2'
SET @end = '2010-8-13'
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT CONVERT(VARCHAR(10),A.[time],23) AS [日期]'
SELECT @sql = @sql + ',SUM(CASE WHEN A.alarmName = '''+ alarmName + ''' THEN 1 ELSE 0 END) AS [' + alarmName + ']'
FROM (SELECT DISTINCT alarmName FROM dbo.TbAlarm
INNER JOIN dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE unitID = @unitID)AS Temp
SELECT @sql = @sql + ' FROM dbo.TbAlarm A INNER JOIN dbo.Device B ON A.unitID = B.deviceID
WHERE A.[time] BETWEEN ''' + CONVERT(VARCHAR(10),@begin,23) + ''' AND ''' + CONVERT(VARCHAR(10),@end,23)
+ ''' AND A.unitID = ' + CAST(@unitID AS VARCHAR(10)) + ' GROUP BY CONVERT(VARCHAR(10),A.[time],23)
ORDER BY CONVERT(VARCHAR(10),A.[time],23) ASC'
--SELECT @sql
EXEC(@sql)
#5
恩 是的 就是要根据 类型 Dtype 来实现动态的
#6
拜托大哥 运行出来 完全不是我想要的结果
#7
运行结果不对
#8
额 学习一下
#9
/*
UnitID 产品编号
alarmID 报警状态
alarmName 报警名称
*/
drop table TbAlarm
CREATE TABLE TbAlarm (time datetime,UnitID int,alarmID int,alarmName nvarchar(20),alarmDescrise nvarchar(100))
insert TbAlarm
select '2010-08-02 14:10:36',129,1,N'正常',N'说明：正常运行' union all
select '2010-08-02 14:11:26',129,2,N'停止',N'说明：停止运行'union all
select '2010-08-04 14:11:16',129,3,N'暂停',N'说明：暂停运行' union all
select '2010-08-05 15:10:36',129,5,N'自动维护',N'自动维护' union all
select '2010-08-08 10:10:36',129,6,N'手动维护',N'手动维护' union all
select '2010-08-12 14:10:36',129,8,N'与服务器断开',N'与服务器断开' union all
select '2010-08-02 12:10:36',130,1,N'正常',N'说明：正常运行'union all
select '2010-08-02 14:10:36',130,7,N'电池已满',N'电池已满'union all
select '2010-08-05 10:10:36',130,8,N'与服务器断开',N'与服务器断开'union all
select '2010-08-08 8:10:36',130,9,N'短路',N'短路'union all
select '2010-08-10 11:10:36',130,10,N'接入',N'接入'union all
select '2010-08-12 21:10:36',130,12,N'复位',N'复位'union all
select '2010-08-02 07:10:36',131,2,N'停止',N''
drop table Device
CREATE TABLE Device (DeviceID int,DeviceName nvarchar(8),DType nvarchar(20))
insert Device
select 115,N'产品D',N'D21型' union all
select 129,N'产品A',N'A23C型' union all
select 130,N'产品A2',N'A23D型' union all
select 131,N'产品J',N'J3F型' union all
select 132,N'产品D',N'D1K型' union all
select 133,N'产品C',N'C12型' union all
select 134,N'产品B',N'B2F型' union all
select 135,N'产品G',N'GK3型'
DECLARE @s NVARCHAR(4000),@Para1 NVARCHAR(100)--,@Dt1 DATETIME,@Dt2 DATETIME
SELECT @Para1='A23D型'
SET @s='select convert(varchar(10),[time],23) as''日期'''
SELECT @s=@s+','+QUOTENAME(dbo.TbAlarm.alarmName+'(次)')+'=sum(case when dbo.TbAlarm.alarmID='+rtrim(dbo.TbAlarm.alarmID)+' then 1 else 0 end)'
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = @Para1)
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
EXEC(@s+N',sum(1) as ''总计'' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where dbo.Device.DType ='''+@Para1+'''
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
')
/*
日期 正常(次) 电池已满(次) 与服务器断开(次) 短路(次) 接入(次) 复位(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 2
2010-08-05 0 0 1 0 0 0 1
2010-08-08 0 0 0 1 0 0 1
2010-08-10 0 0 0 0 1 0 1
2010-08-12 0 0 0 0 0 1 1
(5 個資料列受到影響)
*/
#10
這一段為條件
SELECT @Para1=N'A23D型'--輸入參數A23D型
SELECT @Para1=N'A23D型'--輸入參數A23D型
#11
哇 大侠 果然厉害 还能简化点吗 有点看不明白
#12
还差一个条件 产品编号
#13
本帖最后由 roy_88 于 2010-08-13 17:31:19 编辑
DECLARE @s NVARCHAR(4000),@Para1 NVARCHAR(100),@unitID INT
SELECT
@Para1=N'A23C型',--兩個參數
@unitID=129--兩個參數
SET @s='select convert(varchar(10),[time],23) as''日期'''
SELECT @s=@s+','+QUOTENAME(dbo.TbAlarm.alarmName+'(次)')+'=sum(case when dbo.TbAlarm.alarmID='+rtrim(dbo.TbAlarm.alarmID)+' then 1 else 0 end)'
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = @Para1)
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
EXEC(@s+N',sum(1) as ''总计'' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where dbo.Device.DType ='''+@Para1+''' and unitID ='+@unitID+'
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
')
/*
日期 正常(次) 停止(次) 暂停(次) 自动维护(次) 手动维护(次) 与服务器断开(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 2
2010-08-04 0 0 1 0 0 0 1
2010-08-05 0 0 0 1 0 0 1
2010-08-08 0 0 0 0 1 0 1
2010-08-12 0 0 0 0 0 1 1
(5 個資料列受到影響)
*/
#14
恩 加个时间段查询 就基本上 搞定了
#15
以上是看樓主的數據提供的方法，如果需要用時間這樣加條件
DECLARE @s NVARCHAR(4000),@Para1 NVARCHAR(100),@unitID INT,@Dt1 DATETIME,@Dt2 DATETIME
SELECT
@Para1=N'A23C型',--兩個參數
@unitID=129,--兩個參數
@Dt1='2010-8-2',
@Dt2='2010-8-13'
SET @s='select convert(varchar(10),[time],23) as''日期'''
SELECT @s=@s+','+QUOTENAME(dbo.TbAlarm.alarmName+'(次)')+'=sum(case when dbo.TbAlarm.alarmID='+rtrim(dbo.TbAlarm.alarmID)+' then 1 else 0 end)'
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = @Para1)
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
EXEC(@s+N',sum(1) as ''总计'' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where dbo.Device.DType ='''+@Para1+''' and unitID ='+@unitID+' and [time] between '''+@Dt1+''' and '''+@Dt2+'''
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
')
#16
如果楼主不想将警报名称写死在统计过程中，那就改用动态SQL吧，将警报名称做列名，这就是行列转换了
#1
select convert(varchar(10),[time],23) as'日期' ,UnitID,
sum(case AlarmID when '1' then 1 else 0 end)as'正常(次)',
sum(case AlarmID when '2' then 1 else 0 end)as'停止(次)',
sum(case AlarmID when '3' then 1 else 0 end)as'暂停(次)',
sum(case AlarmID when '5' then 1 else 0 end)as'自动维护(次)',
sum(case AlarmID when '6' then 1 else 0 end)as'手动维护(次)',
sum(case AlarmID when '7' then 1 else 0 end)as'电池已满(次)',
sum(case AlarmID when '8' then 1 else 0 end)as'与服务器断开(次)',
sum(case AlarmID when '9' then 1 else 0 end)as'短路(次)',
sum(case AlarmID when '10' then 1 else 0 end)as'接入(次)',
sum(case AlarmID when '12' then 1 else 0 end)as'复位(次)',
count(AlarmID) as '总计' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.UnitID = dbo.Device.DeviceID
where [time] between'2010-8-2' and '2010-8-13'
group by convert(varchar(10),[time],23) ,UnitID
Order by UnitID,convert(varchar(10),[time],23) asc
不写死要怎么写？动态的？
#2
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + CONVERT(VARCHAR,T.AlarmID) from (select TbAlarm.* FROM TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where [time] between'2010-8-2' and '2010-8-13' and Device.dtype = 'A23D型')T group by CONVERT(VARCHAR,T.AlarmID)
set @sql = '[' + @sql + ']'
exec ('select * from (select time,unitid,alarmid from TbAlarm) a
pivot (max(unitid) for AlarmID in (' + @sql + ')) b')
try
lz结合一下自己的应用，然后case一下列名即可
#3
要的效果是?
#4
DECLARE @unitID INT
DECLARE @begin DATETIME
DECLARE @end DATETIME
SET @unitID = 129
SET @begin = '2010-8-2'
SET @end = '2010-8-13'
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT CONVERT(VARCHAR(10),A.[time],23) AS [日期]'
SELECT @sql = @sql + ',SUM(CASE WHEN A.alarmName = '''+ alarmName + ''' THEN 1 ELSE 0 END) AS [' + alarmName + ']'
FROM (SELECT DISTINCT alarmName FROM dbo.TbAlarm
INNER JOIN dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE unitID = @unitID)AS Temp
SELECT @sql = @sql + ' FROM dbo.TbAlarm A INNER JOIN dbo.Device B ON A.unitID = B.deviceID
WHERE A.[time] BETWEEN ''' + CONVERT(VARCHAR(10),@begin,23) + ''' AND ''' + CONVERT(VARCHAR(10),@end,23)
+ ''' AND A.unitID = ' + CAST(@unitID AS VARCHAR(10)) + ' GROUP BY CONVERT(VARCHAR(10),A.[time],23)
ORDER BY CONVERT(VARCHAR(10),A.[time],23) ASC'
--SELECT @sql
EXEC(@sql)
#5
恩 是的 就是要根据 类型 Dtype 来实现动态的
#6
拜托大哥 运行出来 完全不是我想要的结果
#7
运行结果不对
#8
额 学习一下
#9
/*
UnitID 产品编号
alarmID 报警状态
alarmName 报警名称
*/
drop table TbAlarm
CREATE TABLE TbAlarm (time datetime,UnitID int,alarmID int,alarmName nvarchar(20),alarmDescrise nvarchar(100))
insert TbAlarm
select '2010-08-02 14:10:36',129,1,N'正常',N'说明：正常运行' union all
select '2010-08-02 14:11:26',129,2,N'停止',N'说明：停止运行'union all
select '2010-08-04 14:11:16',129,3,N'暂停',N'说明：暂停运行' union all
select '2010-08-05 15:10:36',129,5,N'自动维护',N'自动维护' union all
select '2010-08-08 10:10:36',129,6,N'手动维护',N'手动维护' union all
select '2010-08-12 14:10:36',129,8,N'与服务器断开',N'与服务器断开' union all
select '2010-08-02 12:10:36',130,1,N'正常',N'说明：正常运行'union all
select '2010-08-02 14:10:36',130,7,N'电池已满',N'电池已满'union all
select '2010-08-05 10:10:36',130,8,N'与服务器断开',N'与服务器断开'union all
select '2010-08-08 8:10:36',130,9,N'短路',N'短路'union all
select '2010-08-10 11:10:36',130,10,N'接入',N'接入'union all
select '2010-08-12 21:10:36',130,12,N'复位',N'复位'union all
select '2010-08-02 07:10:36',131,2,N'停止',N''
drop table Device
CREATE TABLE Device (DeviceID int,DeviceName nvarchar(8),DType nvarchar(20))
insert Device
select 115,N'产品D',N'D21型' union all
select 129,N'产品A',N'A23C型' union all
select 130,N'产品A2',N'A23D型' union all
select 131,N'产品J',N'J3F型' union all
select 132,N'产品D',N'D1K型' union all
select 133,N'产品C',N'C12型' union all
select 134,N'产品B',N'B2F型' union all
select 135,N'产品G',N'GK3型'
DECLARE @s NVARCHAR(4000),@Para1 NVARCHAR(100)--,@Dt1 DATETIME,@Dt2 DATETIME
SELECT @Para1='A23D型'
SET @s='select convert(varchar(10),[time],23) as''日期'''
SELECT @s=@s+','+QUOTENAME(dbo.TbAlarm.alarmName+'(次)')+'=sum(case when dbo.TbAlarm.alarmID='+rtrim(dbo.TbAlarm.alarmID)+' then 1 else 0 end)'
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = @Para1)
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
EXEC(@s+N',sum(1) as ''总计'' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where dbo.Device.DType ='''+@Para1+'''
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
')
/*
日期 正常(次) 电池已满(次) 与服务器断开(次) 短路(次) 接入(次) 复位(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 2
2010-08-05 0 0 1 0 0 0 1
2010-08-08 0 0 0 1 0 0 1
2010-08-10 0 0 0 0 1 0 1
2010-08-12 0 0 0 0 0 1 1
(5 個資料列受到影響)
*/
#10
這一段為條件
SELECT @Para1=N'A23D型'--輸入參數A23D型
SELECT @Para1=N'A23D型'--輸入參數A23D型
#11
哇 大侠 果然厉害 还能简化点吗 有点看不明白
#12
还差一个条件 产品编号
#13
本帖最后由 roy_88 于 2010-08-13 17:31:19 编辑
DECLARE @s NVARCHAR(4000),@Para1 NVARCHAR(100),@unitID INT
SELECT
@Para1=N'A23C型',--兩個參數
@unitID=129--兩個參數
SET @s='select convert(varchar(10),[time],23) as''日期'''
SELECT @s=@s+','+QUOTENAME(dbo.TbAlarm.alarmName+'(次)')+'=sum(case when dbo.TbAlarm.alarmID='+rtrim(dbo.TbAlarm.alarmID)+' then 1 else 0 end)'
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = @Para1)
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
EXEC(@s+N',sum(1) as ''总计'' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where dbo.Device.DType ='''+@Para1+''' and unitID ='+@unitID+'
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
')
/*
日期 正常(次) 停止(次) 暂停(次) 自动维护(次) 手动维护(次) 与服务器断开(次) 总计
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-08-02 1 1 0 0 0 0 2
2010-08-04 0 0 1 0 0 0 1
2010-08-05 0 0 0 1 0 0 1
2010-08-08 0 0 0 0 1 0 1
2010-08-12 0 0 0 0 0 1 1
(5 個資料列受到影響)
*/
#14
恩 加个时间段查询 就基本上 搞定了
#15
以上是看樓主的數據提供的方法，如果需要用時間這樣加條件
DECLARE @s NVARCHAR(4000),@Para1 NVARCHAR(100),@unitID INT,@Dt1 DATETIME,@Dt2 DATETIME
SELECT
@Para1=N'A23C型',--兩個參數
@unitID=129,--兩個參數
@Dt1='2010-8-2',
@Dt2='2010-8-13'
SET @s='select convert(varchar(10),[time],23) as''日期'''
SELECT @s=@s+','+QUOTENAME(dbo.TbAlarm.alarmName+'(次)')+'=sum(case when dbo.TbAlarm.alarmID='+rtrim(dbo.TbAlarm.alarmID)+' then 1 else 0 end)'
FROM dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
WHERE (dbo.Device.DType = @Para1)
GROUP BY dbo.TbAlarm.alarmID, dbo.TbAlarm.alarmName
ORDER BY dbo.TbAlarm.alarmID
EXEC(@s+N',sum(1) as ''总计'' from dbo.TbAlarm INNER JOIN
dbo.Device ON dbo.TbAlarm.unitID = dbo.Device.deviceID
where dbo.Device.DType ='''+@Para1+''' and unitID ='+@unitID+' and [time] between '''+@Dt1+''' and '''+@Dt2+'''
group by convert(varchar(10),[time],23)
Order by convert(varchar(10),[time],23) asc
')
#16
如果楼主不想将警报名称写死在统计过程中，那就改用动态SQL吧，将警报名称做列名，这就是行列转换了