给出2种解决方案
rownumber
SELECT * FROM ( SELECT IdentityID, OpenID, ROW_NUMBER() OVER(PARTITION BY OpenID ORDER BY CreateTime DESC ) AS rownumber FROM dbo.T_Account ) AS tmp WHERE tmp.rownumber = 1
相关子查询
SELECT DISTINCT OpenID, test1.IdentityID FROM dbo.T_Account AS test1 WHERE test1.IdentityID IN ( SELECT TOP 1 IdentityID FROM dbo.T_Account WHERE dbo.T_Account.OpenID = test1.OpenID ORDER BY CreateTime DESC )
查询结果