有一表。CREATE TABLE NFLog
(
NotificationLogId NUMERIC(17,0) NOT NULL,
StationId INT NOT NULL,
EquipmentId INT NOT NULL,
EventId INT NOT NULL,
EventConditionId INT NOT NULL,
StartTime DATETIME NOT NULL,
EventStatus INT NOT NULL
)其中EventStatus的值只为1或者2第一条SQL语句,取得数据 StationId,EquipmentId,EventId,EventConditionId,StartTime相同。但EventStatus不同的数据
第二条SQL语句,取得数据 只有EventStatus=1的数据,并且不能存在StationId,EquipmentId,EventId,EventConditionId,StartTime相同。但EventStatus=2的数据.注,数据量很大,一百万条数据以上,请注意运行效率。
(
NotificationLogId NUMERIC(17,0) NOT NULL,
StationId INT NOT NULL,
EquipmentId INT NOT NULL,
EventId INT NOT NULL,
EventConditionId INT NOT NULL,
StartTime DATETIME NOT NULL,
EventStatus INT NOT NULL
)其中EventStatus的值只为1或者2第一条SQL语句,取得数据 StationId,EquipmentId,EventId,EventConditionId,StartTime相同。但EventStatus不同的数据
第二条SQL语句,取得数据 只有EventStatus=1的数据,并且不能存在StationId,EquipmentId,EventId,EventConditionId,StartTime相同。但EventStatus=2的数据.注,数据量很大,一百万条数据以上,请注意运行效率。
--1select *
from NFLog t
where exists (select 1 from NFLog where StationId = t.StationId and EquipmentId = t.EquipmentId
and EventId = t.EventId and EventConditionId = t.EventConditionId and StartTime = t.StartTime
and EventStatus <> t.EventStatus)--2select *
from NFLog t
where not exists (select 1 from NFLog where StationId = t.StationId and EquipmentId = t.EquipmentId
and EventId = t.EventId and EventConditionId = t.EventConditionId and StartTime = t.StartTime
and EventStatus = 2)
and EventStatus = 1
???
EventStatus 这列不就俩值吗?
我的1楼没呢???
--1select *
from NFLog t
where exists (select 1 from NFLog where StationId = t.StationId and EquipmentId = t.EquipmentId
and EventId = t.EventId and EventConditionId = t.EventConditionId and StartTime = t.StartTime
and EventStatus <> t.EventStatus)--2select *
from NFLog t
where not exists (select 1 from NFLog where StationId = t.StationId and EquipmentId = t.EquipmentId
and EventId = t.EventId and EventConditionId = t.EventConditionId and StartTime = t.StartTime
and EventStatus = 2)
and EventStatus = 1
select distinct StationId,EquipmentId,EventId,EventConditionId,StartTime,EventStatus
from NFLog
行不?
但EventStatus=2的数据是什么意思?不清楚.select m.* from nflog m,
(select StationId,EquipmentId,EventId,EventConditionId,StartTime from nflog group by StationId,EquipmentId,EventId,EventConditionId,StartTime having count(1) = 1) n
where m.EventStatus=1 and
m.StationId = n.StationId and
m.EquipmentId = n.EquipmentId and
m.EventId = n.EventId and
m.EventConditionId = n.EventConditionId and
m.StartTime = n.StartTime
你理解的意思是正确的,
但是
用group by与having的结果是跑了五分钟了还没出数据来。
有更加优化的方案吗?
如果StationId,EquipmentId,EventId,EventConditionId,StartTime这些字段上有索引,可以考虑用Join语句:
select
Log_a.*
from dbo.NFLog Log_a
join dbo.NFLog Log_b
on Log_a.StationId = Log_b.StationId
and Log_a.EquipmentId = Log_b.EquipmentId
and Log_a.EventId = Log_b.EventId
and Log_a.EventConditionId = Log_b.EventConditionId
and Log_a.StartTime = Log_b.StartTime
--and Log_a.[NotificationLogId] != Log_b.[NotificationLogId]
where Log_a.EventStatus != Log_b.EventStatus;