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
=========================
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
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')
godeclare @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
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')
godeclare @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