L1 L2
11111111 11111111
11111111 1111111
1111111 1111111
11111111 11111110
1111111 1111111
1111111 1111111
11111111 11111111
1111111 1111111
11111110 11111110
1111111 1111111
111111111 111111111
11111111 1111111
11111111 11111111
111111111 111111111
1111111 1111111
11111111 1111111
我有这两列数据我要查出 前两位 不为11 或者 最后一为 不为1的
where (left(l1,2) <>'11' or RIGHT(l2,1)<>'1')
为什么 我的这个 SQL 就是 达不到我这个要求呢各位大大 帮我看下是什么原因
11111111 11111111
11111111 1111111
1111111 1111111
11111111 11111110
1111111 1111111
1111111 1111111
11111111 11111111
1111111 1111111
11111110 11111110
1111111 1111111
111111111 111111111
11111111 1111111
11111111 11111111
111111111 111111111
1111111 1111111
11111111 1111111
我有这两列数据我要查出 前两位 不为11 或者 最后一为 不为1的
where (left(l1,2) <>'11' or RIGHT(l2,1)<>'1')
为什么 我的这个 SQL 就是 达不到我这个要求呢各位大大 帮我看下是什么原因
insert into @t select 11111111, 11111111
insert into @t select 11111111, 1111111
insert into @t select 1111111, 1111111
insert into @t select 11111111, 11111110
insert into @t select 1111111, 1111111
insert into @t select 1111111, 1111111
insert into @t select 11111111, 11111111
insert into @t select 1111111, 1111111
insert into @t select 11111110, 11111110
insert into @t select 1111111, 1111111
insert into @t select 111111111,111111111
insert into @t select 11111111, 1111111
insert into @t select 11111111, 11111111
insert into @t select 111111111,111111111
insert into @t select 1111111, 1111111
insert into @t select 11111111, 1111111select * from @t where left(l1,2) <>'11' or RIGHT(l2,1) <>'1'/*
L1 L2
----------- -----------
11111111 11111110
11111110 11111110
*/
insert into @t select 11111111, 11111111
insert into @t select 11111111, 1111111
insert into @t select 1111111, 1111111
insert into @t select 11111111, 11111110
insert into @t select 1111111, 1111111
insert into @t select 1111111, 1111111
insert into @t select 11111111, 11111111
insert into @t select 1111111, 1111111
insert into @t select 11111110, 11111110
insert into @t select 1111111, 1111111
insert into @t select 111111111,111111111
insert into @t select 11111111, 1111111
insert into @t select 11111111, 11111111
insert into @t select 111111111,111111111
insert into @t select 1111111, 1111111
insert into @t select 11111111, 1111111select * from @t where left(l1,2) <>'11' or RIGHT(rtrim(l2),1) <>'1'/*
L1 L2
---------- ----------
11111111 11111110
11111110 11111110
*/
--可以不用 LTRIM 和 RTRIM
select *
from (
select 11111111 L1, 11111111 L2 UNION all
select 11111111,1111111 UNION all
select 1111111,1111111 UNION all
select 11111111,11111110 UNION all
select 1111111,1111111 UNION all
select 1111111,1111111 UNION all
select 11111111,11111111 UNION all
select 1111111,1111111 ) a
where (left(l1,2) <>'11' or RIGHT(l2,1) <>'1')
/*--Result
L1 L2
11111111 11111110
*/--要用 LTRIM 和 RTRIM
select *
from (
select '11111110' L1,'11111110 ' L2 UNION all
select '1111111','1111111' UNION all
select '111111111','111111111 ' UNION all
select '11111111','1111111' UNION all
select '11111111','11111111' UNION all
select '111111111','111111111' UNION all
select '1111111','1111111' UNION all
select '11111111','1111111' ) b
where (left(LTRIM(l1),2) <>'11' or RIGHT(RTRIM(l2),1) <>'1')
/*--Result
L1 L2
11111110 11111110
*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(40),b varchar(40))
go
insert into tb
select
'11111111' , '11111111' union all select
'1211111' ,'1111111' union all select
'11111111' , '11111110' union all select
'1111111' , '1111111' union all select
'1111111' , '1111111' union all select
'11111111' , '11111111'
go
select *
from tb
where LEFT(a,2)<>'11' or RIGHT(b,1)<>1
/*------------
a b
---------------------------------------- ----------------------------------------
1211111 1111111
11111111 11111110(2 行受影响)
-------*/