求助：求一句两表关联查询的语句，比较难！！

两个表，用户表user 和记录表book
用户表user
userid      username
1           张三
2           李四
3           王五
。。。
记录表book
id       userid      starttime     endtime     creattime
1           1         2006-1-2     2006-1-6     2006-1-4
2           2         2006-1-2     2006-1-6     2006-1-4
3           3         2006-1-2     2006-1-6     2006-1-4
4           1         2006-1-9     2006-1-13    2006-1-10
5           2         2006-1-9     2006-1-13    2006-1-10
6           1         2006-1-16    2006-1-20    2006-1-18
7           3         2006-1-16    2006-1-20    2006-1-18
8           3         2006-1-23    2006-1-27    2006-1-25
。。。
通过userid关联，每个用户每周往book表里只能输入一条记录。
creattime是输入时的日期
starttime和endtime两个字段自动记录了输入时候的所在的星期的周一和周五的日期
如果这周没有输入，则这周一和周五的记录则没有，是没有，不是为空！

现在我想取出的是从2006年1月1日起到现在每个用户有哪几周没有输入数据，以endtime周五为节点
结果如下
userid   username
1          张三      2006-1-27 ...
2          李四      2006-1-20  2006-1-27...
3          王五      2006-1-13 ...
如果有的用户每周都没有漏掉，则列出username ，后面没有写的周为空


或者结果竖着排也可以
userid   username
1          张三      2006-1-27
2          李四      2006-1-20
2          李四      2006-1-27
3          王五      2006-1-13

该如何写啊？
各位高手帮帮忙啊，困惑了好久的啊！
如果分不够还可以在加！
多谢各位了啊！

18 个解决方案

#1

最好写个存储过程

declare @begin datetime
declare @end datetime

set @begin='2006-1-1'
set @end=convert(char(8),getdate(),112)

select x.*
from (
select u.*,dateadd(day,a+b+c,@begin) as endtime
from (
select 0 as a
union all
select 1 as a
union all
select 2 as a
union all
select 3 as a
union all
select 4 as a
union all
select 5 as a
union all
select 6 as a
union all
select 7 as a
union all
select 8 as a
union all
select 9 as a
) as a,(
select 0 as b
union all
select 10
union all
select 20
union all
select 30
union all
select 40
union all
select 50
union all
select 60
union all
select 70 
union all
select 80
union all
select 90
) as b,(
select 0 as c
union all
select 100
union all
select 200
union all
select 300
union all
select 400
union all
select 500
union all
select 600
union all
select 700
union all
select 800
union all
select 900
) as c,user u
where dateadd(day,a+b+c,@begin)<=@end
and datepart(weekday,dateadd(day,a+b+c,@begin))=6
) as x left join book b
on x.userid=b.userid and x.endtime=b.endtime
where b.id is null


--没有测试，效率也可能有问题

#2

--可以使用下边产生周五日期的表值函数.

create function dbo.fn_GenerateFriday
        (@start_date datetime,
         @end_date datetime)
returns @tb table (end_date datetime)
as
begin

while(@start_date<@end_date)
begin
        insert @tb select dateadd(d,6-datepart(dw,@start_date),@start_date)
        set @start_date=dateadd(wk,1,@start_date)
end
return
end
Go

#3

--然后利用下边的查询就可以了.

select a.userid,a.username,b.end_date

from [user] a cross join dbo.fn_GenerateFriday('2006-01-01','2006-02-21') b
              left join [book] c
on b.userid=c.userid and b.endtime=c.end_date

#4

--测试

--创建测试表,插入测试数据------------------------------

create table [user](userid int,username varchar(100))

insert [user] select 1,'张三'
insert [user] select 2,'李四'
insert [user] select 3,'王五'

create table book(id int,userid int,starttime datetime,endtime datetime,createtime datetime)

insert book select 1,1,'2006-01-02','2006-01-06','2006-1-4'
insert book select 2,2,'2006-01-02','2006-01-06','2006-1-4'

--查询----------------------------------------------

select a.userid,a.username,b.end_date
from [user] a cross join dbo.fn_GenerateFriday('2006-01-01','2006-02-21') b
left join [book] c
on a.userid=c.userid and b.end_date=c.endtime
where c.id is null

#5

谢谢楼上的啊！
问一下
dbo.fn_GenerateFriday('2006-01-01','2006-02-21')
是取到当前日期，能不能不写2006-02-21而是用getdate（）代替，怎么老是出错啊，该如何改啊？ 

顺便问一下，取出来的结果能不能横着排啊？
就像
userid   username
1          张三      2006-1-27 ...
2          李四      2006-1-20  2006-1-27...
3          王五      2006-1-13 ...
如果有的用户每周都没有漏掉，则列出username ，后面没有写的周为空

谢了啊！

#6

--查询----------------------------------------------
declare @d datetime
set @d = '2006-09-09'  ----
select distinct a.userid,a.username,b.end_date
from [user] a cross join dbo.fn_GenerateFriday('2006-01-01',@d) b
left join [book] c
on a.userid=c.userid and b.end_date=c.endtime
where c.id is null

#7

函数里边不能使用getdate()这样的不确定函数.

#8

呵呵，看来只能象 happyflystone(仙林幽谷客)说的那样
定义变量了啊
declare @d datetime
set @d = Getdate() 


