有一表:id char(20) pw char(20) class_id char(20) name char(20)
----------------- -------------- -------------------- ----------------
G19980101 0 G199801 a
G19980102 0 G199801 b
G19980103 0 G199801 c
G19980104 0 G199801 d
G19980105 0 G199801 e
G19980106 0 G199801 f
G19980107 0 G199801 g
G19980108 0 G199801 h
G19980109 0 G199801 i
G19980110 0 G199801 j
G19980111 0 G199801 k
G19980112 0 G199801 l
G19980113 0 G199801 m
G19980114 0 G199801 n
G19980115 0 G199801 o
G19980116 0 G199801 p
G19980117 0 G199801 q
G19980118 0 G199801 r
G19980119 0 G199801 s
G19980201 0 G199802 t
G19980202 0 G199802 u
G19980203 0 G199802 v
G19980204 0 G199802 w
G19980205 0 G199802 x
G19980206 0 G199802 y
G19990101 0 G199901 z
我想根据class_id检索结果
SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id='something'
但我想something可以全部匹配,也可以只有前半部分匹配,例如:
用 class_id='G199801' 可以检索得a-s
用 class_id='G199802' 可以检索得t-y
用 class_id='G1998' 可以检索得a-y
用 class_id='G1999' 可以检索得z
我应该怎样做?
----------------- -------------- -------------------- ----------------
G19980101 0 G199801 a
G19980102 0 G199801 b
G19980103 0 G199801 c
G19980104 0 G199801 d
G19980105 0 G199801 e
G19980106 0 G199801 f
G19980107 0 G199801 g
G19980108 0 G199801 h
G19980109 0 G199801 i
G19980110 0 G199801 j
G19980111 0 G199801 k
G19980112 0 G199801 l
G19980113 0 G199801 m
G19980114 0 G199801 n
G19980115 0 G199801 o
G19980116 0 G199801 p
G19980117 0 G199801 q
G19980118 0 G199801 r
G19980119 0 G199801 s
G19980201 0 G199802 t
G19980202 0 G199802 u
G19980203 0 G199802 v
G19980204 0 G199802 w
G19980205 0 G199802 x
G19980206 0 G199802 y
G19990101 0 G199901 z
我想根据class_id检索结果
SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id='something'
但我想something可以全部匹配,也可以只有前半部分匹配,例如:
用 class_id='G199801' 可以检索得a-s
用 class_id='G199802' 可以检索得t-y
用 class_id='G1998' 可以检索得a-y
用 class_id='G1999' 可以检索得z
我应该怎样做?
declare @class_id nvarchar(50)
declare @sql nvarchar(1000)
set @class_id='G199801'
Set @sql=N'SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id Like '''+@class_id+'%'''
exec (@sql)
SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id like @class_id + '%'set @class_id = 'G199802' -- 可以检索得t-y
SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id like @class_id + '%'set @class_id = 'G1998' -- 可以检索得a-y
SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id like @class_id + '%'set @class_id = 'G1999' -- 可以检索得z
SELECT id AS 学号,name AS 姓名 FROM student WHERE class_id like @class_id + '%'