如何从单个表中获得“匹配”和“不匹配”行

时间:2021-04-17 09:16:35

I would like to have two stored procedures get the 'matched' and 'unmatched' rows, respectively... from a single table...

我希望有两个存储过程分别获得“匹配”和“不匹配”行……从一个表…

'matched' rows are two rows that have a state of 1 and 2 respectively, for the same:

“匹配”行是两个状态分别为1和2的行,相同:

uID aID cID and pID

An 'unmatched' row is a row that has a state of 1, but there isn't another row with a state of 2 that has the same uID, aID, cID and pID

“不匹配”行是状态为1的行,但是没有另一个状态为2的行具有相同的uID、aID、cID和pID

( I have removed the unique identifier Indx from the data for brevity )

(为了简便起见,我从数据中删除了唯一标识符Indx)

Sample data

样本数据

uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 08:12:13.367
80  542 9000    5700    1   2018-04-17 08:12:54.113
10  240 5000    3860    1   2018-04-17 08:13:09.817
10  200 5000    1240    2   2018-04-17 08:13:18.010
30  240 7000    5938    1   2018-04-17 08:13:31.510
80  542 9000    5700    2   2018-04-17 08:14:04.363

Here are examples of 'matched' rows

下面是“匹配”行的示例

uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 08:12:13.367
10  200 5000    1240    2   2018-04-17 08:13:18.010
80  542 9000    5700    1   2018-04-17 08:12:54.113
80  542 9000    5700    2   2018-04-17 08:14:04.363

Here are examples of 'unmatched' rows

下面是“不匹配”行的示例

uID aID cID pID state   occurs
10  240 5000    3860    1   2018-04-17 08:13:09.817
30  240 7000    5938    1   2018-04-17 08:13:31.510

Matched Row code

匹配行代码

Where I am having difficulty is wrapping my head around the statement that group the matched sets... I thought I would be able to do something like this to get both rows with 1 and 2 but no such luck....

我有困难的地方是,我的脑海中出现了这样一种说法:将匹配的集合分组……我想我能够做这样的与1和2两行,但没有这样的运气....

select uID, aID, cID, pID, state
from Data where state in (1,2) 
group by uID, aID, cID, pID, state
having state = 2 and state = 1

Unmatched Row code

无与伦比的一行代码

Then I thought I would be able to do something similar for the unmatched rows, but this doesn't work either....

然后我想我能够为无与伦比的行做类似的事情,但这并不奏效....

select uID, aID, cID, pID, state
from Data where state in (1,2) 
group by uID, aID, cID, pID, state
having state != 2 and state = 1

I need some help... thanks

我需要一些帮助……谢谢

Infrastructure

基础设施

Here is the code to create the table:

下面是创建表的代码:

