之前有个系统,运行一直正常2年了。
可是今天报错如下:
【
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
】如下是SQl语句,请高手请教。SELECT TOP (100) PERCENT dbo.EVENTS.EMPID, ISNULL(dbo.EMP.FIRSTNAME, '') + ' ' + ISNULL(dbo.EMP.LASTNAME, '') AS staff, dbo.EVENTS.CARDNUM,
dbo.EVENTS.EVENTIME AS Datetime, dbo.EVENTS.DEVID,
(SELECT READERDESC
FROM dbo.READER
WHERE (READERID = dbo.EVENTS.DEVID)) AS whichdoor, CASE WHEN
(SELECT readerdesc
FROM reader
WHERE readerid = devid) = 'INSIDE CR2' THEN 'Go Out' WHEN
(SELECT readerdesc
FROM reader
WHERE readerid = devid) = 'OUTSIDE CR1 KEYPAD' THEN 'Come In' ELSE 'other' END AS eventDetail, dbo.EVENTS.EVENTID,
(SELECT EVDESCR
FROM dbo.EVENT
WHERE (EVENTID = dbo.EVENTS.EVENTID)) AS eventId_Mean, dbo.EVENTS.EVENTTYPE,
(SELECT EVTDESCR
FROM dbo.EVENTYPE
WHERE (EVTYPEID = dbo.EVENTS.EVENTTYPE)) AS eventtype_Mean
FROM dbo.EVENTS INNER JOIN
dbo.EMP ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (dbo.EVENTS.DEVID IN (1, 2))
ORDER BY whichdoor, Datetime
可是今天报错如下:
【
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
】如下是SQl语句,请高手请教。SELECT TOP (100) PERCENT dbo.EVENTS.EMPID, ISNULL(dbo.EMP.FIRSTNAME, '') + ' ' + ISNULL(dbo.EMP.LASTNAME, '') AS staff, dbo.EVENTS.CARDNUM,
dbo.EVENTS.EVENTIME AS Datetime, dbo.EVENTS.DEVID,
(SELECT READERDESC
FROM dbo.READER
WHERE (READERID = dbo.EVENTS.DEVID)) AS whichdoor, CASE WHEN
(SELECT readerdesc
FROM reader
WHERE readerid = devid) = 'INSIDE CR2' THEN 'Go Out' WHEN
(SELECT readerdesc
FROM reader
WHERE readerid = devid) = 'OUTSIDE CR1 KEYPAD' THEN 'Come In' ELSE 'other' END AS eventDetail, dbo.EVENTS.EVENTID,
(SELECT EVDESCR
FROM dbo.EVENT
WHERE (EVENTID = dbo.EVENTS.EVENTID)) AS eventId_Mean, dbo.EVENTS.EVENTTYPE,
(SELECT EVTDESCR
FROM dbo.EVENTYPE
WHERE (EVTYPEID = dbo.EVENTS.EVENTTYPE)) AS eventtype_Mean
FROM dbo.EVENTS INNER JOIN
dbo.EMP ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (dbo.EVENTS.DEVID IN (1, 2))
ORDER BY whichdoor, Datetime
dbo.EVENTS.EMPID,
ISNULL(dbo.EMP.FIRSTNAME, '') + ' ' + ISNULL(dbo.EMP.LASTNAME, '') AS staff,
dbo.EVENTS.CARDNUM,
dbo.EVENTS.EVENTIME AS Datetime, dbo.EVENTS.DEVID,
(SELECT TOP 1 READERDESC
FROM dbo.READER
WHERE (READERID = dbo.EVENTS.DEVID)) AS whichdoor, CASE WHEN
(SELECT TOP 1 readerdesc
FROM reader
WHERE readerid = devid) = 'INSIDE CR2' THEN 'Go Out' WHEN
(SELECT TOP 1 readerdesc
FROM reader
WHERE readerid = devid) = 'OUTSIDE CR1 KEYPAD' THEN 'Come In' ELSE 'other' END AS eventDetail, dbo.EVENTS.EVENTID,
(SELECT TOP 1 EVDESCR
FROM dbo.EVENT
WHERE (EVENTID = dbo.EVENTS.EVENTID)) AS eventId_Mean, dbo.EVENTS.EVENTTYPE,
(SELECT TOP 1 EVTDESCR
FROM dbo.EVENTYPE
WHERE (EVTYPEID = dbo.EVENTS.EVENTTYPE)) AS eventtype_Mean
FROM dbo.EVENTS INNER JOIN
dbo.EMP ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (dbo.EVENTS.DEVID IN (1, 2))
ORDER BY whichdoor, Datetimeselect 显示子查询时用top 1或max/min ,有且只能显示一条