使用JPA从数据库的其余部分的列中查找重复项

Question

我的数据库中有几列。我想选择一列，然后返回所有值重复的记录。所以我想得到一列，检查我的列中的哪些值出现在数据库的其余部分。然后返回这些记录。假设数据库看起来像这样：

sql prettyprint-override">id;col1;col2;col3;col4

'1','ab','cd','ef','1'
'2','ad','bg','ee','5'
'3','xx','bg','cc','6'
'4','vv','zz','ff','4'
'5','zz','ee','gg','4'
'6','zz','vv','zz','2'
'7','vv','aa','bb','8'
'8','ww','nn','zz','4'
'9','zz','yy','ff','9'
'10','qq','oo','ii','3'

我希望col1的结果是这样的

4,'vv','zz','ff',4
5,'zz','ee','gg',4
6,'zz','vv','zz',2
7,'vv','aa','bb',8
9,'zz','yy','ff',9

Answer 1

在这里，我们以两种不同的方式呈现副本。第一种是您要求的格式，带有附加信息。第二种更简洁。

create table t1(
id varchar(10),
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10));

insert into t1 values
('1','ab','cd','ef','1'),
('2','ad','bg','ee','5'),
('3','xx','bg','cc','6'),
('4','vv','zz','ff','4'),
('5','zz','ee','gg','4'),
('6','zz','vv','zz','2'),
('7','vv','aa','bb','8'),
('8','ww','nn','zz','4'),
('9','zz','yy','ff','9'),
('10','qq','oo','ii','3');

select * from t1;

id | col1 | col2 | col3 | col4
:- | :--- | :--- | :--- | :---
1  | ab   | cd   | ef   | 1   
2  | ad   | bg   | ee   | 5   
3  | xx   | bg   | cc   | 6   
4  | vv   | zz   | ff   | 4   
5  | zz   | ee   | gg   | 4   
6  | zz   | vv   | zz   | 2   
7  | vv   | aa   | bb   | 8   
8  | ww   | nn   | zz   | 4   
9  | zz   | yy   | ff   | 9   
10 | qq   | oo   | ii   | 3   

with cte as(
select
  id,
  col1,
  col2,
  col3,
  col4,
  row_number() over 
    ( partition by col1
      order by id desc) r1,
row_number() over 
    ( partition by col2
      order by id desc) r2,
row_number() over 
    ( partition by col3
      order by id desc) r3,
row_number() over 
    ( partition by col4
      order by id desc) r4
from t1 
 )
select * 
from cte
where
r1 > 1
or r2 > 1
or r3 > 1
or r4 > 1;

id | col1 | col2 | col3 | col4 | r1 | r2 | r3 | r4
:- | :--- | :--- | :--- | :--- | -: | -: | -: | -:
6  | zz   | vv   | zz   | 2    |  2 |  1 |  2 |  1
5  | zz   | ee   | gg   | 4    |  3 |  1 |  1 |  2
4  | vv   | zz   | ff   | 4    |  2 |  1 |  2 |  3
2  | ad   | bg   | ee   | 5    |  1 |  2 |  1 |  1

select 'col1' as "column",
   col1 "value", 
   count(id) "count"
from t1 group by col1
having count(id)>1
union all
select 'col2',col2, count(id)
from t1 group by col2
having count(id)>1
union all
select 'col3',col3, count(id)
from t1 group by col3
having count(id)>1
union all
select 'col4',col4, count(id)
from t1 group by col4
having count(id)>1
order by "column","value";

column | value | count
:----- | :---- | ----:
col1   | vv    |     2
col1   | zz    |     3
col2   | bg    |     2
col3   | ff    |     2
col3   | zz    |     2
col4   | 4     |     3

db