寻找丢失的行T-SQL

I am having an issue and I have been working on it for the past three hours or so and have not found a solution. Running on a SQL Server platform. I have a single table that looks like this.

我有一个问题，我在过去三个小时左右的时间里一直在研究它，但还没有找到解决方案。在SQL Server平台上运行。我有一个看起来像这样的表。

PT   ITM  VAL
--   ---  ---
01   01   A
01   02   B
01   03   C
02   01   A
02   03   C
03   01   A
03   02   B

I am trying to find which PTs are missing the item numbers.

我试图找到哪些PT缺少项目编号。

In the case above PT02 and PT03 are missing two item. This is the base what where I started, but I am not sure if I am even on the right track.

在上述情况下，PT02和PT03缺少两个项目。这是我开始的基础，但我不确定我是否在正确的轨道上。

select t.PT,t.ITM
FROM MYTABLE t
GROUP BY t.PT,t.ITM
HAVING COUNT(*) > 1

Thanks jlimited

谢谢jlimited

2 个解决方案

#1

If you're expecting 3 ITM per PT, the query would be

如果您期望每个PT有3个ITM，则查询将是

select PT
FROM MYTABLE
GROUP BY PT
HAVING COUNT(ITM) < 3

for other conditions a more complicated query is required.

对于其他条件，需要更复杂的查询。

#2

Here was the solution that worked. I had to select a VAL that was populated, to find the values that were not.

这是有效的解决方案。我必须选择已填充的VAL，以查找不是的值。

select stg.PT,COUNT(stg.ITM) AS ITM_CNT
  FROM MYTABLE stg
 WHERE stg.ITM IS NOT NULL
   AND stg.VAL IN (11)
 GROUP BY stg.PT
HAVING COUNT(stg.ITM) > 1
EXCEPT
select stg.PT,COUNT(stg.ITM) AS ITM_CNT
  FROM MYTABLE stg
 WHERE stg.ITM IS NOT NULL
   AND stg.VLA IN (4,5)
 GROUP BY stg.PT
HAVING COUNT(stg.ITM) > 1

#1