select name from syscolumns where id = object_id('tb2') select name from syscolumns where id = (select id from sysobjects where type = 'u' and name = 'tb2') 可查询所有字段名
create table #A(a1 nvarchar(10), a2 nvarchar(10), a3 nvarchar(10), a4 nvarchar(10)) insert #A select N'张小姐', N'李小姐', N'易小姐', N'任小姐' create table #B(ID INT, a1 nvarchar(10), a2 nvarchar(10), a3 nvarchar(10), a4 nvarchar(10)) insert #B select 23, N'张小姐', N'李小姐', NULL, NULL UNION ALL select 24, N'张小姐', NULL, NULL, NULL UNION ALL select 25, N'张小姐', N'李小姐', N'易小姐', NULL GO --SQL: select a.ID, field='a'+CAST(a.field AS VARCHAR(10)), b.name from ( SELECT id, field = (case when a.a1 = b.a1 then 1 else 0 end) + (case when a.a2 = b.a2 then 1 else 0 end) + (case when a.a3 = b.a3 then 1 else 0 end) + (case when a.a4 = b.a4 then 1 else 0 end) + 1 FROM #A a, #B b ) a cross apply ( select rowno, [name] from (select rowno=ROW_NUMBER() over(order by getdate()),* from #A m unpivot ([name] for bb in([a1], [a2], [a3], [a4])) n) c where c.rowno = a.field ) b /* ID field name 24 a2 李小姐 23 a3 易小姐 25 a4 任小姐 */
select name from syscolumns where id = (select id from sysobjects where type = 'u' and name = 'tb2') 可查询所有字段名
insert #A
select N'张小姐', N'李小姐', N'易小姐', N'任小姐'
create table #B(ID INT, a1 nvarchar(10), a2 nvarchar(10), a3 nvarchar(10), a4 nvarchar(10))
insert #B
select 23, N'张小姐', N'李小姐', NULL, NULL UNION ALL
select 24, N'张小姐', NULL, NULL, NULL UNION ALL
select 25, N'张小姐', N'李小姐', N'易小姐', NULL
GO
--SQL:
select a.ID, field='a'+CAST(a.field AS VARCHAR(10)), b.name from
(
SELECT
id,
field = (case when a.a1 = b.a1 then 1 else 0 end) +
(case when a.a2 = b.a2 then 1 else 0 end) +
(case when a.a3 = b.a3 then 1 else 0 end) +
(case when a.a4 = b.a4 then 1 else 0 end) + 1
FROM #A a, #B b
) a
cross apply
(
select rowno, [name] from
(select rowno=ROW_NUMBER() over(order by getdate()),* from #A m unpivot ([name] for bb in([a1], [a2], [a3], [a4])) n) c
where c.rowno = a.field
) b
/*
ID field name
24 a2 李小姐
23 a3 易小姐
25 a4 任小姐
*/