Table t1:(一张员工表)
name degree birth...
a
b
c
Table t2:(记录员工每天计划的日志表)
name date plan
a 0331 aplan1
a 0401 aplan2
a 0402 aplan3
a 0403 aplan4
b 0401 bplan1
b 0402 bplan2
显示结果:(只显示昨天和今天的计划)
name yesterdayPlan todayPlan
a aplan3 aplan4
b bplan2 null
c null null
请问一下大家这个结果能不能用个SQL语句实现?可以的话怎么写?谢谢了
name degree birth...
a
b
c
Table t2:(记录员工每天计划的日志表)
name date plan
a 0331 aplan1
a 0401 aplan2
a 0402 aplan3
a 0403 aplan4
b 0401 bplan1
b 0402 bplan2
显示结果:(只显示昨天和今天的计划)
name yesterdayPlan todayPlan
a aplan3 aplan4
b bplan2 null
c null null
请问一下大家这个结果能不能用个SQL语句实现?可以的话怎么写?谢谢了
declare @t1 table(name varchar(10))
insert @t1 select 'a' union all
select 'b' union all
select 'c'declare @t2 table(name varchar(10),date char(4),[plan] varchar(20))
insert @t2 select 'a','0331','aplan1' union all
select 'a','0331','aplan1' union all
select 'a','0401','aplan2' union all
select 'a','0402','aplan3' union all
select 'a','0403','aplan4' union all
select 'b','0401','aplan1' union all
select 'b','0402','aplan2' --查询
select a.name,[yesterdayPlan]=max(case date when right(convert(char(8),getdate()-1,112),4) then [plan] else null end),[todayPlan]=max(case date when right(convert(char(8),getdate(),112),4) then [plan] else null end)
from @t1 a left join @t2 b on a.name=b.name group by a.name/*结果
name yesterdayPlan todayPlan
---------- -------------------- --------------------
a aplan3 aplan4
b aplan2 NULL
c NULL NULL(所影响的行数为 3 行)
*/
create table #t1(name varchar(1))
insert #t1 select 'a'
insert #t1 select 'b'
insert #t1 select 'c'
create table #t2(name varchar(1),date varchar(4),[plan] varchar(6))
insert #t2 select 'a', '0331', 'aplan1'
insert #t2 select 'a', '0401', 'aplan2'
insert #t2 select 'a', '0402', 'aplan3'
insert #t2 select 'a', '0403', 'aplan4'
insert #t2 select 'b', '0401', 'bplan1'
insert #t2 select 'b', '0402', 'bplan2'--查询
select b.name,
yesterdayplan=max(case when date=right(convert(char(8),getdate()-1,112),4) then [plan] end),
todayplan=max(case when date=right(convert(char(8),getdate(),112),4) then [plan] end)
from #t2 a right join #t1 b on a.name=b.name group by b.name--删除测试数据
drop table #t2
drop table #t1/*结果
name yesterdayplan todayplan
---- ------------- ---------
a aplan3 aplan4
b bplan2 NULL
c NULL NULL(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。*/
declare @t table(namer varchar(8))
declare @t1 table(namer varchar(8),date1 varchar(4),plan1 varchar(10))insert into @t
select 'a' union
select 'b' union
select 'c'insert into @t1
select 'a', '0331', 'aplan1' union
select 'a', '0401', 'aplan2' union
select 'a', '0402', 'aplan3' union
select 'a', '0403', 'aplan4' union
select 'b', '0401', 'bplan1' union
select 'b', '0402', 'bplan2'
select c.namer ,
max(case date1 when SUBSTRING(CONVERT(VARCHAR(8),GETDATE()-1,112),5,8) THEN plan1 end )as yesterdayPlan,
max(case date1 when SUBSTRING(CONVERT(VARCHAR(8),GETDATE(),112),5,8) then plan1 end ) as todayPlan
from
(
select a.*,b.date1 , b.plan1 from @t a
left outer join @t1 b on a.namer = b.namer
) c
group by c.namer