SQL return only rows where value exists multiple times and other value is present-CodePudding

I have a table like this in MS SQL SERVER

 ------ ------ 
|  ID  | Cust |
 ------ ------ 
|   1  |  A   |
|   1  |  A   |
|   1  |  B   |
|   1  |  B   |
|   2  |  A   |
|   2  |  A   |
|   2  |  A   |
|   2  |  B   |
|   3  |  A   |
|   3  |  B   |
|   3  |  B   |
|   3  |  C   |
|   3  |  C   |
 ------ ------

I don't know the values in column "Cust" and I want to return all rows where the value of "Cust" appears multiple times and where at least one of the "ID" values is "1". Like this:

 ------ ------ 
|  ID  | Cust |
 ------ ------ 
|   1  |  A   |
|   1  |  A   |
|   1  |  B   |
|   1  |  B   |
|   2  |  A   |
|   2  |  A   |
|   2  |  A   |
|   2  |  B   |
|   3  |  A   |
|   3  |  B   |
|   3  |  B   |
 ------ ------

Any ideas? I can't find it.

CodePudding user response：

You may use COUNT window function as the following:

SELECT ID, Cust 
  FROM
  (
    SELECT ID, Cust, 
       COUNT(*) OVER (PARTITION BY Cust) cn,
       COUNT(CASE WHEN ID=1 THEN 1 END) OVER (PARTITION BY Cust) cn2
   FROM table_name
  ) T
WHERE cn>1 AND cn2>0
ORDER BY ID, Cust

COUNT(*) OVER (PARTITION BY Cust) to check if the value of "Cust" appears multiple times.

COUNT(CASE WHEN ID=1 THEN 1 END) OVER (PARTITION BY Cust) to check that at least one of the "ID" values is "1".

See a demo.