用于过滤特定记录计数的记录的SQL查询

Question

我有一个表，其中有标识、RecordId、类型、读数和IsDeleted列。Identity是自动递增的主键，RecordId是可以有重复值的整数，类型是可以是‘1’或'average‘的读数类型，读数是包含任何整数值的整数，IsDeleted是可以是0或1的位，即false或true。现在，我想要的查询包含表的所有记录，如果每个RecordId的计数(Id)大于2，则显示该表的所有记录。

如果该特定RecordId的COUNT(Id) == 2和这两个记录的读数值(即'one‘或' average’类型)相同，则仅显示average记录。

如果COUNT(Id)为==1，则仅显示该记录。

例如：

Id          RecordId          Type          Reading       IsDeleted 
1           1                 one             4              0
2           1                 one             5              0
3           1                 one             6              0
4           1                 average         5              0
5           2                 one             1              0
6           2                 one             3              0
7           2                 average         2              0
8           3                 one             2              0
9           3                 average         2              0
10          4                 one             5              0
11          4                 average         6              0
12          5                 one             7              0

Ans结果可以是

Id          RecordId          Type          Reading       IsDeleted 
1           1                 one             4              0
2           1                 one             5              0
3           1                 one             6              0
4           1                 average         5              0
5           2                 one             1              0
6           2                 one             3              0
7           2                 average         2              0
9           3                 average         2              0
10          4                 one             5              0
11          4                 average         6              0
12          5                 one             7              0

简而言之，我想跳过' one‘类型的读数，它具有相同值的平均读数，并且它的'one’类型读数不超过1。

Answer 1

;with a as
(
select Id,RecordId,Type,Reading,IsDeleted, count(*) over (partition by RecordId, Reading) cnt, 
row_number() over (partition by RecordId, Reading order by Type, RecordId) rn
from table
)
select Id,RecordId,Type,Reading,IsDeleted
from a where cnt <> 2 or rn = 1

Answer 2

查看此程序

DECLARE @t TABLE(ID INT IDENTITY,RecordId INT,[Type] VARCHAR(10),Reading INT,IsDeleted BIT)
INSERT INTO @t VALUES
(1,'one',4,0),(1,'one',5,0),(1,'one',6,0),(1,'average',5,0),(2,'one',1,0),(2,'one',3,0),
(2,'average',2,0),(3,'one',2,0),(3,'average',2,0),(4,'one',5,0),(4,'average',6,0),(5,'one',7,0),
(6,'average',6,0),(6,'average',6,0),(7,'one',6,0),(7,'one',6,0)
--SELECT * FROM @t

;WITH GetAllRecordsCount AS                              
(   
    SELECT *,Cnt = COUNT(RecordId) OVER(PARTITION BY RecordId ORDER BY RecordId)
    FROM @t
)
-- Condition 1 : When COUNT(RecordId) for each RecordId is greater than 2 
 --               then display all the records of that RecordId. 
, GetRecordsWithCountMoreThan2 AS
(
    SELECT * FROM GetAllRecordsCount WHERE Cnt > 2
)
-- Get all records where count = 2
, GetRecordsWithCountEquals2 AS
(
    SELECT * FROM GetAllRecordsCount WHERE Cnt = 2
)
-- Condition 3 : When COUNT(RecordId) == 1 then display only that record.
, GetRecordsWithCountEquals1 AS
(
    SELECT * FROM GetAllRecordsCount WHERE Cnt = 1
)

-- Condition 1: When COUNT(RecordId) > 2
SELECT * FROM GetRecordsWithCountMoreThan2  UNION ALL

-- Condition 2 : When  COUNT(RecordId) == 2 for that specific RecordId and Reading value of 
--               both i.e. 'one' or 'average' type of the records are same then display only 
--               average record. 
SELECT t1.* FROM GetRecordsWithCountEquals2 t1
JOIN (Select RecordId From GetRecordsWithCountEquals2 Where [Type] = ('one') )X
ON t1.RecordId = X.RecordId
AND t1.Type = 'average'     UNION ALL   

-- Condition 2: When COUNT(RecordId) = 1
SELECT * FROM GetRecordsWithCountEquals1    

结果

ID  RecordId    Type    Reading IsDeleted   Cnt
1   1            one    4             0     4
2   1            one    5             0     4
3   1            one    6             0     4
4   1            average5             0     4
5   2            one    1             0     3
6   2            one    3             0     3
7   2            average2             0     3
9   3            average2             0     2
11  4            average6             0     2
12  5            one    7             0     1

Answer 3

假设您的表名为the_table，让我们这样做：

select main.*
from the_table as main
inner join (
  select recordId, count(Id) as num, count(distinct Reading) as reading_num
  from the_table
  group by recordId
) as counter on counter.recordId=main.recordId
where num=1 or num>2 or reading_num=2 or main.type='average';

未经测试，但它应该是它的某个变体。

编辑 TEST HERE ON FIDDLE

简短的总结是，我们希望将表与o=itself的聚合版本连接起来，然后根据您提到的计数标准对其进行过滤(num=1，然后显示它；num=2，如果读数相同，则只显示平均记录；否则两者都显示；num>2，显示所有记录)。