是否可以在SQL SERVER中只获取具有不同值的列?
TABLE 1
id Name Desig
1 Ali Assistant.Manager
1 Ali Manager
2 John Manager
现在,如果我想检查id 1的状态,它应该返回Designation(即助理经理和经理)
试试这个:
SELECT T1.Desig
FROM TableName T1 JOIN
(SELECT id,name
FROM TableName
GROUP BY id,name
HAVING COUNT(DISTINCT Desig) > 1) T2
ON T1.id=T2.id AND T1.name=T2.name
结果:
DESIG
Assistant.Manager
Manager
请参阅SQL Fiddle中的结果。
如果id
是一个标识,正如您所评论的,您可以按照名称选择最新的一行,如:
select *
from (
select row_number() over (
partition by name
order by id desc) as rn
, *
) as SubQueryAlias
where rn = 1 -- Latest row per name