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