解惑一:
1、创建表和插入数据
create
table
Salaries
(
emp_name
char
(
10
)
not
null,
sal_date
date
not
null,
sal_amt
decimal
(
8
,
2
)
not
null,
primary
key
(
emp_name
,
sal_date
)
)
insert
into
Salaries
values
(
'Tom'
,
'1996-06-20'
,
500.00
),
(
'Tom'
,
'1996-08-20'
,
700.00
),
(
'Tom'
,
'1996-10-20'
,
800.00
),
(
'Tom'
,
'1996-12-20'
,
900.00
),
(
'Dick'
,
'1996-06-20'
,
500.00
),
(
'Harry'
,
'1996-07-20'
,
500.00
),
(
'Harry'
,
'1996-09-20'
,
700.00
)
go
2、创建视图
create
view
Salaries2
(
emp_name
,
sal_date
,
sal_amt
)
as
select
s0
.
emp_name
,
s0
.
sal_date
,
MAX
(
s0
.
sal_amt
)
from
Salaries
as
s0
,
Salaries
as
s1
where
s0
.
sal_date
<=
s1
.
sal_date
and
s0
.
emp_name
=
s1
.
emp_name
group
by
s0
.
emp_name
,
s0
.
sal_date
having
COUNT
(*)
<=
2
go
解惑二:
select
s0
.
emp_name
,
s0
.
sal_date
,
s0
.
sal_amt
,
s1
.
sal_date
,
s1
.
sal_amt
from
Salaries
as
s0
,
Salaries
as
s1
where
s0
.
emp_name
=
s1
.
emp_name
and
s0
.
sal_date
=
(
select
MAX
(
s2
.
sal_date
)
from
Salaries
as
s2
where
s0
.
emp_name
=
s2
.
emp_name
)
and
s1
.
sal_date
=
(
select
MAX
(
s3
.
sal_date
)
from
Salaries
as
s3
where
s0
.
emp_name
=
s3
.
emp_name
and
s3
.
sal_date
<
s0
.
sal_date
)
union
all
select
s4
.
emp_name
,
max
(
s4
.
sal_date
),
MAX
(
s4
.
sal_amt
),null,null
from
Salaries
as
s4
group
by
s4
.
emp_name
having
COUNT
(*)
=
1
解惑三:
1、语句块一(获取表的每个人的最大日期):
(1)from子句获取Salaries表数据;
(2)groupby子句将表数据按emp_name分组
(3)select子句选择emp_name和最大的sal_date
select
w
.
emp_name
,
MAX
(
w
.
sal_date
)
as
maxdate
from
Salaries
as
w
group
by
w
.
emp_name
2、语句块二(获取表的每个人的最大日期和第二大日期):
(1)from子句获取上面语句块一的数据;
(2)left outer join子句左外连接表Salaries,当表a的emp_name与表x的emp_name且表a的日期(即最大日期)小于表x的日期时就添加到表a中;
(3)groupby子句将数据按emp_name和maxdate进行分组;
(4)select语句选择表a的emp_name,表a的maxdate(即最大的日期),表x的最大日期(即除了最大日期之外的第二大日期)
select
a
.
emp_name
,
a
.
maxdate
,
max
(
x
.
sal_date
)
as
maxdate2
from
a
left
outer
join
Salaries
as
x
on
a
.
emp_name
=
x
.
emp_name
and
a
.
maxdate
>
x
.
sal_date
group
by
a
.
emp_name
,
a
.
maxdate
3、语句块三:
(1)from子句获取上面语句块二的数据;
(2)left outer join子句左外连接表Salaries,当表y与表z的emp_name和sal_date相等时则左外连接。
(3)select子句获取名字、最近日期、最近日期的工资、最近第二次的日期、最近第二次日期的工资
select
b
.
emp_name
,
b
.
maxdate
,
y
.
sal_amt
,
b
.
maxdate2
,
z
.
sal_amt
from
b
left
outer
join
Salaries
as
y
on
b
.
emp_name
=
y
.
emp_name
and
b
.
maxdate
=
y
.
sal_date
left
outer
join
Salaries
as
z
on
b
.
emp_name
=
z
.
emp_name
and
b
.
maxdate2
=
z
.
sal_date
4、完整语句
select
b
.
emp_name
,
b
.
maxdate
,
y
.
sal_amt
,
b
.
maxdate2
,
z
.
sal_amt
from
(
select
a
.
emp_name
,
a
.
maxdate
,
max
(
x
.
sal_date
)
as
maxdate2
from
(
select
w
.
emp_name
,
MAX
(
w
.
sal_date
)
as
maxdate
from
Salaries
as
w
group
by
w
.
emp_name
)
as
a
left
outer
join
Salaries
as
x
on
a
.
emp_name
=
x
.
emp_name
and
a
.
maxdate
>
x
.
sal_date
group
by
a
.
emp_name
,
a
.
maxdate
)
as
b
left
outer
join
Salaries
as
y
on
b
.
emp_name
=
y
.
emp_name
and
b
.
maxdate
=
y
.
sal_date
left
outer
join
Salaries
as
z
on
b
.
emp_name
=
z
.
emp_name
and
b
.
maxdate2
=
z
.
sal_date
解惑四:
1、使用解惑一的视图,通过日期的比较,只有前一个日期小于或等于后一个日期中的统计次数为小于等于2的即为前两个数。
例如:
日期为2017.1.1,2017.1.2,2017.1.3,2017.1.4。
那么按照公式的要求,自联结后日期进行比较并分组,那么存在如下分组。
通过上面的比对可了解到,只要分组成员少于或等于2的,那么对应的就是最近的两天
create
view
Salaries2
(
emp_name
,
sal_date
,
sal_amt
)
as
select
s0
.
emp_name
,
s0
.
sal_date
,
MAX
(
s0
.
sal_amt
)
from
Salaries
as
s0
,
Salaries
as
s1
where
s0
.
sal_date
<=
s1
.
sal_date
and
s0
.
emp_name
=
s1
.
emp_name
group
by
s0
.
emp_name
,
s0
.
sal_date
having
COUNT
(*)
<=
2
2、选择数据
(1)选择其中的最大的数据。
select
s1
.
emp_name
,
s1
.
sal_date
,
s1
.
sal_amt
,
s2
.
sal_date
,
s2
.
sal_amt
from
Salaries2
as
s1
,
Salaries2
as
s2
where
s1
.
emp_name
=
s2
.
emp_name
and
s1
.
sal_date
>
s2
.
sal_date
(2)选择其中数据量为1次的数据,即只有一天的数据。
select
emp_name
,
MAX
(
sal_date
),
MAX
(
sal_amt
),null,null
from
Salaries2
group
by
emp_name
having
COUNT
(*)
=
1
(3)将两种数据合并
select
s1
.
emp_name
,
s1
.
sal_date
,
s1
.
sal_amt
,
s2
.
sal_date
,
s2
.
sal_amt
from
Salaries2
as
s1
,
Salaries2
as
s2
where
s1
.
emp_name
=
s2
.
emp_name
and
s1
.
sal_date
>
s2
.
sal_date
union
all
select
emp_name
,
MAX
(
sal_date
),
MAX
(
sal_amt
),null,null
from
Salaries2
group
by
emp_name
having
COUNT
(*)
=
1
解惑五:
1、语句块一:
(1)from子句获取表Salaries为表1的数据;
(2)innerjoin表Salaries为表2的数据,根据两表的emp_name和表2的sal_date进行自联接;
(3)当表2的sal_date数据为获取表Salaries为表4的数据,且表4的emp_name和表1的emp_name相等的时候,表4的sal_date都小于表1的每一条sal_date的时候,当上一步的数据为空值时,则直接获取表2的sal_date即可。
以下为具体例子:
(1)左边为表1和表2内连接的结果,右边为表4的数据;
(2)第一个,当s1_sal_date的数据1996-6-20与表4的所有数据进行比较,都是小于或大于表4的数据,因此没有满足的数据可以返回,因此取本身这条数据的s2_sal_date;
(3)第二个,当s1_sal_date的数据1996-10-20与表4的所有数据进行比较,存在数据1996-10-20大于1996-6-20和1996-8-20,因此再这两个数据中取最大值,选择对应的s2_sal_date即可。
总结:以下的语句是可以查找出,每一行日期与之邻近的上一个日期
select
(...)
from
Salaries
as
s1
inner
join
Salaries
as
s2
on
s2
.
emp_name
=
s1
.
emp_name
and
s2
.
sal_date
=
coalesce
((
select
MAX
(
s4
.
sal_date
)
from
Salaries
as
s4
where
s4
.
emp_name
=
s1
.
emp_name
and
s4
.
sal_date
<
s1
.
sal_date
),
s2
.
sal_date
)
2、语句块二:
(1)以语句块一的数据为基础;
(2)where子句中的子查询筛选数据:当表1的数据与表3的数据中的emp_name相等时时,如果表3的每一个sal_date日期都大于表1的sal_date日期时,则选择数据;
(3)当子查询没有任何数据时,才返回对应的sal_date。
当表1的1996-6-20与表3的所有sal_date比较时,存在1996-8-20、1996-10-20、1996-12-20大于表1的数据,因此在exists子查询中会返回数据;
当表1的1996-12-20与表3的所有sal_date比较时,不存在任何数据大于表1的数据,因此在exists子查询中不会返回任何数据;
同时where子句中的是not exists(true)时,则不返回数据;同时where子句中的是not exists(false)时,则返回数据。
select
s1
.
emp_name
as
s1_emp_name
,
s1
.
sal_date
as
s1_sal_date
,
s1
.
sal_amt
as
s1_sal_amt
,
s2
.
emp_name
as
s2_emp_name
,
s2
.
sal_date
as
s2_sal_date
,
s2
.
sal_amt
as
s2_sal_amt
from
Salaries
as
s1
inner
join
Salaries
as
s2
on
(...)
where
not
exists(
select
*
from
Salaries
as
s3
where
s3
.
emp_name
=
s1
.
emp_name
and
s3
.
sal_date
>
s1
.
sal_date
)
3、语句块三
(1)select子句选择表1的emp_name、sal_date、sal_amt
(2)如果表1的数据与表2的数据不相同时,则直接选择表2的数据;
如果表1的数据与表2的数据相同时,则证明该行数据就只有1个日期而已,则直接为空即可。
select
s1
.
emp_name
,
s1
.
sal_date
as
curr_date
,
s1
.
sal_amt
as
curr_amt
,
case
when
s2
.
sal_date
<>
s1
.
sal_date
then
s2
.
sal_date
end
as
prev_date
,
case
when
s2
.
sal_date
<>
s1
.
sal_date
then
s2
.
sal_amt
end
as
prev_amt
from
Salaries
as
s1
inner
join
Salaries
as
s2
on
s2
.
emp_name
=
s1
.
emp_name
and
s2
.
sal_date
=
coalesce
((
select
MAX
(
s4
.
sal_date
)
from
Salaries
as
s4
where
s4
.
emp_name
=
s1
.
emp_name
and
s4
.
sal_date
<
s1
.
sal_date
),
s2
.
sal_date
)
where
not
exists(
select
*
from
Salaries
as
s3
where
s3
.
emp_name
=
s1
.
emp_name
and
s3
.
sal_date
>
s1
.
sal_date
)
解惑六:
1、创建视图
(1)from子句获取表Salaries的数据
(2)leftouterjoin表Salaries,当表1的emp_name与表2的emp_name相同时,且表1的日期大于表2的日期。相当于获取所有日期与所有比该日期小的集合,如果没有则为空。
create
view
SalaryHistory
(
emp_name
,
curr_date
,
curr_amt
,
prev_date
,
prev_amt
)
as
select
s0
.
emp_name
,
s0
.
sal_date
as
curr_date
,
s0
.
sal_amt
as
curr_amt
,
s1
.
sal_date
as
prev_date
,
s1
.
sal_amt
as
prev_amt
from
Salaries
as
s0
left
outer
join
Salaries
as
s1
on
s0
.
emp_name
=
s1
.
emp_name
and
s0
.
sal_date
>
s1
.
sal_date
go
2、查询数据
(1)from子句获取数据
(2)where子句筛选表1的最大日期,表1代表所有日期的最大日期,表2代表所有日期的邻近日期的最大日期。筛选出来的日期即为表的最大日期和最大的邻近日期。
select
s0
.
emp_name
,
s0
.
curr_date
,
s0
.
curr_amt
,
s0
.
prev_date
,
s0
.
prev_amt
from
SalaryHistory
as
s0
where
s0
.
curr_date
=
(
select
MAX
(
curr_date
)
from
SalaryHistory
as
s1
where
s0
.
emp_name
=
s1
.
emp_name
)
and
s0
.
prev_date
=
(
select
MAX
(
prev_date
)
from
SalaryHistory
as
s2
where
s0
.
emp_name
=
s2
.
emp_name
or
s0
.
prev_date
is
null)
解惑七:
1、查询语句
(1)子查询中,是使用Rank函数将表Salaries按emp_name分组且按sal_date降序排列
(2)查询语句中只有leftouterjoin表插入第二名的日期且选择第一日期即可。
with
SalaryRanks
(
emp_name
,
sal_date
,
sal_amt
,
pos
)
as
(
select
emp_name
,
sal_date
,
sal_amt
,
RANK
()
over
(
PARTITION
BY
emp_name
order
by
sal_date
desc
)
from
Salaries
)
select
C
.
emp_name
,
C
.
sal_date
as
curr_date
,
C
.
sal_amt
as
curr_amt
,
P
.
sal_date
as
prev_date
,
P
.
sal_amt
as
prev_amt
from
SalaryRanks
as
c
left
outer
join
SalaryRanks
as
P
on
P
.
emp_name
=
c
.
emp_name
and
P
.
pos
=
2
where
c
.
pos
=
1
解惑八:
1、查询语句
(1)子查询将表Salaries按emp_name分组按sal_date降序排列并赋值
(2)where子句筛序序号小于3的数据
(3)select子句根据序号1或者2来确定最近的一次和最近一次的前一次
select
s1
.
emp_name
,
MAX
(
case
when
rn
=
1
then
sal_date
else
null
end
)
as
curr_date
,
MAX
(
case
when
rn
=
1
then
sal_amt
else
null
end
)
as
curr_amt
,
MAX
(
case
when
rn
=
2
then
sal_date
else
null
end
)
as
prev_date
,
MAX
(
case
when
rn
=
2
then
sal_amt
else
null
end
)
as
prev_amt
from
(
select
emp_name
,
sal_date
,
sal_amt
,
RANK
()
over
(
PARTITION
BY
emp_name
order
by
sal_date
desc
)
from
Salaries
)
as
s1
(
emp_name
,
sal_date
,
sal_amt
,
rn
)
where
rn
<
3
group
by
s1
.
emp_name
解惑九:
该方法与解惑七中一样
with
cte
(
emp_name
,
sal_date
,
sal_amt
,
rn
)
as
(
select
emp_name
,
sal_date
,
sal_amt
,
ROW_NUMBER
()
over
(
PARTITION
by
emp_name
order
by
sal_date
desc
)
as
rn
from
Salaries
)
select
o
.
emp_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
.
emp_name
=
i
.
emp_name
and
i
.
rn
=
2
where
o
.
rn
=
1
解惑十:
·1、该语句在MSSQL2010、MSSQL2012的版本才能运行
select
emp_name
,
curr_date
,
curr_amt
,
prev_date
,
prev_amt
from
(
select
emp_name
,
sal_date
as
curr_date
,
sal_amt
as
curr_amt
,
MIN
(
sal_amt
)
over
(
partition
by
emp_name
order
by
sal_date
desc
rows
between
1
following
and
following
)
as
prev_date
,
min
(
sal_amt
)
over
(
partition
by
emp_name
order
by
sal_date
desc
rows
between
1
following
and
following
)
as
prev_amt
,
row_number
()
over
(
partition
by
emp_name
order
by
sal_date
desc
)
as
rn
from
Salaries
)
as
dt
where
rn
=
1