SQL Server Select values with Condition

50few1ms  于 2023-03-28  发布在  其他
关注(0)|答案(4)|浏览(167)

I have a table with this sample data, where Id and productname are columns. I need an output where it returns Product1 when it has same Id along with other ProductNames (see the next table for the output).
| Id | ProductName |
| ------------ | ------------ |
| ABC123 | Product1 |
| ABC123 | Product2 |
| XYZ345 | Product1 |
| PQR123 | Product1 |
| MNP789 | Product3 |
| EFG456 | Product1 |
| EFG456 | Product6 |
| EFG456 | Product7 |
| JKL909 | Product8 |
| JKL909 | Product8 |
| JKL909 | Product8 |
| DBC778 | Product9 |
| DBC778 | Product10 |

Desired output:

IdProductName
ABC123Product1
ABC123Product2
EFG456Product1
EFG456Product6
EFG456Product7

Basically it's grouped by Id when it has Product1 with other products.

I tired following query but its not giving desired result

select Id, ProductName 
from tbl1 
group by Id, ProductName 
having count(ProductName) > 1

Thanks in advance

jjhzyzn0

jjhzyzn01#

Two typical options are:

select * from T t
where exists (
    select 1 from T t2
    where t2.Id = t.Id and t2.ProductName <> t.ProductName
);

with data as (
    select *, count(*) over (partition by Id) as cnt
    from T
)
select * from data where cnt > 1;
ef1yzkbh

ef1yzkbh2#

I reckon that, you had better to group the table without the having. The Query will be like the following:

select * 
from tbl1 
group by Id, ProductName

hope this solve your question

vsdwdz23

vsdwdz233#

Below is one method to accomplish the task. The DISTINCT is not required with your sample data but is needed in case there are dups, which might be possible based on the sample data for other products.

SELECT DISTINCT Id, ProductName
FROM @Product
WHERE Id IN(
    SELECT Id
    FROM @Product
    WHERE ProductName = 'Product1'
    )
    AND Id IN(
    SELECT Id
    FROM @Product
    WHERE ProductName <> 'Product1'
    )
ORDER BY Id, ProductName;
dw1jzc5e

dw1jzc5e4#

Using COUNT(*) OVER (PARTITION BY Id) will give you the HAVING -like functionality.

WITH cte AS (
    SELECT Id,
           ProductName,
           NumProductsForId = COUNT(*) OVER (PARTITION BY Id),
           DoesIdContainDesiredProductName = MAX(CASE WHEN ProductName = 'Product1' THEN 1 END) OVER (PARTITION BY Id)
    FROM tbl1
)
SELECT Id, ProductName
FROM cte
WHERE NumProductsForId > 1
  AND DoesIdContainDesiredProductName = 1;

相关问题