日期:2014-05-18  浏览次数:20516 次

关于sql 数据清理的问题
现在假设有一张学生住宿信息表,sql字段有 id(不是学号),房号,上次时间,本次时间,住宿名单1,住宿名单2,住宿名单3,住宿名单4, 要怎么通过这些字段找出是否有同名的学生。

------解决方案--------------------
SQL code
with cte as 
(
select *, row_number() over( order by getdate()) as id from 
(
select 住宿名单1 as col from tb
union all
select 住宿名单2 from tb
union all
select 住宿名单3 from tb
union all
select 住宿名单4 from tb
) as t 
)

select * from cte as a where exists (
   select * from cte as b 
      where a.col = b.col and 
            a.id <> b.id
)