CREATE TABLE [dbo].[Data](
    [INDX] [uniqueidentifier] NOT NULL,
    [uID] [int] NOT NULL,
    [aID] [int] NOT NULL,
    [cID] [int] NOT NULL,
    [pID] [int] NOT NULL,
    [state] [int] NOT NULL,
    [occurs] [datetime] NOT NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [INDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

the stored procedure to "insert the data"

“插入数据”的存储过程

CREATE PROCEDURE [dbo].[InsertData]
    -- Add the parameters for the stored procedure here

    @userID int, 
    @appID int, 
    @compID int, 
    @procID int, 
    @state int,
    @occurence datetime

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

        declare @indx as uniqueidentifier
        set @indx = NEWID();

        insert into [Data]
        Values(
                @indx,
                @userID,
                @appID,
                @compID,
                @procID,
                @state,
                @occurence
                )

END
GO

and here is some code to help populate the table:

下面是一些帮助填充表格的代码:

declare @userID int
declare @appID int
declare @compID int
declare @procID int
declare @state int
declare @occurence datetime

set @userID = 10
set @appID = 200
set @compID = 5000
set @procID = 1240
set @state = 1

set @occurence = GETDATE();
EXEC InsertData @userID, @appID, @compID, @procID, @state, @occurence

Test when adding another state:

添加其他状态时进行测试:

    set @userID = 80
    set @appID = 546
    set @compID = 9000
    set @procID = 5700
    set @state = 3
    set @occurence = GETDATE();
    EXEC InsertData @userID, @appID, @compID, @procID, @state, @occurence

So I wind up with this data set:

最后我得到了这个数据集

uID aID cID pID state
10  200 5000    1240    2
10  200 5000    1240    1
10  240 5000    3860    1
80  542 9000    5700    1
30  240 7000    5938    1
80  546 9000    5700    3
80  542 9000    5700    2

So... scsimon... your query works great... but what if this were the case:

所以…scsimon……查询是伟大的……但如果是这样的话:

NEW DUPLICATE DATA ADDED

添加新重复数据

uID aID cID pID state
10  200 5000    1240    1
80  542 9000    5700    1
10  240 5000    3860    1
10  200 5000    1240    2
30  240 7000    5938    1
80  542 9000    5700    2
80  546 9000    4502    3
10  200 5000    1240    1
10  200 5000    1240    2

Query results, which is close...

查询结果,很接近……

uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 11:57:22.693
10  200 5000    1240    1   2018-04-17 11:57:29.797
10  200 5000    1240    2   2018-04-17 11:57:25.740
10  200 5000    1240    2   2018-04-17 11:57:30.827
80  542 9000    5700    1   2018-04-17 11:57:23.710
80  542 9000    5700    2   2018-04-17 11:57:27.767

But what I really want is this:

但我真正想要的是:

uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 11:57:22.693
10  200 5000    1240    2   2018-04-17 11:57:25.740

10  200 5000    1240    1   2018-04-17 11:57:29.797
10  200 5000    1240    2   2018-04-17 11:57:30.827

80  542 9000    5700    1   2018-04-17 11:57:23.710
80  542 9000    5700    2   2018-04-17 11:57:27.767

Final Answer (thanks scsimon)

最终答案(感谢scsimon)

'Matched'

“匹配”

select t.uID, t.aID, t.cID, t.pID, t.state, t.occurs
from Data t
inner join
    (select uID, aID, cID, pID
     from Data
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) > 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID
 order by uID, occurs, state

Returns a set of :

返回一组:

uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 11:57:22.693
10  200 5000    1240    2   2018-04-17 11:57:25.740
10  200 5000    1240    1   2018-04-17 11:57:29.797
10  200 5000    1240    2   2018-04-17 11:57:30.827
80  542 9000    5700    1   2018-04-17 11:57:23.710
80  542 9000    5700    2   2018-04-17 11:57:27.767

'Unmatched'

“无与伦比的”

select t.uID, t.aID, t.cID, t.pID, t.state, t.occurs
from Data t
inner join
    (select uID, aID, cID, pID
     from Data
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) = 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID
 order by occurs

returns a set:

返回一组:

uID aID cID pID state   occurs
10  240 5000    3860    1   2018-04-17 11:57:24.727
30  240 7000    5938    1   2018-04-17 11:57:26.753

3 个解决方案

#1


2  

Assuming each unique coupling of uID, aID, cID, pID will only be in these states once, you could use this.

假设uID、aID、cID、pID的每个唯一耦合将只在这些状态中存在一次,您可以使用这个。

--matching rows
select t.*
from table t
inner join
    (select uID, aID, ,cID, pID
     from table
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) > 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID


 --unmatching rows
select t.*
from table t
inner join
    (select uID, aID, ,cID, pID
     from table
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) = 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID

#2


1  

I was able to replicate your expected output using exists and not exists

我可以使用exist而不存在来复制您的预期输出

--Matched row code
SELECT * 
  FROM Data D1
 WHERE EXISTS (SELECT *
                 FROM Data D2
                WHERE D2.uID = D1.uID AND D2.aID = D1.aID AND D2.cID = D1.cID
                  AND D2.state = 2)
ORDER BY uID, state

--Unmatched row code    
SELECT * 
  FROM Data D1
 WHERE NOT EXISTS (SELECT *
                     FROM Data D2
                    WHERE D2.uID = D1.uID AND D2.aID = D1.aID AND D2.cID = D1.cID
                      AND D2.state = 2)
ORDER BY uID, state

#3


1  

How about this. I'm assuming here there are only 2 states 1 and 2.

这个怎么样。我假设这里只有两种状态1和2。

SELECT  a.*
FROM    Data a
  JOIN (SELECT uID, aID, cID, pID, Count(*) as NumMatches
        FROM   Data
        Group By uID, aID, cID, pID
        Having Count(*) = 2) b ON a.uID = b.uID and a.aID = b.aID and a.cID = b.cID and a.pID = b.pID
Order by a.uID, a.aID, a.cID, a.pID 

and for non matches

和非匹配

SELECT  a.*
FROM    Data a
  LEFT OUTER JOIN (SELECT uID, aID, cID, pID, Count(*) as NumMatches
        FROM   Data
        Group By uID, aID, cID, pID
        Having Count(*) = 2) b ON a.uID = b.uID and a.aID = b.aID and a.cID = b.cID and a.pID = b.pID
WHERE IsNull(b.uID,0) = 0
Order by a.uID, a.aID, a.cID, a.pID   

#1


2  

Assuming each unique coupling of uID, aID, cID, pID will only be in these states once, you could use this.

假设uID、aID、cID、pID的每个唯一耦合将只在这些状态中存在一次,您可以使用这个。

--matching rows
select t.*
from table t
inner join
    (select uID, aID, ,cID, pID
     from table
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) > 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID


 --unmatching rows
select t.*
from table t
inner join
    (select uID, aID, ,cID, pID
     from table
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) = 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID

#2


1  

I was able to replicate your expected output using exists and not exists

我可以使用exist而不存在来复制您的预期输出

--Matched row code
SELECT * 
  FROM Data D1
 WHERE EXISTS (SELECT *
                 FROM Data D2
                WHERE D2.uID = D1.uID AND D2.aID = D1.aID AND D2.cID = D1.cID
                  AND D2.state = 2)
ORDER BY uID, state

--Unmatched row code    
SELECT * 
  FROM Data D1
 WHERE NOT EXISTS (SELECT *
                     FROM Data D2
                    WHERE D2.uID = D1.uID AND D2.aID = D1.aID AND D2.cID = D1.cID
                      AND D2.state = 2)
ORDER BY uID, state

#3


1  

How about this. I'm assuming here there are only 2 states 1 and 2.

这个怎么样。我假设这里只有两种状态1和2。

SELECT  a.*
FROM    Data a
  JOIN (SELECT uID, aID, cID, pID, Count(*) as NumMatches
        FROM   Data
        Group By uID, aID, cID, pID
        Having Count(*) = 2) b ON a.uID = b.uID and a.aID = b.aID and a.cID = b.cID and a.pID = b.pID
Order by a.uID, a.aID, a.cID, a.pID 

and for non matches

和非匹配

SELECT  a.*
FROM    Data a
  LEFT OUTER JOIN (SELECT uID, aID, cID, pID, Count(*) as NumMatches
        FROM   Data
        Group By uID, aID, cID, pID
        Having Count(*) = 2) b ON a.uID = b.uID and a.aID = b.aID and a.cID = b.cID and a.pID = b.pID
WHERE IsNull(b.uID,0) = 0
Order by a.uID, a.aID, a.cID, a.pID