本文将介绍SQLServerSQL语句练习实例之四找出促销活动中销售额最高的职员的详细情况，特别是关于在sales表中查找销售金额最高的订单的相关信息。我们将通过案例分析、数据研究等多种方式，帮助您更
本文将介绍SQL Server SQL语句练习实例之四 找出促销活动中销售额最高的职员的详细情况，特别是关于在sales表中查找销售金额最高的订单的相关信息。我们将通过案例分析、数据研究等多种方式，帮助您更全面地了解这个主题，同时也将涉及一些关于-sql语句练习50题(Mysql学习练习版)、mysql/sqlserver数据库sql语句练习、SQL Server sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘、SQL Server SQL语句练习实例之一找出最近的两次晋升日期与工资额的知识。
本文目录一览：- SQL Server SQL语句练习实例之四 找出促销活动中销售额最高的职员（在sales表中查找销售金额最高的订单）
- -sql语句练习50题(Mysql学习练习版)
- mysql/sqlserver数据库sql语句练习
- SQL Server sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘
- SQL Server SQL语句练习实例之一找出最近的两次晋升日期与工资额
SQL Server SQL语句练习实例之四 找出促销活动中销售额最高的职员（在sales表中查找销售金额最高的订单）
感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！
代码如下:
---找出促销活动中销售额最高的职员
---你刚在一家服装销售公司中找到了一份工作，此时经理要求你根据数据库中的两张表得到促销活动销售额最高的销售员
---1.一张是促销活动表
---2.一张是销售客列表
create table Promotions
(
activity nvarchar(30),sdate datetime,edate datetime
)
insert Promotions
select '五一促销活动','2011-5-1','2011-5-7'
union
select '十一促销活动','2011-10-1','2011-10-7'
union
select 'OA专场活动','2011-6-1','2011-6-7'
go
create table sales
(
id int not null,name nvarchar(20),saledate datetime,price money
)
go
insert sales
select 1,'王五',1000 union
select 1,'2011-5-2',2000 union
select 1,'2011-5-3',3000 union
select 1,'2011-5-4',4000 union
select 1,'张三','李四','2011-5-6','赵六','2011-5-5','钱七','2011-5-8',1000 union
select 1,'孙五','2011-6-2','2011-6-3','2011-6-4',11000 union
select 1,20000 union
select 1,'2011-6-6','2011-6-5','2011-6-8',1500 union
select 1,'2011-10-2',12000 union
select 1,'2011-10-3',9000 union
select 1,'2011-10-4','2011-10-6',27000 union
select 1,'2011-10-5','2011-10-8',3000
go
-----我们需要找出在每次的促销活动中，其销售总额大于 等于
---所有其他职员销售额的职员及促销事件。
---说明：谓词a2.name<>a.name将其他职员从子查询合计中排除出去
---------谓词Between 中的子查询确保我们使用了正确的促销日期
--方法一：
select a.name,b.activity,SUM(a.price) as totalprice
from sales a,Promotions as b
where a.saledate between b.sdate and b.edate
group by a.name,b.activity
having SUM(price)>= all(select SUM(price) from sales a2
where a2.name<>a.name and a2.saledate between
(
select sdate from Promotions as b2 where b2.activity=b.activity
)
and (select edate from Promotions b3
where b3.activity=b.activity)
group by a2.name)
-----------------
---方法二：
---说明： 如果促销活动时间是不重叠的，则promotions表中只有一个主键列，这样在group by
--子句中使用(activity,sdate,edate)将不会改变。但是它将使having子句可以使用sdate和edate
select a.name,Promotions as b
where a.saledate between b.sdate and b.edate
group by b.activity,b.sdate,b.edate,a.name
having SUM(price)>= all(select SUM(price) from sales a2
where a2.name<>a.name and a2.saledate between
b.sdate
and b.edate
group by a2.name)
go
--方法三：
---使用cte(sql 2005以后的版本)
with clearksTotal(name,activity,totalprice) as
(
select a.name,SUM(price)
from sales a,Promotions b
where a.saledate between b.sdate and b.edate
group by a.name,b.activity
)
select c1.name,c1.activity,c1.totalprice
from clearksTotal c1
where totalprice=(select MAX(c2.totalprice) from clearksTotal c2
where c1.activity=c2.activity)
go
drop table Promotions
go
drop table sales
-sql语句练习50题(Mysql学习练习版)
–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
测试数据
--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '''',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '''',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '''',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '''',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '''',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values(''01'' , ''赵雷'' , ''1990-01-01'' , ''男'');
insert into Student values(''02'' , ''钱电'' , ''1990-12-21'' , ''男'');
insert into Student values(''03'' , ''孙风'' , ''1990-05-20'' , ''男'');
insert into Student values(''04'' , ''李云'' , ''1990-08-06'' , ''男'');
insert into Student values(''05'' , ''周梅'' , ''1991-12-01'' , ''女'');
insert into Student values(''06'' , ''吴兰'' , ''1992-03-01'' , ''女'');
insert into Student values(''07'' , ''郑竹'' , ''1989-07-01'' , ''女'');
insert into Student values(''08'' , ''王菊'' , ''1990-01-20'' , ''女'');
--课程表测试数据
insert into Course values(''01'' , ''语文'' , ''02'');
insert into Course values(''02'' , ''数学'' , ''01'');
insert into Course values(''03'' , ''英语'' , ''03'');
--教师表测试数据
insert into Teacher values(''01'' , ''张三'');
insert into Teacher values(''02'' , ''李四'');
insert into Teacher values(''03'' , ''王五'');
--成绩表测试数据
insert into Score values(''01'' , ''01'' , 80);
insert into Score values(''01'' , ''02'' , 90);
insert into Score values(''01'' , ''03'' , 99);
insert into Score values(''02'' , ''01'' , 70);
insert into Score values(''02'' , ''02'' , 60);
insert into Score values(''02'' , ''03'' , 80);
insert into Score values(''03'' , ''01'' , 80);
insert into Score values(''03'' , ''02'' , 80);
insert into Score values(''03'' , ''03'' , 80);
insert into Score values(''04'' , ''01'' , 50);
insert into Score values(''04'' , ''02'' , 30);
insert into Score values(''04'' , ''03'' , 20);
insert into Score values(''05'' , ''01'' , 76);
insert into Score values(''05'' , ''02'' , 87);
insert into Score values(''06'' , ''01'' , 31);
insert into Score values(''06'' , ''03'' , 34);
insert into Score values(''07'' , ''02'' , 89);
insert into Score values(''07'' , ''03'' , 98);
练习题和sql语句
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a
join score b on a.s_id=b.s_id and b.c_id=''01''
left join score c on a.s_id=c.s_id and c.c_id=''02'' or c.c_id = NULL where b.s_score>c.s_score
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a left join score b on a.s_id=b.s_id and b.c_id=''01'' or b.c_id=NULL
join score c on a.s_id=c.s_id and c.c_id=''02'' where b.s_score<c.s_score
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)>=60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
student b
left join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60
union
select a.s_id,a.s_name,0 as avg_score from
student a
where a.s_id not in (
select distinct s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from
student a
left join score b on a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;
-- 6、查询"李"姓老师的数量
select count(t_id) from teacher where t_name like ''李%'';
-- 7、查询学过"张三"老师授课的同学的信息
select a.* from
student a
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三''));
-- 8、查询没学过"张三"老师授课的同学的信息
select * from
student c
where c.s_id not in(
select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三'')));
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* from
student a,score b,score c
where a.s_id = b.s_id and a.s_id = c.s_id and b.c_id=''01'' and c.c_id=''02'';
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from
student a
where a.s_id in (select s_id from score where c_id=''01'' ) and a.s_id not in(select s_id from score where c_id=''02'')
-- 11、查询没有学全所有课程的同学的信息
select s.* from
student s where s.s_id in(
select s_id from score where s_id not in(
select a.s_id from score a
join score b on a.s_id = b.s_id and b.c_id=''02''
join score c on a.s_id = c.s_id and c.c_id=''03''
where a.c_id=''01''))
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in(
select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id=''01'')
);
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.* from student a where a.s_id in(
select distinct s_id from score where s_id!=''01'' and c_id in(select c_id from score where s_id=''01'')
group by s_id
having count(1)=(select count(1) from score where s_id=''01''));
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select a.s_name from student a where a.s_id not in (
select s_id from score where c_id =
(select c_id from course where t_id =(
select t_id from teacher where t_name = ''张三''))
group by s_id);
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from
student a
left join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.*,b.c_id,b.s_score from
student a,score b
where a.s_id = b.s_id and b.c_id=''01'' and b.s_score<60 ORDER BY b.s_score DESC;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,(select s_score from score where s_id=a.s_id and c_id=''01'') as 语文,
(select s_score from score where s_id=a.s_id and c_id=''02'') as 数学,
(select s_score from score where s_id=a.s_id and c_id=''03'') as 英语,
round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
select a.s_id,a.c_id,
@i:=@i +1 as i保留排名,
@k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''01'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''02'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=''03'' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
-- 20、查询学生的总成绩并进行排名
select a.s_id,
@i:=@i+1 as i,
@k:=(case when @score=a.sum_score then @k else @i end) as rank,
@score:=a.sum_score as score
from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
(select @k:=0,@i:=0,@score:=0)s
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
left join score b on a.c_id=b.c_id
left join teacher c on a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id=''01''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@j:=@j+1 as 排名 from score a,(select @j:=0)s where a.c_id=''02''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@k:=@k+1 as 排名 from score a,(select @k:=0)s where a.c_id=''03''
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select distinct f.c_name,a.c_id,b.`85-100`,b.百分比,c.`70-85`,c.百分比,d.`60-70`,d.百分比,e.`0-60`,e.百分比 from score a
left join (select c_id,SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)b on a.c_id=b.c_id
left join (select c_id,SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
ROUND(100*(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)c on a.c_id=c.c_id
left join (select c_id,SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70`,
ROUND(100*(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)d on a.c_id=d.c_id
left join (select c_id,SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60`,
ROUND(100*(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)e on a.c_id=e.c_id
left join course f on a.c_id = f.c_id
---也可以这么做
SELECT
c.c_id,
c.c_name,
a.`85-100`,
a.`85-100百分比`,
b.`70-85`,
b.`70-85百分比`,
ce.`60-70`,
ce.`60-70百分比`,
d.`0-60`,
d.`0-60百分比`
FROM course c
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "85-100",ROUND(100*SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END)/count(1),2) as "85-100百分比" FROM score s GROUP BY s.c_id) a ON a.c_id = c.c_id
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "70-85",ROUND(100*SUM(CASE WHEN s.s_score >70 AND s.s_score <= 85 THEN 1 ELSE 0 END)/count(1),2) as "70-85百分比" FROM score s GROUP BY s.c_id) b ON b.c_id = c.c_id
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "60-70",ROUND(100*SUM(CASE WHEN s.s_score >60 AND s.s_score <= 70 THEN 1 ELSE 0 END)/count(1),2) as "60-70百分比" FROM score s GROUP BY s.c_id) ce ON ce.c_id = c.c_id
LEFT JOIN(SELECT s.c_id,SUM(CASE WHEN s.s_score >85 AND s.s_score <= 100 THEN 1 ELSE 0 END) AS "0-60",ROUND(100*SUM(CASE WHEN s.s_score >0 AND s.s_score <= 60 THEN 1 ELSE 0 END)/count(1),2) as "0-60百分比" FROM score s GROUP BY s.c_id) d ON d.c_id = c.c_id
-- 24、查询学生平均成绩及其名次
select a.s_id,
@i:=@i+1 as ''不保留空缺排名'',
@k:=(case when @avg_score=a.avg_s then @k else @i end) as ''保留空缺排名'',
@avg_score:=avg_s as ''平均分''
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id)a,(select @avg_score:=0,@i:=0,@k:=0)b;
-- 25、查询各科成绩前三名的记录
-- 1.选出b表比a表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的
select a.s_id,a.c_id,a.s_score from score a
left join score b on a.c_id = b.c_id and a.s_score<b.s_score
group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
ORDER BY a.c_id,a.s_score DESC
-- 26、查询每门课程被选修的学生数
select c_id,count(s_id) from score a GROUP BY c_id
-- 27、查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name from student where s_id in(
select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);
---另外一种写法
SELECT
st.s_id,
st.s_name
FROM student st
INNER JOIN(
SELECT
s.s_id
FROM score s
GROUP BY s_id
HAVING COUNT(s.c_id) = 2
) a ON a.s_id = st.s_id;
-- 28、查询男生、女生人数
select s_sex,COUNT(s_sex) as 人数 from student GROUP BY s_sex
-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like ''%风%'';
-- 30、查询同名同性学生名单，并统计同名人数
select a.s_name,a.s_sex,count(*) from student a JOIN
student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex
-- 31、查询1990年出生的学生名单
select s_name from student where s_birth like ''1990%''
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_id=b.s_id where b.c_id=(
select c_id from course where c_name =''数学'') and b.s_score<60
----另外一种自己的写法
SELECT
st.s_name,
s.s_score
FROM student st
INNER JOIN score s ON s.s_id = st.s_id
INNER JOIN course c ON c.c_id = s.c_id AND c.c_name = ''数学''
WHERE s.s_score < 60;
-- 35、查询所有学生的课程及分数情况；
select a.s_id,a.s_name,
SUM(case c.c_name when ''语文'' then b.s_score else 0 end) as ''语文'',
SUM(case c.c_name when ''数学'' then b.s_score else 0 end) as ''数学'',
SUM(case c.c_name when ''英语'' then b.s_score else 0 end) as ''英语'',
SUM(b.s_score) as ''总分''
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name
--注意case when...和case...when的用法
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
left join student a on a.s_id=c.s_id where c.s_score>=70
----另外一种过滤方法
SELECT
st.s_name,
c.c_name,
s.s_score
FROM course c
LEFT JOIN score s on s.c_id = c.c_id AND s.s_score > 70
LEFT JOIN student st ON st.s_id = s.s_id
-- 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
where a.s_score<60
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
where a.c_id = ''01'' and a.s_score>80
-- 39、求每门课程的学生人数
select count(*) from score GROUP BY c_id;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 查询老师id
select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=''张三''
-- 查询最高分（可能有相同分数）
select MAX(s_score) from score where c_id=''02''
-- 查询信息
select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=''张三'')
and b.s_score in (select MAX(s_score) from score where c_id=''02'')
--可以将上面组织一下，一次查询出来如下：
SELECT
st.*
FROM student st
left JOIN score s on s.s_id = st.s_id
left JOIN course c on c.c_id = s.c_id
WHERE s.c_id = (SELECT c_id FROM course c,teacher t WHERE c.t_id = t.t_id AND t.t_name LIKE ''张三'')
AND s.s_score in (SELECT max(s_score) FROM score WHERE c_id = (SELECT c_id FROM course c,teacher t WHERE c.t_id = t.t_id AND t.t_name LIKE ''张三''))
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
-- 42、查询每门功成绩最好的前两名
-- 牛逼的写法
select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC
-- 44、检索至少选修两门课程的学生学号
select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2
-- 45、查询选修了全部课程的学生信息
select * from student where s_id in(
select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))
--46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_birth,(DATE_FORMAT(NOW(),''%Y'')-DATE_FORMAT(s_birth,''%Y'') -
(case when DATE_FORMAT(NOW(),''%m%d'')>DATE_FORMAT(s_birth,''%m%d'') then 0 else 1 end)) as age
from student;
-- 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))=WEEK(s_birth)
select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),''%Y%m%d''))
select WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))
-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),''%Y%m%d''))+1 =WEEK(s_birth)
-- 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),''%Y%m%d'')) =MONTH(s_birth)
-- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),''%Y%m%d''))+1 =MONTH(s_birth)
mysql/sqlserver数据库sql语句练习
1、在test数据库上建表
create table Student
(
StudentName varchar(200) not null,
BatchID int comment ''批次编号'',
RegNo varchar(100) comment ''注册号码'',
StudentAddress varchar(200) comment ''注册地址'',
Phone varchar(15) comment ''电话'',
DateOfBirth datetime comment ''生日'',
AdmissionDate datetime comment ''注册日期''
);
create table DemoTable(Name VarChar(20),Age VarChar(3));
create table ThisTable(Firstname Text,Lastname Text);
mysql：
mysql> create Table get0(Word nvarchar(31) NOT NULL,Comment nvarchar(50) NOT NULL);
mysql报错的写法：
mysql> create Table get0(Word nvarchar(31) COLLATE Chinese_PRC_CI_AS NOT NULL,Comment nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL);
ERROR 1273 (HY000): Unknown collation: ''Chinese_PRC_CI_AS''
mysql> create Table get(Word nvarchar(31) NOT NULL,Comment nvarchar(50) NOT NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''get(Word nvarchar(31) NOT NULL,Comment nvarchar(50) NOT NULL)'' at line 1
mysql> CREATE TABLE OFFICES(OfficeID nvarchar(4),OfficeName nvarchar(10));
mysql> create Table Singer(SingerID int AUTO_INCREMENT not NULL,SingerName nvarchar(31) NOT NULL,Birthday datetime not null,PRIMARY KEY (SingerID ASC));
IDENTITY、COLLATE Chinese_PRC_CI_AS是sql server的写法：
create Table Singer(SingerID int IDENTITY(1,1) not NULL,SingerName nvarchar(31) COLLATE Chinese_PRC_CI_AS NOT NULL,Birthday datetime not null);
create Table userphoto(ID int IDENTITY(1,1) not NULL,UserName nvarchar(31) COLLATE Chinese_PRC_CI_AS NOT NULL,Old int not null,photo image);
create Table get(Word nvarchar(31) COLLATE Chinese_PRC_CI_AS NOT NULL,Comment nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL);
增加字段
ALTER TABLE Student ADD IsGuest tinyint not null default 0;
ALTER TABLE Student ADD FaceUrl nvarchar(255);
ALTER TABLE Student modify column FaceUrl nvarchar(255) comment ''头像url'';
show full columns from Student;
2、在test数据库的表中加入记录、更新记录
INSERT INTO OFFICES(OfficeID,OfficeName)VALUES ("a1", "bbb");
INSERT INTO OFFICES(OfficeID,OfficeName)VALUES ("A1", "Bbb");
UPDATE OFFICES SET OfficeID = UCASE(OfficeID);
UPDATE OFFICES SET OfficeID = LCASE(OfficeID);
DELETE FROM OFFICES;
Insert into DemoTable(Name,Age)Values(''韩Ivan'',27);
Insert into get0(Word,Comment)Values(''poet'',''诗人'');
mysql> Insert into Singer(SingerID,SingerName,Birthday)Values(1,''张柏芝'',''1980-5-24 0:00:00'');
mysql> Insert into Singer(SingerName,Birthday)Values(''Ivan'',''1960-5-24 0:00:00'');
Query OK, 1 row affected (0.00 sec)
mysql> select * from Singer;
+----------+------------+---------------------+
| SingerID | SingerName | Birthday |
+----------+------------+---------------------+
| 1 | 张柏芝 | 1980-05-24 00:00:00 |
| 2 | Ivan | 1960-05-24 00:00:00 |
+----------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> update Singer set SingerName=''刘德华'',Birthday=''1961-9-27 0:00:00'' where SingerID=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Singer;
+----------+------------+---------------------+
| SingerID | SingerName | Birthday |
+----------+------------+---------------------+
| 1 | 张柏芝 | 1980-05-24 00:00:00 |
| 2 | 刘德华 | 1961-09-27 00:00:00 |
+----------+------------+---------------------+
2 rows in set (0.00 sec)
3、跨表查询
sql中inner join、left join 、right join之间的区别
left join 是left outer join简写
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
例子：
A表(a1,b1,c1) B表(a2,b2)
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 04 王五
select A.*,B.* from A inner join B on(A.a1=B.a2);
结果是：
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
SELECT * FROM A INNER JOIN B ON A.a1=B.a2;
SELECT A.a1, B.b2, A.b1,A.c1 FROM A INNER JOIN B ON A.a1=B.a2;
SELECT * FROM A,B;/*求笛卡儿积，A有3条记录，B有3条记录，则笛卡儿积有9条记录*/
select A.*,B.* from A left join B on(A.a1=B.a2);
select A.*,B.* from A left outer join B on(A.a1=B.a2);
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 NULL NULL
select A.*,B.* from A right outer join B on(A.a1=B.a2);
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
NULL NULL NULL 04 王五
mysql报错：
select A.*,B.* from A full outer join B on(A.a1=B.a2);
变通处理的:
select * from A left join B on A.a1=B.a2 union select * from A right join B on A.a1=B.a2;
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 NULL NULL
NULL NULL NULL 04 王五
将下面的sql脚本复制到Xshell终端里回车执行：
USE `test`;
-- ----------------------------
-- Table structure for A
-- ----------------------------
DROP TABLE IF EXISTS `A`;
CREATE TABLE `A` (
`a1` varchar(10) NOT NULL,
`b1` varchar(10) NOT NULL,
`c1` smallint(5) NOT NULL
);
-- ----------------------------
-- Table structure for B
-- ----------------------------
DROP TABLE IF EXISTS `B`;
CREATE TABLE `B` (
`a2` varchar(10) NOT NULL,
`b2` varchar(10) NOT NULL
);
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `A` VALUES (''01'', ''数学'', ''95'');
INSERT INTO `A` VALUES (''02'', ''语文'', ''90'');
INSERT INTO `A` VALUES (''03'', ''英语'', ''80'');
INSERT INTO `B` VALUES (''01'', ''张三'');
INSERT INTO `B` VALUES (''02'', ''李四'');
INSERT INTO `B` VALUES (''04'', ''王五'');
select A.*,B.* from A inner join B on(A.a1=B.a2);
2007年笔记：
SELECT语句的完整格式，它包括六个子句，其中 SELECT 和 FROM 子句是必须的，其它子句可以任选，每个子句的功能如下 :
1、Select 子句列出所有要求 SELECT 语句检索的数据项。它放在 SELECT 语句开始处，指定此查询要检索的数据项。这些数据项通常用选择表表示，即一组用“，”隔开的选择项。按照从左到右的顺序，每个选择项产生的一个列的查询结果，一个选择项可能是以下项目：
(1)、列名：标识 FROM 子句指定表中的列。如果列名作为选择项，则 SQL 直接从数据库表中每行取出该列的值，再将其放在查询结果的相应行中。
(2)、常数：指定在查询结果的每行中都放上该值。
(3)、SQL 表达式：说明必须将要放入查询结果中的值按表达式的规定进行计算。
2、From 子句列出包含所要查询数据的表，它由关键字 FROM 后跟一组用逗号分开的表名组成。每个表明都代表一个包括该查询要检索数据的表。这些表称为此 SQL 语句的表源，因为查询结果都源于它们。
3、Where 子句告诉 SQL 只查询某些行中的数据，这些行用搜索条件描述。
4、Group By 子句指定汇总查询，即不是对每行产生一个查询结果，而是将相似的行进行分组，再对每组产生一个汇总结果。
5、Having 子句告诉 SQL 只产生有 Group By 得到的某些组的结果，和 Where 子句一样，所需要的组也用一个搜索条件指定。
6、Order By 子句将查询结果按一列或多列中的数据排序。如果省略此子句，则查询结果将是无序的。
CREATE DATABASE tianjiao DEFAULT CHARACTER SET=utf8;
USE tianjiao;
CREATE TABLE sales
(
NAME VARCHAR(15) NOT NULL COMMENT ''姓名'',
sex TINYINT COMMENT ''性别:0男,1女'',
salary INT COMMENT ''工资'',
target INT COMMENT ''销售目标'',
sales INT COMMENT ''销售额'',
district VARCHAR(15) COMMENT ''地区''
);
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''书生'',0,2500,8000,9000,''上海'');
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''吴冠军'',0,3000,10000,9999,''北京'');
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''雷鸣'',0,2000,8000,10000,''四川'');
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''雪儿'',1,2500,5000,6000,''广州'');
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''顾一'',0,2600,9000,9800,''大连'');
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''阿卓'',1,2000,4000,4000,''天津'');
INSERT INTO sales(NAME,sex,salary,target,sales,district)VALUES(''熠天'',0,4000,20000,20000,''全国'');
SELECT NAME,target,sales FROM sales;
SELECT NAME,target,sales FROM sales WHERE sex=0;
SELECT NAME,target,sales FROM sales WHERE sales>target AND sex=0 ORDER BY target;
SQL Server sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘
感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！
代码如下:
DECLARE @PAGESIZE INT
DECLARE @PAGEINDEX INT
DECLARE @PAGECOUNT INT
DECLARE @RECORDCOUNT INT
SELECT @PAGESIZE=5
SELECT @PAGEINDEX=1
DECLARE @FIELDNAME VARCHAR(50)
DECLARE @FIELDVALUE VARCHAR(50)
DECLARE @OPERATION VARCHAR(50)
--组合条件
DECLARE @WHERE NVARCHAR(1000)
SELECT @WHERE=' WHERE NOTdisPLAY=0 '
DECLARE ABC CURSOR FOR
SELECT FIELDNAME,FIELDVALUE,OPERATION FROM TBParaMETERS
OPEN ABC
FETCH NEXT FROM ABC INTO @FIELDNAME,@FIELDVALUE,@OPERATION
WHILE @@FETCH_STATUS=0
BEGIN
IF(@OPERATION = 'Like')
SELECT @WHERE=@WHERE + ' AND ' + @FIELDNAME + ' Like ''%'+@FIELDVALUE+'%'''
ELSE
BEGIN
IF(@FIELDNAME='CLASSID')
BEGIN
DECLARE @ROOTID INT
SELECT @ROOTID=@FIELDVALUE
--将指定类别的值的子类加入临时表
INSERT INTO TBTEMCLASS(ID) SELECT ID FROM TBSDINFOCLASS WHERE ROOTID=@ROOTID
--使用游标来将指定类别的最小类别提出放入临时表
DECLARE CLASSID CURSOR FOR
SELECT ID FROM TBTEMCLASS
OPEN CLASSID
FETCH NEXT FROM CLASSID INTO @ROOTID
WHILE @@FETCH_STATUS=0
BEGIN
--如果判断有子类则将子类加入临时表，并删除该类别，以使游标在临时表中循环
IF(EXISTS(SELECT ID FROM TBSDINFOCLASS WHERE ROOTID=@ROOTID))
BEGIN
INSERT INTO TBTEMCLASS(ID) SELECT ID FROM TBSDINFOCLASS WHERE ROOTID=@ROOTID
DELETE FROM TBTEMCLASS WHERE ID=@ROOTID
END
FETCH NEXT FROM CLASSID INTO @ROOTID
END
CLOSE CLASSID
DEALLOCATE CLASSID
--将自身加入临时表
INSERT INTO TBTEMCLASS(ID) SELECT @FIELDVALUE
SELECT @WHERE=@WHERE +' AND CLASSID IN(SELECT ID FROM TBTEMCLASS)'
END
ELSE
SELECT @WHERE=@WHERE + ' AND ' + @FIELDNAME + @OPERATION+@FIELDVALUE
END
FETCH NEXT FROM ABC INTO @FIELDNAME,@OPERATION
END
CLOSE ABC
DEALLOCATE ABC
TruncATE TABLE TBParaMETERS
-- --计数语句
DECLARE @COUNTsql NVARCHAR(500)
SELECT @COUNTsql=N'SELECT @RECORDCOUNT=COUNT(*) FROM TBSDINFO INNER JOIN TBUSER ON TBSDINFO.USERNAME=TBUSER.USERNAME '
SELECT @COUNTsql=@COUNTsql+@WHERE
--
-- --执行统计
EXEC sp_executesql @COUNTsql, N'@RECORDCOUNT INT OUT', @RECORDCOUNT OUT
--
-- --计算页数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
--
-- --查询语句
DECLARE @sql NVARCHAR(2000)
DECLARE @ORDERBY VARCHAR(100)
SELECT @ORDERBY=' ORDER BY TBSDINFO.IsCommon DESC,TBSDINFO.CommonTime DESC,TBSDINFO.CreateTime DESC'
IF(@PAGEINDEX=1)
BEGIN
SELECT @sql='INSERT INTO TBTEMINFO(ID,TITLE,REMARK,CREATETIME,ENDTIME,WEBDOMAIN,CLASSID,CLASSNAME,TYPEID,TYPENAME,PROVINCEID,PROVINCE,CITYID,CITY,COMPANYNAME,ADDRESS,USERTYPE) '
SELECT @sql=@sql+'SELECT TOP '+CONVERT(VARCHAR(4),@PAGESIZE)+' TBSDINFO.ID,Remark,TBSDINFO.Createtime,EndTime,WebDomain,TBSDINFO.CLASSID,(SELECT CLASSNAME FROM TBSDINFOCLASS WHERE TBSDINFOCLASS.ID=TBSDINFO.CLASSID) AS CLASSNAME,(SELECT TYPENAME FROM TBSDINFOTYPE WHERE TBSDINFO.TYPEID=TBSDINFOTYPE.ID) AS TYPENAME,ProvinceID,(SELECT PROVINCE FROM TBPROVINCE WHERE TBPROVINCE.ID=PROVINCEID) AS PROVINCE,CityID,(SELECT CITY FROM TBCITY WHERE TBCITY.ID=CITYID) AS CITY,CompanyName,TBSDINFO.Address,UserType FROM TBSDINFO INNER JOIN TBUSER ON TBUSER.USERNAME=TBSDINFO.USERNAME'
SELECT @sql=@sql+@WHERE
SELECT @sql=@sql+@ORDERBY
END
ELSE
BEGIN
DECLARE @MINRECORD INT
SELECT @MINRECORD=(@PAGEINDEX-1)*@PAGESIZE
SELECT @sql='INSERT INTO TBTEMINFO(ID,UserType FROM TBSDINFO INNER JOIN TBUSER ON TBUSER.USERNAME=TBSDINFO.USERNAME'
IF(@WHERE<>'')
SELECT @sql=@sql+@WHERE+' AND '
ELSE
SELECT @sql=@sql+' WHERE '
SELECT @sql=@sql+' TBSDINFO.ID NOT IN(SELECT TOP '+CONVERT(VARCHAR(4),@MINRECORD)+' TBSDINFO.ID FROM TBSDINFO INNER JOIN TBUSER ON TBUSER.USERNAME=TBSDINFO.USERNAME '+@WHERE+@ORDERBY+')'
SELECT @sql=@sql+@ORDERBY
END
--PRINT @sql
--执行查询
--查询的结果是将找到的记录放入临时表，再通过以下游标查询出相应的父类与根类记录
EXEC (@sql)
DECLARE @CLASSID INT
DECLARE @ID INT
DECLARE TEM CURSOR FOR
SELECT ID,CLASSID FROM TBTEMINFO
OPEN TEM
FETCH NEXT FROM TEM INTO @ID,@CLASSID
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @NS VARCHAR(500)
DECLARE @DS VARCHAR(200)
SELECT @NS=''
SELECT @DS=''
DECLARE @TEMROOTID INT
DECLARE @TEMTS VARCHAR(50)
SELECT @CLASSID=ID,@TEMTS=CLASSNAME,@TEMROOTID=ROOTID FROM TBSDINFOCLASS WHERE ID=@CLASSID
SELECT @NS=@TEMTS+'#'+@NS
SELECT @DS=CONVERT(VARCHAR(10),@CLASSID)+'#'+@DS
WHILE(@TEMROOTID>0)
BEGIN
SELECT @TEMROOTID=ROOTID,@CLASSID=ID,@TEMTS=CLASSNAME FROM TBSDINFOCLASS WHERE ID=@TEMROOTID
SELECT @NS=@TEMTS+'#'+@NS
SELECT @DS=CONVERT(VARCHAR(10),@CLASSID)+'#'+@DS
END
UPDATE TBTEMINFO SET NS=@NS,DS=@DS WHERE ID=@ID
FETCH NEXT FROM TEM INTO @ID,@CLASSID
END
CLOSE TEM
DEALLOCATE TEM
SELECT * FROM TBTEMINFO
TruncATE TABLE TBTEMINFO
TruncATE TABLE TBTEMCLASS
SQL Server SQL语句练习实例之一找出最近的两次晋升日期与工资额
感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！
代码如下:
--程序员们在编写一个雇员报表，他们需要得到每个雇员当前及历史工资状态的信息，
--以便生成报表。报表需要显示每个人的晋升日期和工资数目。
--如果将每条工资信息都放在结果集的一行中，并让宿主程序去格式化它。
--应用程序的程序员都是一帮懒人，他们需要在每个雇员的一行上得到当前
--和历史工资信息。这样就可以写一个非常简单的循环语句。
---示例：
create table salaries
( name nvarchar(50) not null,sal_date date not null,salary money not null,)
go
ALTER TABLE [dbo].salaries ADD CONSTRAINT [PK_salaries] PRIMARY KEY CLUSTERED
(
name,sal_date asc
)WITH (PAD_INDEX = OFF,STATISTICS_norECOmpuTE = OFF,SORT_IN_TEMPDB = OFF,IGnorE_DUP_KEY = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
----插入数据
insert into salaries
select 'TOM','2010-1-20',2000
union
select 'TOM','2010-6-20',2300
union
select 'TOM','2010-12-20',3000
union
select 'TOM','2011-6-20',4000
union
select 'Dick',2000
union
select 'Harry',2000
go
----方法一、使用left join 连接进行查询（sql 2000及以上版本）
select b.name,b.maxdate,y.salary,b.maxdate2,z.salary
from(select a.name,a.maxdate,MAX(x.sal_date) as maxdate2
from(select w.name,MAX(w.sal_date) as maxdate
from salaries as w
group by w.name) as a
left outer join salaries as x on a.name=x.name and a.maxdate>x.sal_date
group by a.name,a.maxdate) as b
left outer join salaries as y
on b.name=y.name and b.maxdate=y.sal_date
left outer join salaries as z
on b.name=z.name and b.maxdate2=z.sal_date
go
----方法二、这个方法是对每个雇员中的行进行编号，然后取出两个雇用日期最近的日期，
---（sql 2005以上版本）
select s1.name,MAX(case when rn=1 then sal_date else null end) as curr_date,MAX(case when rn=1 then salary else null end) as curr_salary,MAX(case when rn=2 then sal_date else null end) as prev_date,MAX(case when rn=2 then salary else null end) as curr_salary
from (select name,sal_date,salary,RANK() over(partition by name order by sal_date desc) rn
from salaries
) s1 where rn<3 group by s1.name
go
---方法三、在sql server 2005之后版本可以使用这种方法 ，使用CTE的方式来实现
with cte(name,sal_amt,rn)
as
(
select name,ROW_NUMBER() over(PARTITION by name order by sal_date desc) as rn from salaries
)
select o.name,o.sal_date AS curr_date,o.sal_amt as curr_amt,i.sal_date as prev_date,i.sal_amt as prev_amt from cte as o
left outer join cte as i on o.name=i.name and i.rn=2 where o.rn=1
go
----方法四、使用视图，将问题分为两种情况
---1.只有一次工资变动的雇员
---2.有两次或多次工资变动的雇员
create view v_salaries
as
select a.name,a.sal_date,MAX(a.salary) as salary from salaries as a,salaries as b
where a.sal_date<=b.sal_date and a.name=b.name group by a.name,a.sal_date
having COUNT(*)<=2
go
select a.name,a.salary,b.sal_date,b.salary from v_salaries a,v_salaries b
where a.name=b.name and a.sal_date>b.sal_date
union all
select name,max(sal_date),max(salary),cast(null as date),cast(null as decimal(8,2))
from v_salaries
group by name
having count(*)=1
go
drop table salaries
go
drop view v_salaries
今天关于SQL Server SQL语句练习实例之四 找出促销活动中销售额最高的职员和在sales表中查找销售金额最高的订单的分享就到这里，希望大家有所收获，若想了解更多关于-sql语句练习50题(Mysql学习练习版)、mysql/sqlserver数据库sql语句练习、SQL Server sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘、SQL Server SQL语句练习实例之一找出最近的两次晋升日期与工资额等相关知识，可以在本站进行查询。
本文标签：
