SQL返回子级计数不等于列值的行

您需要子查询

select *
  from item
  where expectedsubtems <> (
    select count(*)
      from subitem
      where subitem.itemid = item.itemid
    )

尝试:

Select i.ItemId, i.ItemName
From Item i
  Left Join SubItem s
     On s.ItemID  = i.ItemId
Group By i.ItemId, i.ItemName, i.ExpectedSubItems
Having Count(*) <> i.ExpectedSubitems

SELECT  ItemID
FROM    Item
JOIN    SubItem
ON      SubItem.ItemID = Item.ItemID
GROUP BY
        ItemID, ExpectedSubItems 
HAVING  ExpectedSubItems <> COUNT(*)

或者这样(这样你就不必全神贯注 Item 也适用于 0 应为子项)

SELECT  Item.*
FROM    Item
CROSS APPLY
        (
        SELECT  NULL
        FROM    SubItem
        WHERE   SubItem.ItemID = Item.ItemID
        HAVING  ExpectedSubItems <> COUNT(*)
        ) S

应该这样做:

SELECT
     I.item_id,
     I.item_name,
     I.expected_subitems
FROM
     Items I
LEFT OUTER JOIN Sub_Items SI ON
     SI.item_id = I.item_id
GROUP BY
     I.item_id,
     I.item_name,
     I.expected_subitems
HAVING
     COUNT(SI.item_id) <> I.expected_subitems

尝试以下操作:

select *
  from Item I
  LEFT OUTER JOIN (select ItemID, COUNT(*) as ActualSubItemCount
                     from SubItem
                     group by ItemID) S
    ON (I.ItemID = S.ItemID)
  where (S.ItemID IS NULL AND NVL(I.ExpectedSubItems, 0) <> 0) OR
        I.ExpectedSubItems <> S.ActualSubItemCount;