• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求一个sql

xueer8835 2011-07-28 03:30:53
我现在数据库中有两个表格
表格aaa

字段
ID WorkDate Doing Name
--------------------------------------------
1 2011-07-01 值班 小A
2 2011-07-03 休假 小B
3 2011-07-04 请假 小C
. . .
. . .
. . .

表格bbb

字段
ID Name
--------------------------------------------
1 小A
2 小B
3 小C
4 小D
5 小E
. .



我现在要的结果是


字段
ID WorkDate Doing Name
--------------------------------------------
1 2011-07-01 值班 小A
2 2011-07-01 null 小B
3 2011-07-01 null 小C
4 2011-07-01 null 小D
5 2011-07-01 null 小E

6 2011-07-02 null 小A
7 2011-07-02 null 小B
8 2011-07-02 null 小C
9 2011-07-02 null 小D
10 2011-07-02 null 小E

11 2011-07-03 null 小A
12 2011-07-03 休假 小B
13 2011-07-03 null 小C
14 2011-07-03 null 小D
15 2011-07-03 null 小E

16 2011-07-04 null 小A
17 2011-07-04 null 小B
18 2011-07-04 请假 小C
19 2011-07-04 null 小D
20 2011-07-04 null 小E
. . .
. . .
. . .
1 2011-07-31 null 小E



解释一下就是我现在给定个日期区间 startdate: 2011-07-01~ enddate: 2011-07-31日,列出每天的日期
如果我现在表bbb中有5个人,则一个日期对应5条记录 Doing 字段的值 从表aaa中读取


急用,想了一天也没写出解决办法,麻烦路过的高手帮帮忙,谢谢了
...全文
90 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
一缕青烟 2011-07-29

create table #b
( id int ,
name nvarchar(10)
)

insert into #b
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'

create table #a
(
id int,
kdate date,
doing nvarchar(10)
)

insert into #a
select 1,'2011-07-1','值班' union all
select 3,'2011-07-2','请假' union all
select 1,'2011-07-2','值班' union all
select 2,'2011-07-3','休假'

declare @yandm nvarchar(10),@d int;
set @yandm='2011-7-';
set @d=31;
with mycet(年月,日期)
as
(
select @yandm,1
union all
select @yandm,日期+1 from mycet where 日期<@d
)

select #b.id,a.日期,#a.doing,#b.name from(
select cast(年月+CAST(日期 as nvarchar(4)) as date) as 日期,null as Doing from mycet
) a
cross join #b
left join #a on a.日期=#a.kdate and #b.id=#a.id

