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
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) )
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
不好意思,没有看清是两个表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
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
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
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