DECLARE @EventPerSystem TABLE(ID INT, SystemEventID INT, [DateTime] DATETIME)INSERT INTO @EventPerSystem
SELECT 1, 40, '1/13/2006 9:22:00AM'
UNION
SELECT 2, 40, '1/13/2006 6:45:00PM'
UNION
SELECT 3, 40, '1/16/2006 9:21:00AM'
UNION
SELECT 4, 40, '1/19/2006 9:34:00AM'
UNION
SELECT 7, 37, '1/13/2006 10:22:00 AM'
UNION
SELECT 8, 37, '1/13/2006 8:45:00 PM'
UNION
SELECT 9, 37, '1/16/2006 12:21:00 AM'
UNION
SELECT 10, 37, '1/17/2006 10:22:00 AM'
DECLARE @FunctionLog TABLE(CaseID INT, FunctionID INT, StartTime DATETIME, Duration INT, StopTime DATETIME)
INSERT INTO @FunctionLog
SELECT ID, 1, [DateTime], NULL, NULL FROM @EventPerSystem WHERE SystemEventID = 40 ORDER BY ID
UPDATE @FunctionLog SET StopTime = A.[DateTime]
FROM (SELECT ID, [DateTime] FROM @EventPerSystem WHERE SystemEventID = 37) AS A, @FunctionLog AS B
WHERE B.CaseID = A.ID - 6
UPDATE @FunctionLog SET Duration = DATEDIFF(mi, StartTime, StopTime)SELECT * FROM @FunctionLog
SELECT 1, 40, '1/13/2006 9:22:00AM'
UNION
SELECT 2, 40, '1/13/2006 6:45:00PM'
UNION
SELECT 3, 40, '1/16/2006 9:21:00AM'
UNION
SELECT 4, 40, '1/19/2006 9:34:00AM'
UNION
SELECT 7, 37, '1/13/2006 10:22:00 AM'
UNION
SELECT 8, 37, '1/13/2006 8:45:00 PM'
UNION
SELECT 9, 37, '1/16/2006 12:21:00 AM'
UNION
SELECT 10, 37, '1/17/2006 10:22:00 AM'
DECLARE @FunctionLog TABLE(CaseID INT, FunctionID INT, StartTime DATETIME, Duration INT, StopTime DATETIME)
INSERT INTO @FunctionLog
SELECT ID, 1, [DateTime], NULL, NULL FROM @EventPerSystem WHERE SystemEventID = 40 ORDER BY ID
UPDATE @FunctionLog SET StopTime = A.[DateTime]
FROM (SELECT ID, [DateTime] FROM @EventPerSystem WHERE SystemEventID = 37) AS A, @FunctionLog AS B
WHERE B.CaseID = A.ID - 6
UPDATE @FunctionLog SET Duration = DATEDIFF(mi, StartTime, StopTime)SELECT * FROM @FunctionLog
但是我不知道lz是不是主要要拿来更新用的,所以就拆开了写另外,这里有一个bug,即时间为“12:21:00 AM”这样的东西,我建议全部改成“12:21:00 PM”,因为AM的12点就是24小时制的0点,并不是中午12点,中午12点的表示应该为12PM
UPDATE FunctionLog
SET StopTime = e.DateTime
FROM FMCV.dbo.EventPerSystem e
WHERE e.SystemEventID = 37
然后我想用ORDER BY DATATIME,因为时间能决定顺序。但我不知道如何把ORDERBY放在UPDATE语句里
我试过
select e.Datetime
FROM FMCV.dbo.EventPerSystem e
WHERE e.SystemEventID = 37
orderby e.Datetime
顺序是没问题。关键就是如何UPDATE语句里放ORDERBY
谢谢了
SET StopTime = e.DateTime
FROM FMCV.dbo.EventPerSystem e
WHERE e.SystemEventID = 37
无论如何是不对的,因为这样的where将返回很多结果,set语句只取第一条,所以你的更新将全部为一个值
SELECT 1, 40, '1/13/2006 9:22:00AM'
UNION
SELECT 2, 40, '1/13/2006 6:45:00PM'
UNION
SELECT 3, 40, '1/16/2006 9:21:00AM'
UNION
SELECT 4, 40, '1/19/2006 9:34:00AM'
UNION
SELECT 7, 37, '1/13/2006 10:22:00 AM'
UNION
SELECT 8, 37, '1/13/2006 8:45:00 PM'
UNION
SELECT 9, 37, '1/16/2006 12:21:00 AM'
UNION
SELECT 10, 37, '1/17/2006 10:22:00 AM'
DECLARE @Temp37 TABLE(ID INT IDENTITY(1, 1), [DateTime] DATETIME)INSERT INTO @Temp37
SELECT [DateTime] FROM @EventPerSystem WHERE SystemEventID = 37 ORDER BY [DateTime]
DECLARE @FunctionLog TABLE(CaseID INT, FunctionID INT, StartTime DATETIME, Duration INT, StopTime DATETIME)INSERT INTO @FunctionLog
SELECT A.ID, 1, A.[DateTime], DATEDIFF(mi, A.[DateTime], B.[DateTime]), B.[DateTime] FROM
@EventPerSystem AS A
INNER JOIN
@Temp37 AS B
ON A.ID = B.IDSELECT * FROM @FunctionLog这样行不?
A.ID = B.ID 只能对于有序排列的才行的通。
下面的例子: 40 和37 是一对,41 和38是一对。对于这种情况,我觉得选出START(40,41),再选出结束(37,38),比较加如COUNT(Select Datetime from EventPesystem where SystemEventID=40)==Count(Select Datetime from EventPesystem where SystemEventID=37),那就一一对应,插入到FunctionLog
但具体SQL还请教。EventPerSystem
id SystemEventID DateTime
1 40 1/13/2006 9:22:00AM
2 40 1/13/2006 6:45:00PM
3 40 1/16/2006 9:21:00AM
4 40 1/19/2006 9:34:00AM
5 41 1/19/2006 10:34:00AM
6 41 1/19/2006 14:34:00PM
7 37 1/13/2006 10:22:00 AM
8 37 1/13/2006 8:45:00 PM
9 37 1/16/2006 12:21:00 AM
10 38 1/17/2006 10:22:00 AM
11 38 1/18/2006 9:34:00AM
12 37 1/16/2006 11:21:00 AM
谢谢