且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

简单的SQL查询where子句问题

更新时间:2022-11-27 13:57:40

如果要从结果集中消除行,请使用非相等而不是相等。例如

 选择 * 来自 temptable 其中(第一个<>  10   second<>  4 ); 


  DECLARE   @ TestTable   AS   TABLE (Value1  INT ,Value2  INT 

INSERT INTO @ TestTable ( Value1,Value2)
VALUES 10 4 ),( 11 4 ),( 10 an>, 4 ),( 10 5 ),( 10 6 ),( 11 4

- 返回所有值

SELECT Value1,Value2 FROM @ TestTable

- Value1 Value2
- 10 4
-
11 4
- 10 4
- 10 5
- 10 6
- 11 4


- 仅返回10和4列

SELECT Value1,Value2 FROM @ TestTable
WHERE (Value1 = 10 AND Value2 = 4


- Value1 Value2
- 10 4
- 10 4


- 删除第一列值为10且第二列值为4的行

SELECT Value1,Value2 FROM @ TestTable
WHERE (Value1<> 10 Value2<> 4

- Value1 Value2
- 11 4
- 10 5
- 10 6
- 11 4


Hello All,

Conside following table

TEMPTABLE

Col : first       Second             

Data    10          4  

Data    11          4  

Data    10          4  

Data    10          5  

Data    10          6  

Data    11          4  



I want to remove rows whose first column value is 10 and second column value is 4.

I tried using

select * from temptable where (first = 10 and second = 4);

but it is removing all rows having first value as 10.Please let me know any better idea.

Instead of equality use non-equality if you want to eliminate the rows from the result set. For example
select * from temptable where (first <> 10 and second <> 4);


DECLARE @TestTable AS TABLE (Value1 INT,Value2 INT)

INSERT INTO @TestTable (Value1,Value2) 
VALUES (10,4), (11,4),(10,4 ),(10,5 ),(10,6),(11 ,4)

-- Return All Values 

SELECT Value1,Value2 FROM @TestTable 

--Value1	Value2
--	10		  4
--	11		  4
--	10	      4
--	10	      5
--	10	      6
--	11	      4


-- Return Only 10 And 4 Columns 

SELECT Value1,Value2 FROM @TestTable
WHERE (Value1 = 10 AND Value2 = 4)


	--Value1	Value2
	--	10		  4
	--	10		  4


--Remove rows whose first column value is 10 and second column value is 4

SELECT Value1,Value2 FROM @TestTable
WHERE (Value1 <> 10 OR Value2 <> 4)

	--Value1	Value2
	--		11	4
	--		10	5
	--		10	6
	--		11	4