【工作中的SQL小问】关于SQL查询的，诚心请教～

[xuxichun]

Table StuInfo
=========================
Name Age Gender
Lily 18 Female
David 19 Male
Asa 20 Male
Mike Male
========================
有个控件用来获得相应年龄的男学生的信息,控件上的查询条件是Age,要求模糊匹配

如果Age输入19 或者 19% 或者 %9
则输出
David 19 Male

如果age填%
则输出
David 19 Male
Asa 20 Male
Mike Male

怎么写这个SQL?

如果写成
select * from StuInfo
where age Like @age AND Gender='male'
这样的话如果age填％就只能得到下面两组数据，少了一行，请问这个SQL该如何改？
David 19 Male
Asa 20 Male

[-狙击手-]

------------------------------------
-- Author:  happyflystone  
-- Date:2008-12-15 22:47:00
------------------------------------

-- Test Data: STUINFO
IF OBJECT_ID('STUINFO') IS NOT NULL 
    DROP TABLE STUINFO
Go
CREATE TABLE STUINFO(Name NVARCHAR(5),Age INT,Gender NVARCHAR(6))
Go
INSERT INTO STUINFO
 SELECT 'Lily',18,'Female' UNION ALL
 SELECT 'David',19,'Male' UNION ALL
 SELECT 'Asa',20,'Male' UNION ALL
 SELECT 'Mike',null,'Male'
GO
--Start
SELECT 
	* 
FROM 
	STUINFO
where 
    isnull(age,'') like '%' and Gender = 'Male'

--Result:
/*
Name  Age         Gender
----- ----------- ------
David 19          Male
Asa   20          Male
Mike  NULL        Male

(3 行受影响)


*/
--End 

[dawugui]

--以下四个查询语句是一样的.

create Table StuInfo (Name varchar(10) ,Age varchar(10) ,  Gender varchar(10))
insert into StuInfo values('Lily'  , '18' ,   'Female') 
insert into StuInfo values('David' , '19' ,   'Male') 
insert into StuInfo values('Asa'   , '20' ,   'Male') 
insert into StuInfo values('Mike'  , ''   ,   'Male') 
go

declare @age as varchar(10)

set @age = '19'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male

（所影响的行数为 1 行）
*/

set @age = '19%'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male

（所影响的行数为 1 行）
*/

set @age = '9%'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male

（所影响的行数为 1 行）
*/

set @age = ''
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male
Asa        20         Male
Mike                  Male

（所影响的行数为 3 行）
*/
drop table stuinfo

[dawugui]

--以下五个查询语句是一样的.只是变量的值不同.

create Table StuInfo (Name varchar(10) ,Age varchar(10) ,  Gender varchar(10))
insert into StuInfo values('Lily'  , '18' ,   'Female') 
insert into StuInfo values('David' , '19' ,   'Male') 
insert into StuInfo values('Asa'   , '20' ,   'Male') 
insert into StuInfo values('Mike'  , ''   ,   'Male') 
go

declare @age as varchar(10)

set @age = '19'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male

（所影响的行数为 1 行）
*/

set @age = '19%'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male

（所影响的行数为 1 行）
*/

set @age = '9%'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male

（所影响的行数为 1 行）
*/

set @age = ''
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male
Asa        20         Male
Mike                  Male

（所影响的行数为 3 行）
*/

set @age = '%'
select * from stuinfo where Gender = 'Male' and age like case when @age = '%' then age else '%'+replace(@age , '%' , '')+'%' end
/*
Name       Age        Gender     
---------- ---------- ---------- 
David      19         Male
Asa        20         Male
Mike                  Male

（所影响的行数为 3 行）
*/

drop table stuinfo