如何判断，同一行，不同字段的值，是否相同？

[wxylvmnn]

比如我有表A，里面有字段a1,a2,a3,a4,a5

在，
字段不为null的前提下，
在同一行内，
如何判断
1，数据不是跳着数据的，比如输入a1,然后a2没输入，直接输入了a3
2，a1~a5这5个值，是否有任意2个值是相同的？

[gw6328]

declare @a table(id int,a1 int,a2 int,a3 int,a4 int,a5 int);

insert into @a select 1,1,3,5,2,6 union all 
select 2,2,4,0,5,6 union all 
select 3,1,3,3,4,5;

--select id,col,v from @a unpivot
--(v for col in ([a1],[a2],[a3],[a4],[a5])) upt

--1
;with cte as (
	select rn=row_number() over(partition by id order by col),id,col,v from @a unpivot
	(v for col in ([a1],[a2],[a3],[a4],[a5])) upt
)
	select * from cte a where exists(select 1 from cte where id=a.id and v=0 and (rn>a.rn))

--2
;with cte as (
	select rn=row_number() over(partition by id order by col),id,col,v from @a unpivot
	(v for col in ([a1],[a2],[a3],[a4],[a5])) upt
)
select * from cte a where exists(select 1 from cte where id=a.id and col<>a.col and v=a.v )

[Haytor]

参考一下

;WITH TabA(A,B,C,D,E) AS(
	SELECT 'A','B','C','','A' UNION ALL
	SELECT 'B','B','','A','' UNION ALL
	SELECT 'C','C','B','B','C' UNION ALL
	SELECT 'D','','C','B','A' UNION ALL
	SELECT 'A','E','D','C','B'
),
TabB AS (
	SELECT
		ROW_NUMBER() OVER(ORDER BY GETDATE()) AS RowID,
		* 
	FROM TabA
),
TabC AS(
	SELECT
		RowID,
		b.nCol,
		b.nValue
	FROM TabB a 
	UNPIVOT( nValue FOR nCol IN (A,B,C,D,E)) b
)
SELECT 
	a.A,a.B,a.C,a.D,a.E,
	'字段' 
	+ STUFF(( 
		SELECT 
			',' + QUOTENAME( b.nCol )
		FROM 
			TabC b 
		WHERE 
			b.RowID = a.RowID AND 
			b.nValue='' 
		FOR XML PATH('')
	),1,1,'')+ '没有输入数据' AS '说明1',
	'本行有'
	+STUFF(( 
		SELECT
			',' + LTRIM(COUNT(*)) +'个'+ LTRIM(c.nValue) 
		FROM 
			TabC c 
		WHERE 
			c.RowID = a.RowID AND
            c.nValue <> ''
		GROUP BY 
			c.nValue
		HAVING COUNT(*)>=2
		FOR XML PATH('')
	 ),1,1,'') AS '说明2'
FROM 
	TabB a

A	B	C	D	E	说明1	                                说明2
A	B	C		A	字段[D]没有输入数据	        本行有2个A
B	B		A		字段[C],[E]没有输入数据	本行有2个B
C	C	B	B	C	NULL	                               本行有2个B,3个C
D		C	B	A	字段[B]没有输入数据	        NULL
A	E	D	C	B	NULL	                                NULL

[薛定谔的DBA]

列转行分组统计吧

[jjjing1998]

拜读。。。。。。。。。。。。。。。。。。