sql-server - 如果 SQL 中的不同列包含多行，如何识别人员或 ID

Question

我有一个表，其中一个人在另一列中多次包含相同的值。

例如：

  person     product    portal  count    indicator
  -----------------------------------------------
      1        10        5         2        y
      1        10        6         2        y
      1        15        7         1        y


select person, count(person) over(partition by product) 
from table A 

如果一个人多次包含相同的产品，那么在这种情况下，我想识别这样的人群并想给它某种类型的指标。

在上面的示例中，产品 10 出现了多次，所以我想为所有行的人提供一个指示符。

标签： sql-serveroracleamazon-redshift-spectrum

Answer 1

WITH  C AS  
(SELECT    person, product, count(1) as Duplicate_product   
FROM  [table] A
GROUP  BY  person, product),
B AS 
(SELECT DISTINCT  person   
FROM  [table] A
GROUP BY person, product
HAVING COUNT(1) > 1)

SELECT  t.person, t.product, t.portal, 
CASE WHEN  B.person is null THEN 'N' ELSE 'Y' END  AS  indicator, ISNULL(C.Duplicate_product, 1) AS  [count] 

FROM  [table]  AS  t
INNER JOIN C ON t.person = C.person AND t.product = C.product
LEFT  JOIN B ON t.person = B.person;