请问这SQL怎么写?

[jiaguwen123]

大家好,如果我想，当a.serial是以9开头的就查找，与B中id前9位相等的，其它就查与id前8位相等呢？
比如数据：
a: serial b:id
920081123 92008112356
20081125 20081125

[子陌红尘]

select 
    * 
from 
    a,b 
where 
    (case when left(a.serial,1) ='9' and left(a.serial,9)=left(b.serial,9) then 1
          when left(a.serial,1)!='9' and left(a.serial,8)=left(b.serial,8) then 1
          else 0
     end) = 1

[Limpire]

select * from a, b where ( a.serial like '9%' and left(a.serial,9)=left(b.id, 9) ) or ( a.serial not like '9%' and left(a.serial,8)=left(b.id,8) )

[13571863167]

CREATE TABLE tbl_Test1(serial varchar(10), id varchar(15))
INSERT INTO tbl_Test1
SELECT '920081123','92008112356' UNION ALL
SELECT '920081124','92008112456' UNION ALL
SELECT '920081125','92008111156' UNION ALL
SELECT '20081125','20081125'  UNION ALL
SELECT '20081126','20081126'  UNION ALL
SELECT '20081122','20081127'

SELECT * FROM tbl_Test1 WHERE (left(serial,1)='9' and left(serial,9) = left(id,9)) or (left(serial,1)<>'9' and left(serial,8) = left(id,8))

/*
serial     id              
---------- --------------- 
920081123  92008112356
920081124  92008112456
20081125   20081125
20081126   20081126
（所影响的行数为 4 行）
*/
DROP TABLE tbl_Test1

[13571863167]

不好意思，没有看清是两个表

CREATE TABLE a(serial varchar(10))
INSERT INTO a
SELECT '920081123' UNION ALL
SELECT '920081124' UNION ALL
SELECT '920081125' UNION ALL
SELECT '20081125' UNION ALL
SELECT '20081126' UNION ALL
SELECT '20081122'

CREATE TABLE b(id varchar(15))
INSERT INTO b
SELECT '92008112356' UNION ALL
SELECT '92008112456' UNION ALL
SELECT '92008111156' UNION ALL
SELECT '20081125'  UNION ALL
SELECT '20081126'  UNION ALL
SELECT '20081127'

SELECT a.serial,b.id FROM a,b WHERE (left(serial,1)='9' and left(serial,9) = left(id,9)) or (left(serial,1)<>'9' and left(serial,8) = left(id,8))

DROP TABLE a
DROP TABLE b

[jiaguwen123]

谢谢