有一张表mot 有三个字段id name addr13007234538 zhang shanghai
13764653323 wang shanghai
13523454346 liming beijing
15923905787 wang shanghai
13234789790 daqing shanghai
13134568750 zhangqi shanghai
13456783448 tonghua shanghai
分别存储手机号、姓名、地址我用这个语句 把手机号查出来
select id from mot where addr='shanghai'可我想要的结果是,把移动和联痛分开,如何做出呢???高手帮帮忙!
13764653323 wang shanghai
13523454346 liming beijing
15923905787 wang shanghai
13234789790 daqing shanghai
13134568750 zhangqi shanghai
13456783448 tonghua shanghai
分别存储手机号、姓名、地址我用这个语句 把手机号查出来
select id from mot where addr='shanghai'可我想要的结果是,把移动和联痛分开,如何做出呢???高手帮帮忙!
例如表A
a b
130 联通
137 移动
135 移动
....
select id,b from mot,a where addr= 'shanghai '
and substr(id,1,3)=a
移动:
select id from mot
where addr= 'shanghai'
and (
id like '135%'
or id like '136%'
or id like '137%'
or id like '138%'
or id like '139%'
or id like '159%'
);
联通:
select id from mot
where addr= 'shanghai'
and (
id like '130%'
or id like '131%'
or id like '132%'
or id like '133%'
or id like '153%'
);
建议增加一个字段,用来区分是联通还是移动的号码;譬如1表示联通,0表示移动。
一个bit位就够了。
如果特急的话可以这样(不知道效率如何):
select id,b from mot,a where addr= 'shanghai '
and substr(id,1,3) in ('130' ,'131' ,'132','133','153')
移动的如下:
select id,b from mot,a where addr= 'shanghai '
and substr(id,1,3) in ('139' ,'135' ,'136','137','138','159')
那用ORDER BY ID 可以吗?
见笑了。