tab1 (总字典表,记录每个人初始工作性质):
姓名 | 工作 | 开始日期
------------------------------------
张三 | A | 2015-04-10
李四 | A | 2015-04-10
王五 | A | 2015-04-10
tab2(工作变更表,记录每人每次工作性质变更的时间):
姓名 | 工作 | 变更日期
------------------------------------
张三 | B | 2015-10-10
张三 | A | 2015-10-18
李四 | C | 2015-10-02
tab3(工作量表.记录每人每天的工作量):
姓名 | 工作量 | 日期
------------------------------------
张三 | 1 | 2015-10-01
张三 | 1 | 2015-10-02
....
李四 | 2 | 2015-10-02
生成一个视图.要求视图如下(查询10月份所有人每天的工作量以及工作性质):
姓名 | 日期 | 工作 | 工作量
张三 | 2015-10-01 | A | 1
张三 | 2015-10-02 | A | 1
张三 | 2015-10-03 | A | 1
....
张三 | 2015-10-10 | B | 1
张三 | 2015-10-11 | B | 1
....
张三 | 2015-10-18 | A | 1
....
张三 | 2015-10-31 | A | 1
李四 | 2015-10-01 | A | 1
李四 | 2015-10-02 | C | 1
...
李四 | 2015-10-31 | C | 1
王五 | 2015-10-01 | A | 1
...
王五 | 2015-10-31 | A | 1
请问这条SQL如何写...
就是3个表..
表1存放总字典数据.表2存放每个人工作性质变更的日期.如果没有就表示工作一直没改变.表3存放每人每天的工作量
最后求时间段内每个人每天的工作量以及工作性质..
------解决思路----------------------
WITH
/* 测试数据
tab1(姓名,工作,开始日期)AS(
SELECT '张三','A','2015-04-10' UNION ALL
SELECT '李四','A','2015-04-10' UNION ALL
SELECT '王五','A','2015-10-28' -- 调整了开始日期
)
,tab2(姓名,工作,变更日期)AS(
SELECT '张三','B','2015-10-10' UNION ALL
SELECT '张三','A','2015-10-18' UNION ALL
SELECT '李四','C','2015-10-02'
)
,tab3(姓名,工作量,日期)AS(
SELECT '张三',1,'2015-10-01' UNION ALL
SELECT '张三',1,'2015-10-02' UNION ALL
SELECT '李四',2,'2015-10-02'
), */
c AS ( -- 日历
SELECT DATEADD(day,number,'2015-10-01') 日期
FROM master..spt_values
WHERE type = 'p'
AND number < 31
)
,a AS (
SELECT t1.姓名,
c.日期,
t1.工作,
t3.工作量
FROM tab1 t1
JOIN c
ON t1.开始日期 <= c.日期
LEFT JOIN tab3 t3
ON t1.姓名 = t3.姓名
AND c.日期 = t3.日期
)
SELECT a.姓名,
a.日期,
ISNULL(b.工作,a.工作) 工作,
a.工作量
FROM a
OUTER APPLY (
SELECT TOP 1 *
FROM tab2 t2
WHERE t2.姓名 = a.姓名
AND t2.变更日期 <= a.日期
ORDER BY t2.变更日期 DESC
) b
ORDER BY 姓名,日期
姓名 日期 工作 工作量
---- ------------ ---- -----------
李四 2015-10-01 A NULL
李四 2015-10-02 C 2
李四 2015-10-03 C NULL
李四 2015-10-04 C NULL
李四 2015-10-05 C NULL
李四 2015-10-06 C NULL
李四 2015-10-07 C NULL
李四 2015-10-08 C NULL
李四 2015-10-09 C NULL
李四 2015-10-10 C NULL
李四 2015-10-11 C NULL
李四 2015-10-12 C NULL
李四 2015-10-13 C NULL
李四 2015-10-14 C NULL
李四 2015-10-15 C NULL
李四 2015-10-16 C NULL
李四 2015-10-17 C NULL
李四 2015-10-18 C NULL
李四 2015-10-19 C NULL
李四 2015-10-20 C NULL
李四 2015-10-21 C NULL
李四 2015-10-22 C NULL
李四 2015-10-23 C NULL
李四 2015-10-24 C NULL
李四 2015-10-25 C NULL
李四 2015-10-26 C NULL
李四 2015-10-27 C NULL
李四 2015-10-28 C NULL
李四 2015-10-29 C NULL
李四 2015-10-30 C NULL
李四 2015-10-31 C NULL
王五 2015-10-28 A NULL
王五 2015-10-29 A NULL
王五 2015-10-30 A NULL
王五 2015-10-31 A NULL
张三 2015-10-01 A 1
张三 2015-10-02 A 1
张三 2015-10-03 A NULL
张三 2015-10-04 A NULL
张三 2015-10-05 A NULL
张三 2015-10-06 A NULL
张三 2015-10-07 A NULL
张三 2015-10-08 A NULL
张三 2015-10-09 A NULL
张三 2015-10-10 B NULL
张三 2015-10-11 B NULL
张三 2015-10-12 B NULL
张三 2015-10-13 B NULL
张三 2015-10-14 B NULL
张三 2015-10-15 B NULL
张三 2015-10-16 B NULL
张三 2015-10-17 B NULL
张三 2015-10-18 A NULL
张三 2015-10-19 A NULL
张三 2015-10-20 A NULL
张三 2015-10-21 A NULL
张三 2015-10-22 A NULL
张三 2015-10-23 A NULL
张三 2015-10-24 A NULL
张三 2015-10-25 A NULL
张三 2015-10-26 A NULL
张三 2015-10-27 A NULL
张三 2015-10-28 A NULL
张三 2015-10-29 A NULL
张三 2015-10-30 A NULL
张三 2015-10-31 A NULL
------解决思路----------------------
select 姓名
,日期
,工作=ISNULL((select top 1 工作 from tab2 where 姓名=t.姓名 and 变更日期<=t.日期 order by 变更日期 desc)
,(select MAX(工作) from tab1 where 姓名=t.姓名))
,工作量
from tab3 t
where DATEDIFF(M,日期,'2015-10-01')=0
order by 姓名,日期