请帮助。我有以下 Select 查询，并且只想选择 A.Name 列重复超过 1 次的行：
SELECT
A.Payer,
A.PaymentDate,
A.Name
FROM
(SELECT
T.InstitutionRoleXrefLongName AS 'Payer',
T.PaymentDate AS 'PaymentDate',
T.FullName AS 'Name'
FROM
Transfer T
UNION ALL
SELECT
T.InstitutionRoleXrefLongName AS 'Payer',
T.PaymentDate AS 'PaymentDate',
T.FullName AS 'Name'
FROM
TransferClosed T) A
WHERE
PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'
使用CTE和self join得到count大于 1的那个。
你可以试试这个。
;with CTE AS (
SELECT
A.Payer,
A.PaymentDate,
A.Name
FROM (
SELECT
T.InstitutionRoleXrefLongName AS 'Payer',
T.PaymentDate AS 'PaymentDate',
T.FullName AS 'Name'
FROM Transfer T
UNION ALL
SELECT
T.InstitutionRoleXrefLongName AS 'Payer',
T.PaymentDate AS 'PaymentDate',
T.FullName AS 'Name'
FROM TransferClosed T
) A
WHERE PaymentDate Between '20180101' AND '20180331 23:59:59'
)
select t2.*
from (
SELECT name,count(1) totle
FROM CTE
GROUP BY Name
) t1 inner join CTE t2
ON t1.totle > 1 and t1.Name = t2.Name
sqlfiddleCTE模拟你的结果集
sqlfiddle：http ://sqlfiddle.com/#!18/cc68f/9
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句