提问人：Kannan 提问时间：10/31/2023 最后编辑：marc_sKannan 更新时间：10/31/2023 访问量：79
需要 SQL 查询来获取匹配和不匹配的记录
Need a SQL Query for Getting matched and not matched Records
问：
我的表格结构如下所示：
表1
| 科尔1 |
|---|
| 10 |
| 20 |
| 20 |
| 20 |
| 30 |
| 30 |
| 40 |
表2
| 科尔1 |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
我的预期结果是这样的：
| 科尔1 | 科尔2 |
|---|---|
| 10 | 匹配 |
| 20 | 匹配 |
| 20 | 不匹配 |
| 20 | 不匹配 |
| 30 | 匹配 |
| 30 | 不匹配 |
| 40 | 匹配 |
我尝试使用的查询：
SELECT
T1.Col1,
CASE
WHEN T2.Col1 IS NOT NULL THEN 'Matched'
ELSE 'NotMatched'
END AS Col2
FROM
Table1 T1
LEFT JOIN
Table2 T2 ON T1.Col1 = T2.Col1;
答：
1赞
Tim Biegeleisen
10/31/2023
#1
我们可以坚持使用您的联接方法，但用于跟踪事件：ROW_NUMBER
WITH cte1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
FROM Table1
),
cte2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
FROM Table2
)
SELECT T1.Col1,
CASE WHEN T2.Col1 IS NOT NULL THEN 'Matched' ELSE 'NotMatched' END AS Col2
FROM cte1 T1
LEFT JOIN cte2 T2
ON T1.Col1 = T2.Col1 AND T1.rn = T2.rn
ORDER BY T1.Col1, T2.Col1 DESC;
评论
0赞
Zohar Peled
10/31/2023
这将起作用，假设 OP 确实弄错了这个标签，并且他没有使用 2005 版本，而是使用一些更新的东西。（顺便说一句，我个人会用它来代替这个，但我认为这并不重要）rank()row_number()
-1赞
SelVazi
10/31/2023
#2
假设 table2 仅包含不同的值，您可以使用为每行分配一个唯一编号，然后仅为每个分区分配第一条记录：row_number()left join
select t1.Col1, CASE WHEN t2.Col1 IS NOT NULL THEN 'Matched' ELSE 'Not Matched' END AS Col2
from (
select *, row_number() over(partition by Col1 order by Col1 ) as rn
from table1
) as t1
left join table2 t2 ON t1.Col1 = t2.Col1 and rn = 1;
0赞
Vahid Heydarinezhad
10/31/2023
#3
CREATE TABLE Table1 (
Col1 INT
);
INSERT INTO Table1 (Col1) VALUES
(10),
(20),
(20),
(20),
(30),
(30),
(40);
CREATE TABLE Table2 (
Col1 INT
);
INSERT INTO Table2 (Col1) VALUES
(10),
(20),
(30),
(40);
select T1.col1,case when T2.col1 is null then 'NotMatched' else 'Matched' end col2
from
(
select col1 , ROW_NUMBER() OVER (PARTITION BY t.col1 ORDER BY t.col1 ) er
from table1 t
) as T1
left join
(
select t.col1 ,count(*) er2
from table1 t
group by t.col1
) as T2
on T1.col1=T2.col1 and T1.er=T2.er2
order by 1,2
;
0赞
Saikat
10/31/2023
#4
这是解决方案
select
a.col1 ,
case when a.col1 = b.col1 then 'Matched'
else 'Not Matched' end as col2
from
(
select * , row_number() over(partition by col1 order by col1) as seq from tab1
) as a left join (select col1 , row_number() over(partition by col1 order by col1) as seq from tab2) as b
on a.seq = b.seq and a.col1 = b.col1;
评论
Col1row_number()