英文:
SQL Return only rows where the value is the same
问题
I want to see all records that have the same test_id number more than once. So from the above sample, I would like to see:
| recnr | test_id |
|---|---|
| 2 | 102 |
| 3 | 102 |
| 5 | 104 |
| 6 | 104 |
| 7 | 104 |
Anyone have an idea how to accomplish that?
Thanks in advance.
I did try to use group by and stuff but probably the wrong way.
英文:
i'm struggling to get rows where the value is the same in SQL.
Example
| recnr | test_id |
|---|---|
| 1 | 101 |
| 2 | 102 |
| 3 | 102 |
| 4 | 103 |
| 5 | 104 |
| 6 | 104 |
| 7 | 104 |
| 8 | 105 |
I want to see all records that has the same test_id number more then once. So from above sample i would like to see:
| recnr | test_id |
|---|---|
| 2 | 102 |
| 3 | 102 |
| 5 | 104 |
| 6 | 104 |
| 7 | 104 |
Anyone an idee how to accomplish that?
Thanks in advance.
I did try to use group by and stuf but probably the wrong way.
答案1
得分: 1
以下是翻译好的部分：
我们首先使用“group by”和“having count(1) > 1”获取重复的test_id列表，然后使用“inner join”获取预期的数据：
从我的表中选择 t.*
从 (
从我的表中选择 test_id
分组按 test_id
有 count(1) > 1
) 作为 s on s.test_id = t.test_id
这是在mysql上测试过的有效解决方案.. 演示链接
英文:
We first get the list of test_id that are duplicated using group by and having count(1) > 1 then using inner join we get the expected data :
select t.*
from mytable t
inner join (
select test_id
from mytable
group by test_id
having count(1) > 1
) as s on s.test_id = t.test_id
This is a working solution tested on mysql .. demo here
答案2
得分: 0
For MS SQL SERVER, We can use IN operator, Subquery, GroupBy and Having to get your desired result.
SELECT * FROM MyTable A WHERE A.test_id IN (SELECT B.test_id FROM MyTable B GROUP BY B.test_id HAVING COUNT(1) > 1);
You can use the following queries to test it on any online MS SQL SERVER compiler.
CREATE TABLE MyTable (
recnr int,
test_id int
);
-- insert
INSERT INTO MyTable(recnr,test_id) VALUES (0001,101);
INSERT INTO MyTable(recnr,test_id) VALUES (0002, 102);
INSERT INTO MyTable(recnr,test_id) VALUES (0003, 102);
INSERT INTO MyTable(recnr,test_id) VALUES (0004,103);
INSERT INTO MyTable(recnr,test_id) VALUES (0005, 104);
INSERT INTO MyTable(recnr,test_id) VALUES (0006, 104);
INSERT INTO MyTable(recnr,test_id) VALUES (0007,104);
INSERT INTO MyTable(recnr,test_id) VALUES (0008, 105);
-- fetch
SELECT * FROM MyTable A WHERE A.test_id IN (SELECT B.test_id FROM MyTable B GROUP BY B.test_id HAVING COUNT(1) > 1);
I have added an image for reference.
英文:
For MS SQL SERVER, We can use IN operator, Subquery, GroupBy and Having to get your desired result.
SELECT * FROM MyTable A WHERE A.test_id IN ( SELECT B.test_id FROM MyTable B GROUP BY B.test_id HAVING COUNT(1)>1 );
You can use following queries to test it on any online MS SQL SERVER compiler.
CREATE TABLE MyTable (
recnr int,
test_id int
);
-- insert
INSERT INTO MyTable(recnr,test_id) VALUES (0001,101);
INSERT INTO MyTable(recnr,test_id) VALUES (0002, 102);
INSERT INTO MyTable(recnr,test_id) VALUES (0003, 102);
INSERT INTO MyTable(recnr,test_id) VALUES (0004,103);
INSERT INTO MyTable(recnr,test_id) VALUES (0005, 104);
INSERT INTO MyTable(recnr,test_id) VALUES (0006, 104);
INSERT INTO MyTable(recnr,test_id) VALUES (0007,104);
INSERT INTO MyTable(recnr,test_id) VALUES (0008, 105);
-- fetch
SELECT * FROM MyTable A WHERE A.test_id IN (SELECT B.test_id FROM MyTable B GROUP BY B.test_id HAVING COUNT(1)>1) ;
GO
I have added image for reference.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论