select case when speed=0 then '停止' else '正常' end 状态,min(t_time),max(t_time) from mytable group by CONVERT(varchar(13),t_time),speed 其中这个t_time 就是你的time 字段
select case when speed=0 then '停止' else '正常' end 状态,min(t_time) 起始时间,max(t_time) 终止时间 from mytable group by CONVERT(varchar(13),t_time),speed
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE dbo.TB; CREATE TABLE TB(speed INT,[time] DATETIME) INSERT INTO dbo.TB SELECT 45, '2011/10/11 6:24:23' UNION ALL SELECT 54, '2011/10/11 6:25:23' UNION ALL SELECT 0, '2011/10/11 6:34:23' UNION ALL SELECT 0, '2011/10/11 6:39:23' UNION ALL SELECT 0, '2011/10/11 6:41:23' UNION ALL SELECT 43, '2011/10/11 6:44:23' UNION ALL SELECT 21, '2011/10/11 6:54:23' UNION ALL SELECT 0, '2011/10/11 7:19:23' UNION ALL SELECT 0, '2011/10/11 7:25:23' UNION ALL SELECT 43, '2011/10/11 7:44:23'; SELECT IDENTITY(INT,1,1) id ,speed,[time] INTO tmp FROM dbo.TB ORDER BY [time]; SELECT '停止' AS [狀態] , MIN(CONVERT(VARCHAR(19), a.[time], 120) + '--' + CONVERT(VARCHAR(19), b.time, 120)) AS [時間] FROM ( SELECT a.[time] FROM dbo.tmp a WHERE NOT EXISTS ( SELECT 1 FROM dbo.tmp WHERE id = a.id - 1 AND speed = 0 ) AND a.speed = 0 ) a LEFT JOIN ( SELECT a.[time] FROM dbo.tmp a WHERE NOT EXISTS ( SELECT 1 FROM dbo.tmp WHERE id = a.id + 1 AND speed = 0 ) AND a.speed = 0 ) b ON a.time < b.time GROUP BY a.[time]; DROP TABLE dbo.TB,dbo.tmp; /* 狀態 時間 ---- ---------------------------------------- 停止 2011-10-11 06:34:23--2011-10-11 06:41:23 停止 2011-10-11 07:19:23--2011-10-11 07:25:23(2 個資料列受到影響) */
其中这个t_time 就是你的time 字段
DROP TABLE dbo.TB;
CREATE TABLE TB(speed INT,[time] DATETIME)
INSERT INTO dbo.TB
SELECT 45, '2011/10/11 6:24:23' UNION ALL
SELECT 54, '2011/10/11 6:25:23' UNION ALL
SELECT 0, '2011/10/11 6:34:23' UNION ALL
SELECT 0, '2011/10/11 6:39:23' UNION ALL
SELECT 0, '2011/10/11 6:41:23' UNION ALL
SELECT 43, '2011/10/11 6:44:23' UNION ALL
SELECT 21, '2011/10/11 6:54:23' UNION ALL
SELECT 0, '2011/10/11 7:19:23' UNION ALL
SELECT 0, '2011/10/11 7:25:23' UNION ALL
SELECT 43, '2011/10/11 7:44:23';
SELECT IDENTITY(INT,1,1) id ,speed,[time] INTO tmp FROM dbo.TB ORDER BY [time];
SELECT '停止' AS [狀態] ,
MIN(CONVERT(VARCHAR(19), a.[time], 120) + '--'
+ CONVERT(VARCHAR(19), b.time, 120)) AS [時間]
FROM ( SELECT a.[time]
FROM dbo.tmp a
WHERE NOT EXISTS ( SELECT 1
FROM dbo.tmp
WHERE id = a.id - 1
AND speed = 0 )
AND a.speed = 0
) a
LEFT JOIN ( SELECT a.[time]
FROM dbo.tmp a
WHERE NOT EXISTS ( SELECT 1
FROM dbo.tmp
WHERE id = a.id + 1
AND speed = 0 )
AND a.speed = 0
) b ON a.time < b.time
GROUP BY a.[time];
DROP TABLE dbo.TB,dbo.tmp;
/*
狀態 時間
---- ----------------------------------------
停止 2011-10-11 06:34:23--2011-10-11 06:41:23
停止 2011-10-11 07:19:23--2011-10-11 07:25:23(2 個資料列受到影響)
*/
//Gridview绑定项这样写
<asp:TemplateField HeaderText="速度">
<ItemTemplate>
<%#Eval("speed")=="0"?"停止":Eval("speed")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="时间">
<ItemTemplate>
<%#Eval("time")%>
</ItemTemplate>
</asp:TemplateField>