但是那种横排该如何写呢？？？

#9

/*建表*/
create table [user](userid int,username varchar(50))

insert [user] 
    select 1,'张三' union
    select 2,'李四' union
    select 3,'王五'

create table book(id int,userid int,starttime datetime,endtime datetime,createtime datetime)

insert book 
    select 1,1,'2006-1-2','2006-1-6','2006-1-4' union
    select 2,2,'2006-1-2','2006-1-6','2006-1-4' union
    select 3,3,'2006-1-2','2006-1-6','2006-1-4' union
    select 4,1,'2006-1-9','2006-1-13','2006-1-10' union
    select 5,2,'2006-1-9','2006-1-13','2006-1-10' union
    select 6,1,'2006-1-16','2006-1-20','2006-1-18' union
    select 7,3,'2006-1-16','2006-1-20','2006-1-18' union
    select 8,3,'2006-1-23','2006-1-27','2006-1-25'

/*取所有周五*/
create function dbo.f_GenerateFriday
       (@start_date datetime,
        @end_date datetime)
returns @tb table (endtme datetime)
as
begin

while(@start_date<@end_date)
begin
        insert @tb select dateadd(d,6-datepart(dw,@start_date),@start_date)
        set @start_date=dateadd(wk,1,@start_date)
end
return
end
Go

/*横排*/
create function dbo.f_GenerateStrEndtime
        (@userid int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s = @s+convert(char(10),d.endtime,120)+','
        from (select c.endtime
              from [user] a cross join  dbo.f_GenerateFriday('2006-01-01','2006-02-21') c
              left join [book] b
              on a.userid=b.userid and c.endtime=b.endtime
             where b.id is null and a.userid=@userid) d
SELECT @s = LEFT(@s,LEN(@s) - 1)
RETURN(@s)
end

/*取数*/
select a.userid,a.username,dbo.fn_GenerateStrEndtime(a.userid) endtime
  from [user] a

/*结果*/
userid   username  endtime
1 张三 2006-01-27,2006-02-03,2006-02-10,2006-02-17,2006-02-24
2 李四 2006-01-20,2006-01-27,2006-02-03,2006-02-10,2006-02-17,2006-02-24
3 王五 2006-01-13,2006-02-03,2006-02-10,2006-02-17,2006-02-24

#10

抱歉，取数部分应该为：
select a.userid,a.username,dbo.f_GenerateStrEndtime(a.userid) endtime
  from [user] a

#11

问个简单一点的sql吧！
1、现在我想取出的上周没有输入数据用户。
2、取出本周和上周都没有输入数据的用户。
这两个sql该怎么写啊？
应该简单一些吧！

#12

取得上周没有输入的
select a.username
from  [user] a
where a.userid not in (select  userid  from book b
where b.creattime<dateadd(wk,-3,dateadd(d,6-datepart(dw,getdate()),getdate()))
     and b.endtime= dateadd(wk,-2,dateadd(d,6-datepart(dw,getdate()),getdate()))
)

#13

取出本周没有输入数据的用户。
能写详细点吗！
谢了各位啊！

#14

To:huailairen(流浪猫) 
取出来的怎么是所有的人啊！？？

写三句sql啊！
1、取出上周没有输入数据用户。
2、取出本周没有输入数据用户。
3、取出本周和上周都没有输入数据的用户。

#15

--取出上周没有记录的人

SET DATEFIRST 1
Declare @LastWeekDate datetime --上一个星期五的日期
select @LastWeekDate=dateadd(d,-9+datepart(dw,getdate()),getdate())

select a.* from [user] a
where not exists(select 1 from book where userid=a.userid and endtime=@LastWeekDate)
set datefirst 7

#16

不好意思，上面写的有点问题，

--取出上周没有记录的人
--这里星期日为本周最后一天
SET DATEFIRST 1
Declare @LastWeekDate datetime --上一个星期五的日期
select @LastWeekDate=dateadd(d,-2-datepart(dw,getdate()),getdate())

select a.* from [user] a
where not exists(select 1 from book where userid=a.userid and endtime=@LastWeekDate)
set datefirst 7

#17

--本周没有记录的人
SET DATEFIRST 1
Declare @thisWeekDate char(10) --上一个星期五的日期
select @thisWeekDate=dateadd(d,5-datepart(dw,getdate()),getdate())

select a.* from [user] a
where not exists(select 1 from book where userid=a.userid and endtime=@thisWeekDate)
set datefirst 7

--取出本周和上周都没有输入数据的用户
SET DATEFIRST 1
Declare @thisWeekDate char(10) --上一个星期五的日期
Declare @LastWeekDate char(10) --本周星期五的日期
 
select @thisWeekDate=dateadd(d,5-datepart(dw,getdate()),getdate())
select @LastWeekDate=dateadd(d,-9+datepart(dw,getdate()),getdate())

select a.* from [user] a
where not exists(select 1 from book where userid=a.userid and 
(endtime=@thisWeekDate or endtime=@LastWeekDate ))
set datefirst 7

#18

楼上的老兄
为什么我在sql中把
thisWeekDate 直接换成 dateadd(d,-9+datepart(dw,getdate()),getdate())
就不行了呢？？