SQL简单查询语句问题!

lmhosts 2008-04-22 10:13:39
例子 表:plat_uidlist


Uid Areaid State
2335435 8 0
2335436 8 1
2335437 2 2
2335438 8 3
2335439 3 0
2335440 8 0
6677889 8 0
1234578 4 0
4980010 8 0
4980099 8 0



我要写一条语句 以下是要求

号码筛选规则
1、 UID中任意位置出现"888"、"666"、"999"的号码预留。
2、 UID中任意位置出现"AAAA"的号码预留。
3、 UID后四位号码出现以下排列的预留:"ABCD"、"AABB"、"ABAB"、"AAAB"、"ABBB"。
4、 4980000 - 4989999 段内UID预留
5 areaid=8 state<>3



以下是我写的语句 大家帮参考下 是否合理 我求最加优化语句 谢谢

select * from dbo.PLAT_UIDList
where state<>'3' and areaid='8' and
(uid between '4980000' and '4989999' and
uid like '%888%' or uid like '%666%' or uid like '%999%')
or uid like '%AAAA%'
or right(uid,4) in ('ABCD','AABB','ABAB','AAAB','ABBB')

我基础不好 我就迷糊在括号上拉!呵呵 大家提下意见 这么写有毛病不 是否合理

...全文
83 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lmhosts 2008-04-22
我按上面 朋友 的语句执行了下 得出的结果
会查询出
UID
0215780
0250986
0260731

这结果 不是我的想要的规则啊

号码筛选规则
1、 UID中任意位置出现"888"、"666"、"999"的号码预留。
2、 UID中任意位置出现"AAAA"的号码预留。
3、 UID后四位号码出现以下排列的预留:"ABCD"、"AABB"、"ABAB"、"AAAB"、"ABBB"。
4、 4980000 - 4989999 段内UID预留
回复
lmhosts 2008-04-22
谁能在我写的语句基础上 进行更改 ...
楼上的方法 用CHARINDEX函数! 虽然很精确 但是我这里不适用
回复
kk19840210 2008-04-22
create table plat_uidlist(Uid varchar(10) , Areaid int , State int)
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )



select * from plat_uidlist
where
charindex('888',uid)>0 or charindex('666',uid)>0 or charindex('999',uid)>0 --1、 UID中任意位置出现"888"、"666"、"999"的号码预留。
or
right(uid,4)%1111=0 --2、 UID中任意位置出现"AAAA"的号码预留
or
charindex(right(uid,4),'0123456789')>0 or (right(uid,2)%11=0 and left(right(uid,4),2)%11=0) or cast(left(right(uid,4),2) as int )-cast(right(uid,2)as int)=0 or left(right(uid,4),3)%111=0 or right(uid,3)%111=0 --3、 UID后四位号码出现以下排列的预留:"ABCD"、"AABB"、"ABAB"、"AAAB"、"ABBB"。
or
uid between 4980000 and 4989999--4、 4980000 - 4989999 段内UID预留
or
(areaid=8 and [state]<>3 )--5 areaid=8 state <>3

Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0

(6 行受影响)
回复
dawugui 2008-04-22
create table plat_uidlist(Uid varchar(10) , Areaid int , State int)
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )
go

--符合条件的
select * from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
/*
Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0

(所影响的行数为 6 行)
*/

--不符合条件的
select * from plat_uidlist where Uid not in
(
select Uid from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
)
/*
Uid Areaid State
---------- ----------- -----------
2335437 2 2
2335438 8 3
2335439 3 0
1234578 4 0

(所影响的行数为 4 行)
*/

--drop table plat_uidlist
回复
dawugui 2008-04-22
create table plat_uidlist(Uid varchar(10) , Areaid int , State int)
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )
go

select * from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
/*
Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0

(所影响的行数为 6 行)
*/

drop table plat_uidlist
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2008-04-22 10:13
社区公告
暂无公告