SQL Server 查询，用于查找在一列上具有聚合功能但在选择子句中具有多个列的记录

Question

这是客户表的最小化版本。可以有将相同帐号映射到不同组的客户。我正在寻找映射到多个组的客户编号。当我使用 sybase 时，我下面的查询工作正常。相同的查询在 SQL Server 中不起作用。

我可以在一个查询中同时获得custAccnt和相应的custId吗，如下所示。

select DISTINCT lt.custAccnt, lt.custId from VAL_CUSTOMERS lt 
where lt.eligible = 'Y' group by lt.custAccnt 
having count(distinct lt.custId) > 1
+----------+-----------+---------+----------+
| custName | custAccnt | custId  | eligible |
+----------+-----------+---------+----------+
| Joe      | AB1VU1235 |   43553 | Y        |
| Joe      | AB1VU1235 |  525577 | Y        |
| Lucy     | CDNMY4568 |  332875 | Y        |
| Lucy     | CDNMY4568 |  211574 | Y        |
| Lucy     | CDNMY4568 |  211345 | Y        |
| Manie    | TZMM7S009 |  123890 | Y        |
| Tom      | YFDU1235  | 1928347 | Y        |
| Tom      | YFDU1235  |  204183 | Y        |
| Chef     | TNOTE6573 |  734265 | Y        |
+----------+-----------+---------+----------+

结果：-

+-----------+---------+
| AB1VU1235 |   43553 |
| AB1VU1235 |  525577 |
| CDNMY4568 |  332875 |
| CDNMY4568 |  211574 |
| CDNMY4568 |  211345 |
| YFDU1235  | 1928347 |
| YFDU1235  |  204183 |
+-----------+---------+

Answer 1

有很多方法可以解决这个问题。这里有几个应该有效。

select lt.custAccnt
    , lt.custId 
from VAL_CUSTOMERS lt 
cross apply
(
    select c.custAccnt
    from VAL_CUSTOMERS c
    where c.custAccnt = lt.custAccnt
    group by c.custAccnt
    having count(*) > 1
) x
where lt.eligible = 'Y'

select lt.custAccnt
    , lt.custId 
from VAL_CUSTOMERS lt 
where lt.eligible = 'Y'
AND lt.custAccnt IN
(
    select c.custAccnt
    from VAL_CUSTOMERS c
    group by c.custAccnt
    having count(*) > 1
) 

Answer 2

如果表中有重复的custAccnt和custId，@Sean查询将不起作用。

WITH cte AS(SELECT *
                 , COUNT (custId) OVER (PARTITION BY custAccnt) AS CntcustId
                 , ROW_NUMBER () OVER (PARTITION BY custAccnt, custId ORDER BY custName)    AS Rownum
                 FROM VAL_CUSTOMERS
                 WHERE eligible = 'Y'
)
SELECT custAccnt, custId
FROM cte
WHERE CntcustId>1
AND Rownum = 1;

使用行号消除重复项。

Answer 3

我认为这可能会起作用...

"...映射到多个组的客户编号..." ， <-- group custAcct？

select t.custAccnt, t.custId 
from VAL_CUSTOMERS t 
where (Select count(distinct custAccnt ) 
       from VAL_CUSTOMERS 
       Where custId = t.custId) > 1

声明"...映射到多个组的客户编号..."没有说任何关于"资格"的事情，所以我没有提到它。 如果你真的想说：

"...映射到多个组的合格客户编号..."，请尝试以下操作：

select t.custAccnt, t.custId 
from VAL_CUSTOMERS t 
where eligible = 'Y'
  and (Select count(distinct custAccnt ) 
       from VAL_CUSTOMERS 
       Where custId = t.custId) > 1

或者，这可能更快...它回答了一个略有不同的，但是，(我认为(等效的问题，
"找...符合条件的客户编号，其中同一客户编号有另一行映射到不同的客户帐户...">

select t.custAccnt, t.custId 
from VAL_CUSTOMERS t 
where eligible = 'Y'
  and exists 
      (Select * from VAL_CUSTOMERS 
       Where custId = t.custId
         and custAccnt != t.custAccnt ) 

Answer 4

;WITH cte1
( custName , custAccnt , custId  , eligible )
As
(
 SELECT 'Joe'      ,'AB1VU1235' ,   43553 , 'Y'    UNION ALL
 SELECT 'Joe'      ,'AB1VU1235' ,  525577 , 'Y'    UNION ALL
 SELECT 'Lucy'     ,'CDNMY4568' ,  332875 , 'Y'    UNION ALL
 SELECT 'Lucy'     ,'CDNMY4568' ,  211574 , 'Y'    UNION ALL
 SELECT 'Lucy'     , 'CDNMY4568' ,  211345 , 'Y'   UNION ALL
 SELECT 'Manie'    ,'TZMM7S009' ,  123890 , 'Y'    UNION ALL
 SELECT 'Tom'      ,'YFDU1235'  , 1928347 , 'Y'    UNION ALL
 SELECT 'Tom'      ,'YFDU1235'  ,  204183 , 'Y'    UNION ALL
 SELECT 'Chef'     ,'TNOTE6573' ,  734265 , 'Y'    
  )
,cte2 AS (
    SELECT custName
          ,custAccnt
          ,count(custName) cnt
    FROM cte1
    GROUP BY custName,custAccnt
    )
,cte3 AS (
    SELECT custName
        ,cnt
    FROM cte2 WHERE cnt <> 1
         )
SELECT custAccnt
    ,custId
FROM cte1
WHERE custName IN (
        SELECT custName
        FROM cte3
        )