IF OBJECT_ID('tempdb..#tem','U') IS NOT NULL DROP TABLE #tem CREATE TABLE #tem ( ID INT IDENTITY(1,1) ,num1 INT ,num2 INT ,num3 INT ) INSERT INTO #tem VALUES (0,1,1),(1,0,0),(2,0,0),(3,1,0),(0,2,1),(0,3,2),(1,0,3),(0,1,4),(0,2,5),(0,3,0) ,(1,0,0),(0,1,1),(0,2,2),(1,3,0),(0,0,1),(1,0,2),(2,1,0),(0,0,1),(0,1,2) SELECT * FROM ( SELECT * ,CASE WHEN num1=num2 AND num1<>num3 THEN 1 WHEN num1=num3 AND num1<>num2 THEN 1 WHEN num2=num3 AND num1<>num2 THEN 1 ELSE 0 END AS x FROM #tem ) t WHERE x=1
注意看需求,要2列相同,某列不同的。你的这个代码,会把3列全部相同的都查出来额 你确定??? 我测试,三列全同时相同的貌似查不出来啊IF OBJECT_ID('tempdb..#tem','U') IS NOT NULL DROP TABLE #tem CREATE TABLE #tem ( ID INT IDENTITY(1,1) ,num1 INT ,num2 INT ,num3 INT ) INSERT INTO #tem VALUES (1,1,1),(0,0,0),(0,0,0),(0,0,0) --,(0,2,1),(0,3,2),(1,0,3),(0,1,4),(0,2,5),(0,3,0) --,(1,0,0),(0,1,1),(0,2,2),(1,3,0),(0,0,1),(1,0,2),(2,1,0),(0,0,1),(0,1,2) SELECT * FROM #tem SELECT * FROM ( SELECT * ,CASE WHEN num1=num2 AND num1<>num3 THEN 1 WHEN num1=num3 AND num1<>num2 THEN 1 WHEN num2=num3 AND num1<>num2 THEN 1 ELSE 0 END AS x FROM #tem ) t WHERE x=1
IF OBJECT_ID('tempdb..#tem','U') IS NOT NULL DROP TABLE #tem
CREATE TABLE #tem
(
ID INT IDENTITY(1,1)
,num1 INT
,num2 INT
,num3 INT
)
INSERT INTO #tem
VALUES (0,1,1),(1,0,0),(2,0,0),(3,1,0),(0,2,1),(0,3,2),(1,0,3),(0,1,4),(0,2,5),(0,3,0)
,(1,0,0),(0,1,1),(0,2,2),(1,3,0),(0,0,1),(1,0,2),(2,1,0),(0,0,1),(0,1,2)
SELECT *
FROM (
SELECT *
,CASE WHEN num1=num2 AND num1<>num3 THEN 1
WHEN num1=num3 AND num1<>num2 THEN 1
WHEN num2=num3 AND num1<>num2 THEN 1
ELSE 0 END AS x
FROM #tem ) t WHERE x=1
我测试,三列全同时相同的貌似查不出来啊IF OBJECT_ID('tempdb..#tem','U') IS NOT NULL DROP TABLE #tem
CREATE TABLE #tem
(
ID INT IDENTITY(1,1)
,num1 INT
,num2 INT
,num3 INT
)
INSERT INTO #tem
VALUES (1,1,1),(0,0,0),(0,0,0),(0,0,0)
--,(0,2,1),(0,3,2),(1,0,3),(0,1,4),(0,2,5),(0,3,0)
--,(1,0,0),(0,1,1),(0,2,2),(1,3,0),(0,0,1),(1,0,2),(2,1,0),(0,0,1),(0,1,2)
SELECT * FROM #tem SELECT *
FROM (
SELECT *
,CASE WHEN num1=num2 AND num1<>num3 THEN 1
WHEN num1=num3 AND num1<>num2 THEN 1
WHEN num2=num3 AND num1<>num2 THEN 1
ELSE 0 END AS x
FROM #tem ) t WHERE x=1