用mssql写的,和mysql差不多 --测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([姓名] int,[身份证] nvarchar(23),[手机] nvarchar(23)) Insert #T select 1,N'空',N'123' union all select 2,N'888',N'空' union all select 1,N'777',N'空' union all select 2,N'空',N'00' Go --测试数据结束 SELECT DISTINCT * FROM ( SELECT a.姓名, CASE WHEN a.身份证='空'THEN b.身份证 ELSE a.身份证 end AS 身份证, CASE WHEN a.手机='空'THEN b.手机 ELSE a.手机 end AS 手机 FROM #T a LEFT JOIN #T b ON b.姓名 = a.姓名 AND b.手机 <> a.手机 AND b.身份证 <> a.身份证 )t
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] int,[身份证] nvarchar(23),[手机] nvarchar(23))
Insert #T
select 1,N'空',N'123' union all
select 2,N'888',N'空' union all
select 1,N'777',N'空' union all
select 2,N'空',N'00'
Go
--测试数据结束
SELECT DISTINCT * FROM (
SELECT
a.姓名,
CASE WHEN a.身份证='空'THEN b.身份证 ELSE a.身份证 end AS 身份证,
CASE WHEN a.手机='空'THEN b.手机 ELSE a.手机 end AS 手机
FROM
#T a
LEFT JOIN
#T b
ON b.姓名 = a.姓名
AND b.手机 <> a.手机
AND b.身份证 <> a.身份证
)t