在中使用Sql server

一种方法是使用子查询,该子查询聚合事件并检查所有状态是否为1:

WITH cte AS (
    SELECT EventId
    FROM tbl_items
    GROUP BY EventId
    HAVING SUM(CASE WHEN status <> 1 THEN 1 ELSE 0 END) = 0
)

SELECT t1.*
FROM Tbl_event t1
INNER JOIN cte t2
    ON t1.EventId = t2.EventId;

如果只需要事件ID,请使用 SELECT * FROM cte ,否则请使用我刚才给出的完整查询。如果以后需要,这种方法将推广到更复杂的逻辑。

这将为您提供状态为1的所有项目的所有事件。

SELECT
    E.EventID
FROM
    Tbl_event AS E
    INNER JOIN tbl_items AS I ON E.EventID = I.EventID
GROUP BY
    E.EventID
HAVING
    MIN(I.status) = 1

如果需要完整的事件记录:

;WITH EventsWithItemsStatus1 AS
(
    SELECT
        E.EventID
    FROM
        Tbl_event AS E
        INNER JOIN tbl_items AS I ON E.EventID = I.EventID
    GROUP BY
        E.EventID
    HAVING
        MIN(I.status) = 1
)
SELECT
    E.*
FROM
    Tbl_event AS E
    INNER JOIN EventsWithItemsStatus1 AS N ON E.EventID = N.EventID

使用 group by 带有的子句 subquery

select * from Tbl_event e
inner join (
      select EventId  
      from tbl_items
      where status = 1 
      group by EventId  
      having count(*) > 1 
) i on i.EventId  = e.EventId  

问题不是很清楚

SELECT     min(L.ItemId), L.EventId
FROM       Tbl_items L
where      L.Status = 1 
group by   L.EventId
having count(*) > 1