创建一张 People表。字段有 People_Id、People_Name、
People_Age、People_Sex,插入4条数据。使用存储过程找出
People_Age为20以下的员工、21—45的员工、46以上的员工。这个存储过程怎么写;我有思路,但是没有写出来; 先从people表取出people_age字段,然后再去判断
People_Age、People_Sex,插入4条数据。使用存储过程找出
People_Age为20以下的员工、21—45的员工、46以上的员工。这个存储过程怎么写;我有思路,但是没有写出来; 先从people表取出people_age字段,然后再去判断
when people between 21 and 45 then '21-45'
when people >=46 then '>46'
end as '年龄区间',*
from people
'20以下的员工'
FROM people
WHERE people_age <= 20
UNION ALL
SELECT * ,
'46以上的员工'
FROM people
WHERE people_age >= 46
UNION ALL
SELECT * ,
'21—45的员工'
FROM people
WHERE people_age BETWEEN 21 AND 45
@AgeStart tinyint=Null
,@AgeEnd tinyint=Null
)
AsSelect
People_Id
,People_Name
,People_Age
,People_Sex
from People
Where (People_Age>=@AgeStart Or @AgeStart Is null)
Or (People_Age<=@AgeEnd Or @Agend Is null)Go
--執行:
Exec sp @AgeEnd=20 -- 20歲以下
Exec sp @AgeStart=21,@AgeEnd=45 -- 21歲以上,45歲以下
Exec sp @AgeStart=46 -- 46歲以上
@AgeStart tinyint=Null
,@AgeEnd tinyint=Null
)
As
Select
People_Id
,People_Name
,People_Age
,People_Sex
from People
Where (People_Age>=@AgeStart Or @AgeStart Is null)
And (People_Age<=@AgeEnd Or @Agend Is null)
Go
--執行:
Exec sp @AgeEnd=20 -- 20歲以下
Exec sp @AgeStart=21,@AgeEnd=45 -- 21歲以上,45歲以下
Exec sp @AgeStart=46 -- 46歲以上
Create procedure sp
AsSelect
*
,Case when People_Age <=20 Then N'20歲以下'
when People_Age >=46 Then N'46歲以上'
Else N'21歲以上,45歲以下'
From PeopleGoExec sp
create proc xxx
as
sql语句
就完事拉。
我的意思是:存储过程带参数的我执行的时候,只要输入数字就会出现相应的结果比如说 exec people 20 然后就会出现年龄为20岁员工的信息
Create procedure sp(
@Age tinyint
)
As
Select
People_Id
,People_Name
,People_Age
,People_Sex
from People
Where People_Age=@Age
Go
--執行:
Exec sp @Age=20 -- 20歲
@a INT
AS
IF @a <= 20
BEGIN
SELECT *
FROM people
WHERE people_age <= 20
END
ELSE
IF @a >= 46
BEGIN SELECT *
FROM people
WHERE people_age >= 46
END
ELSE
BEGIN
SELECT *
FROM people
WHERE people_age BETWEEN 21 AND 45
END
个人感觉只用IF会比 IF ELSE清晰呵呵。
CREATE PROC test
@a INT
AS
BEGINIF @a <= 20
BEGIN
SELECT *
FROM people
WHERE people_age <= 20
END IF @a >=21 AND @a <=45
BEGIN
SELECT *
FROM people
WHERE people_age BETWEEN 21 AND 45
END
IF @a>=46
BEGIN
SELECT *
FROM people
WHERE people_age>=46
END
END
@BeginAge int,
@EndAge int
as
begin
select * from People where People_Age between isnull(@BeginAge,0) and isnull(@EndAge,0)
end