/*

id 日期 doing name
1 2011-07-01 值班 a
2 2011-07-01 NULL b
3 2011-07-01 NULL c
4 2011-07-01 NULL d
1 2011-07-02 值班 a
2 2011-07-02 NULL b
3 2011-07-02 请假 c
4 2011-07-02 NULL d
1 2011-07-03 NULL a
2 2011-07-03 休假 b
3 2011-07-03 NULL c
4 2011-07-03 NULL d
1 2011-07-04 NULL a
2 2011-07-04 NULL b
3 2011-07-04 NULL c
4 2011-07-04 NULL d
1 2011-07-05 NULL a
2 2011-07-05 NULL b
3 2011-07-05 NULL c
4 2011-07-05 NULL d
1 2011-07-06 NULL a
2 2011-07-06 NULL b
3 2011-07-06 NULL c
4 2011-07-06 NULL d
1 2011-07-07 NULL a
2 2011-07-07 NULL b
3 2011-07-07 NULL c
4 2011-07-07 NULL d
1 2011-07-08 NULL a
2 2011-07-08 NULL b
3 2011-07-08 NULL c
4 2011-07-08 NULL d
1 2011-07-09 NULL a
2 2011-07-09 NULL b
3 2011-07-09 NULL c
4 2011-07-09 NULL d
1 2011-07-10 NULL a
2 2011-07-10 NULL b
3 2011-07-10 NULL c
4 2011-07-10 NULL d
1 2011-07-11 NULL a
2 2011-07-11 NULL b
3 2011-07-11 NULL c
4 2011-07-11 NULL d
1 2011-07-12 NULL a
2 2011-07-12 NULL b
3 2011-07-12 NULL c
4 2011-07-12 NULL d
1 2011-07-13 NULL a
2 2011-07-13 NULL b
3 2011-07-13 NULL c
4 2011-07-13 NULL d
1 2011-07-14 NULL a
2 2011-07-14 NULL b
3 2011-07-14 NULL c
4 2011-07-14 NULL d
1 2011-07-15 NULL a
2 2011-07-15 NULL b
3 2011-07-15 NULL c
4 2011-07-15 NULL d
1 2011-07-16 NULL a
2 2011-07-16 NULL b
3 2011-07-16 NULL c
4 2011-07-16 NULL d
1 2011-07-17 NULL a
2 2011-07-17 NULL b
3 2011-07-17 NULL c
4 2011-07-17 NULL d
1 2011-07-18 NULL a
2 2011-07-18 NULL b
3 2011-07-18 NULL c
4 2011-07-18 NULL d
1 2011-07-19 NULL a
2 2011-07-19 NULL b
3 2011-07-19 NULL c
4 2011-07-19 NULL d
1 2011-07-20 NULL a
2 2011-07-20 NULL b
3 2011-07-20 NULL c
4 2011-07-20 NULL d
1 2011-07-21 NULL a
2 2011-07-21 NULL b
3 2011-07-21 NULL c
4 2011-07-21 NULL d
1 2011-07-22 NULL a
2 2011-07-22 NULL b
3 2011-07-22 NULL c
4 2011-07-22 NULL d
1 2011-07-23 NULL a
2 2011-07-23 NULL b
3 2011-07-23 NULL c
4 2011-07-23 NULL d
1 2011-07-24 NULL a
2 2011-07-24 NULL b
3 2011-07-24 NULL c
4 2011-07-24 NULL d
1 2011-07-25 NULL a
2 2011-07-25 NULL b
3 2011-07-25 NULL c
4 2011-07-25 NULL d
1 2011-07-26 NULL a
2 2011-07-26 NULL b
3 2011-07-26 NULL c
4 2011-07-26 NULL d
1 2011-07-27 NULL a
2 2011-07-27 NULL b
3 2011-07-27 NULL c
4 2011-07-27 NULL d
1 2011-07-28 NULL a
2 2011-07-28 NULL b
3 2011-07-28 NULL c
4 2011-07-28 NULL d
1 2011-07-29 NULL a
2 2011-07-29 NULL b
3 2011-07-29 NULL c
4 2011-07-29 NULL d
1 2011-07-30 NULL a
2 2011-07-30 NULL b
3 2011-07-30 NULL c
4 2011-07-30 NULL d
1 2011-07-31 NULL a
2 2011-07-31 NULL b
3 2011-07-31 NULL c
4 2011-07-31 NULL d

*/
回复
xueer8835 2011-07-29
得分的四位的代码修改一下都能实现我要的东西,非常感谢,分数不多感谢支持
回复
wujianfeng32 2011-07-28
不难,你这没有关联的就是交叉
回复
cd731107 2011-07-28
declare @tb table(ID int,WorkDate varchar(10),Doing varchar(10),Name varchar(10))
insert into @tb
select 1,'2011-07-01','值班','小A' union all
select 2,'2011-07-03','休假','小B' union all
select 3,'2011-07-04','请假','小C';

declare @tb1 table(ID int,Name varchar(10))
insert into @tb1
select 1,'小A' union all
select 2,'小B' union all
select 3,'小C' union all
select 4,'小D' union all
select 5,'小E'

select c.id,c.WorkDate,d.Doing,c.name from
(select a.id,a.name ,dateadd(dd,number,'2011-07-01') as WorkDate
from @tb1 a ,master..spt_values b
WHERE b.type='P'
and dateadd(dd,b.number,'2011-07-01')<='2011-07-31') c
left join @tb d on c.WorkDate=d.WorkDate and c.Name=d.name
order by c.WorkDate,c.id

/*


id WorkDate Doing name
----------- ------------------------------------------------------ ---------- ----------
1 2011-07-01 00:00:00.000 值班 小A
2 2011-07-01 00:00:00.000 NULL 小B
3 2011-07-01 00:00:00.000 NULL 小C
4 2011-07-01 00:00:00.000 NULL 小D
5 2011-07-01 00:00:00.000 NULL 小E
1 2011-07-02 00:00:00.000 NULL 小A
2 2011-07-02 00:00:00.000 NULL 小B
3 2011-07-02 00:00:00.000 NULL 小C
4 2011-07-02 00:00:00.000 NULL 小D
5 2011-07-02 00:00:00.000 NULL 小E
1 2011-07-03 00:00:00.000 NULL 小A
2 2011-07-03 00:00:00.000 休假 小B
3 2011-07-03 00:00:00.000 NULL 小C
4 2011-07-03 00:00:00.000 NULL 小D
5 2011-07-03 00:00:00.000 NULL 小E
1 2011-07-04 00:00:00.000 NULL 小A
2 2011-07-04 00:00:00.000 NULL 小B
3 2011-07-04 00:00:00.000 请假 小C
4 2011-07-04 00:00:00.000 NULL 小D
5 2011-07-04 00:00:00.000 NULL 小E
1 2011-07-05 00:00:00.000 NULL 小A
2 2011-07-05 00:00:00.000 NULL 小B
3 2011-07-05 00:00:00.000 NULL 小C
4 2011-07-05 00:00:00.000 NULL 小D
5 2011-07-05 00:00:00.000 NULL 小E

.........


*/
回复
mr_cheung 2011-07-28
你可以用下这个![Quote=引用 2 楼 xueyong4712816 的回复:]
表A和表B交叉连接,然后在加ID字段
[/Quote]
回复
silentcross 2011-07-28

Create function getnamelist(@ID int)
returns table
As
return(
select aaa.Doing, bbb.[Name] from dbo.bbb left outer join dbo.aaa
on aaa.ID = @ID and aaa.[Name] = bbb.[Name]
)



select
aaa.ID,aaa.WorkDate,tmp.*
from aaa
cross apply getnamelist(aaa.ID) as tmp


1 2011-07-01 00:00:00.000 值班 小A
1 2011-07-01 00:00:00.000 NULL 小B
1 2011-07-01 00:00:00.000 NULL 小C
1 2011-07-01 00:00:00.000 NULL 小D
1 2011-07-01 00:00:00.000 NULL 小E
2 2011-07-03 00:00:00.000 NULL 小A
2 2011-07-03 00:00:00.000 休假 小B
2 2011-07-03 00:00:00.000 NULL 小C
2 2011-07-03 00:00:00.000 NULL 小D
2 2011-07-03 00:00:00.000 NULL 小E
3 2011-07-04 00:00:00.000 NULL 小A
3 2011-07-04 00:00:00.000 NULL 小B
3 2011-07-04 00:00:00.000 请假 小C
3 2011-07-04 00:00:00.000 NULL 小D
3 2011-07-04 00:00:00.000 NULL 小E
回复

declare @tb table(ID int,WorkDate varchar(10),Doing varchar(10),Name varchar(10))
insert into @tb
select 1,'2011-07-01','值班','小A' union all
select 2,'2011-07-03','休假','小B' union all
select 3,'2011-07-04','请假','小C';
declare @tb1 table(ID int,Name varchar(10))
insert into @tb1
select 1,'小A' union all
select 2,'小B' union all
select 3,'小C' union all
select 4,'小D' union all
select 5,'小E' union all
select 6,'小F';

select a.id,a.workdate,c.doing,a.name
from (select b.*,a.workdate from @tb a cross join @tb1 b) a
left join @tb c on a.id = c.id and a.workdate = c.workdate
order by a.workdate

/*************

id workdate doing name
----------- ---------- ---------- ----------
1 2011-07-01 值班 小A
2 2011-07-01 NULL 小B
3 2011-07-01 NULL 小C
4 2011-07-01 NULL 小D
5 2011-07-01 NULL 小E
6 2011-07-01 NULL 小F
1 2011-07-03 NULL 小A
2 2011-07-03 休假 小B
3 2011-07-03 NULL 小C
4 2011-07-03 NULL 小D
5 2011-07-03 NULL 小E
6 2011-07-03 NULL 小F
1 2011-07-04 NULL 小A
2 2011-07-04 NULL 小B
3 2011-07-04 请假 小C
4 2011-07-04 NULL 小D
5 2011-07-04 NULL 小E
6 2011-07-04 NULL 小F

(18 行受影响)
回复
chtzhking 2011-07-28
数据只贴出来部分,你可以多添加一下数据试试
where number between 0 and 40 中number的范围是-1~2048


" where month(DATEADD(DAY,a.number,a.jishu))=7 "这句可以修改为" where month(DATEADD(DAY,a.number,a.jishu)) in (7,8,9)"()内填写所查询月份即可,前提是把number的范围先改了
回复
chtzhking 2011-07-28

declare @tb table(ID int,WorkDate varchar(10),Doing varchar(10),Name varchar(10))
insert into @tb
select 1,'2011-07-01','值班','小A' union all
select 2,'2011-07-03','休假','小B' union all
select 3,'2011-07-04','请假','小C';
declare @tb1 table(ID int,Name varchar(10))
insert into @tb1
select 1,'小A' union all
select 2,'小B' union all
select 3,'小C' union all
select 4,'小D' union all
select 5,'小E' union all
select 6,'小F';


with cte as (
select convert(varchar(10),DATEADD(DAY,a.number,a.jishu),23) as shijian
from (
select distinct number,'2011-07-01' as jishu ---修改为所需月份的第一天即可
from master..spt_values
where number between 0 and 40
) as a where month(DATEADD(DAY,a.number,a.jishu))=7 ----7为7月
)
select ROW_NUMBER()over(order by B.shijian,C.NAME) as id,B.shijian,ISNULL(D.Doing,'') AS Doing,C.Name
from cte b CROSS join @tb1 c
LEFT JOIN @tb AS D
ON B.shijian=D.WorkDate AND C.Name=D.Name

-------------
id shijian Doing Name
1 2011-07-01 值班 小A
2 2011-07-01 小B
3 2011-07-01 小C
4 2011-07-01 小D
5 2011-07-01 小E
6 2011-07-01 小F
7 2011-07-02 小A
8 2011-07-02 小B
9 2011-07-02 小C
10 2011-07-02 小D
11 2011-07-02 小E
12 2011-07-02 小F
13 2011-07-03 小A
14 2011-07-03 休假 小B
15 2011-07-03 小C
16 2011-07-03 小D
17 2011-07-03 小E
18 2011-07-03 小F
19 2011-07-04 小A
20 2011-07-04 小B
21 2011-07-04 请假 小C
22 2011-07-04 小D
23 2011-07-04 小E
24 2011-07-04 小F
25 2011-07-05 小A
26 2011-07-05 小B
27 2011-07-05 小C
28 2011-07-05 小D
29 2011-07-05 小E
30 2011-07-05 小F

回复
xueyong4712816 2011-07-28
表A和表B交叉连接,然后在加ID字段
回复

select b.id,a.workdate,a.doing,b.name
from aaa a cross join bbb b on a.name = b.name
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2011-07-28 03:30
社区公告
暂无公告