求一个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中读取

急用，想了一天也没写出解决办法，麻烦路过的高手帮帮忙，谢谢了

...全文

130 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

一缕青烟 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

AcHerat 元老 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';



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字段

AcHerat 元老 2011-07-28

打赏
举报



select b.id,a.workdate,a.doing,b.name

from aaa a cross join bbb b on a.name = b.name

支持ACCESS或SQL SERVER数据库（通过ODBC支持），兼容etchat用户文件格式。用户注册管理部分用DLL形式从iChat.exe中独立出来。用户注册、验证部分DLL模块源码公开。

需求是这样的，下面的一组数据，是从设备里面读出来的，这个是一个累积计数，以135.252.126.57这个设备计算，08/18日计数是20245，08/19日读取时20245，说明在这个时间区间内读数没有变化。如果是多个设备，如何通过...

备忘一下在sql中求日期均值的语句首先：AVG（）只能作用与数值型字段，不能作用于日期字段。所以不能像求数值均值一样求日期均值。要求日期均值，需要先把日期转为Long , 然后对转后的Long求均值，对Long求...

大纲1.汇总分析2.分组3.对分组结果指定条件4.用sql解决业务问题5....如何看懂报表信息一,汇总分析汇总函数汇总函数作用count 求某列的行数sum 对某列数据求和avg 求某列数据的平均值max 求某列数据...

sql求两个表的差

MS-SQL Server

34,838

社区成员

254,631

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章