当前位置: 动力学知识库 > 问答 > 编程问答 >

select - MySql - Selecting all records with the same field when one row matches the result

问题描述:

I have made a fiddle : http://sqlfiddle.com/#!9/b1f15

I Have this table

id | sid | product

---------------------

1 | ABC | 1

2 | ABC | 2

3 | ABC | 3

4 | BCD | 1

5 | BCD | 2

6 | XYZ | 2

7 | XYZ | 3

What i want is to get all SID's if one SID have the product = 3

The result i was looking is:

id | sid | product

---------------------

1 | ABC | 1

2 | ABC | 2

3 | ABC | 3

6 | XYZ | 2

7 | XYZ | 3

Im completely stuck, please help! Thanks

网友答案:

Use self-join:

SELECT t1.*
FROM sp_test t1
JOIN sp_test t2
ON t1.sid = t2.sid AND t2.product = '3';

Or EXISTS:

SELECT *
FROM sp_test t1
WHERE EXISTS (
    SELECT  1 FROM sp_test t2 WHERE t1.sid = t2.sid AND t2.product = '3'
);

SQLFiddle Demo

网友答案:

Another variation:

select 
  id, sid, product
from 
  sp_test x
where 
  exists (
    select 
      1
    from 
      sp_test y
    where 
      y.product = '3' 
      and x.sid = y.sid)
网友答案:

Use a simple nested query.

SELECT * FROM sp_test
WHERE sid IN (SELECT sid FROM sp_test WHERE product = 3);
网友答案:

Use simple left join as below:

select sp1.* from sp_test sp1 left join sp_test sp2 on sp1.sid=sp2.sid
where sp2.product=3;
网友答案:

Use conditional aggregation:

SELECT t1.id, t1.sid, t1.product
FROM sp_test t1
INNER JOIN
(
    SELECT sid
    FROM sp_test
    GROUP BY sid
    HAVING SUM(CASE WHEN product = 3 THEN 1 ELSE 0 END) > 0
) t2
    ON t1.sid = t2.sid

Follow the link below for a running demo:

SQLFiddle

分享给朋友:
您可能感兴趣的文章:
随机阅读: