表1id name
1 张3
2 李4
3 王5
...表2id time
1 2009-7-16 08:00:00
3 2009-7-14 07:15:00
4 2009-7-15 06:55:00
...表1 id = 表2 id------------------------------
请教各位大师如何查询 "每天" 表2中 6:00:00 -8:00:00 间没有记录的人员姓名.
比较菜请直接给出语句,即今天执行则是查询出7月16日,明日查询则是查询出7月17日
1 张3
2 李4
3 王5
...表2id time
1 2009-7-16 08:00:00
3 2009-7-14 07:15:00
4 2009-7-15 06:55:00
...表1 id = 表2 id------------------------------
请教各位大师如何查询 "每天" 表2中 6:00:00 -8:00:00 间没有记录的人员姓名.
比较菜请直接给出语句,即今天执行则是查询出7月16日,明日查询则是查询出7月17日
datediff(d,time,getdate())=0)
from a
where name not in(select a.name
from a
inner join b on a.id=b.id
where convert(varchar(10),b.time,108) between '6:00:00' and '8:00:00'
)
from a
where id not in(
select id
from b
where convert(varchar(10),time,108) between '6:00:00' and '8:00:00'
)
DECLARE @T1 TABLE(
ID INT,
NAME VARCHAR(10)
)
INSERT INTO @T1
SELECT 1, '张3' UNION ALL
SELECT 2, '李4' UNION ALL
SELECT 3, '王5'
DECLARE @T2 TABLE(
ID INT ,
time DATETIME
)
INSERT INTO @T2
SELECT 1, '2009-7-16 08:00:00' UNION ALL
SELECT 3, '2009-7-14 07:15:00' UNION ALL
SELECT 4, '2009-7-15 06:55:00'SELECT CONVERT(VARCHAR(10),T2.[TIME],102) '日期' ,T3.ID '缺席人员ID',MAX(T3.[NAME]) '缺席人员姓名'
FROM @T1 T1
RIGHT JOIN @T2 T2
ON T2.ID=T1.ID AND DATEPART(hh,T2.[TIME])>=6 AND DATEPART(hh,T2.[TIME])<=8
RIGHT JOIN @T1 T3 ON T3.ID<>ISNULL(T1.ID,0)
GROUP BY T3.ID,T2.[TIME]
/*
日期 缺席人员ID 缺席人员姓名
2009.07.14 1 张3
2009.07.14 2 李4
2009.07.15 1 张3
2009.07.15 2 李4
2009.07.15 3 王5
2009.07.16 2 李4
2009.07.16 3 王5
--不知道对不对
*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
CREATE TABLE a(id int,name varchar(10))
go
insert into a
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'老八'
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(id int,time datetime)
go
insert into b
select
1, '2009-7-16 08:00:00' union all select
3 , '2009-7-14 07:15:00' union all select
4 , '2009-7-15 06:55:00'
goselect 缺席日期=CONVERT(varchar(10),time,120),缺席人=name
from (
select a.id,name,time from a cross join b where DATEPART(hh,[TIME]) between 6 and 8
except
select a.id,name ,time from a join b on a.id=b.id and DATEPART(hh,[TIME]) between 6 and 8) t
order by time
/*------------(3 行受影响)
缺席日期 缺席人
---------- ----------
2009-07-14 张三
2009-07-14 李四
2009-07-14 老八
2009-07-15 张三
2009-07-15 李四
2009-07-15 王五
2009-07-16 李四
2009-07-16 王五
2009-07-16 老八(9 行受影响)
-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
CREATE TABLE a(id int,name varchar(10))
go
insert into a
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'老八'
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(id int,time datetime)
go
insert into b
select
1, '2009-7-16 08:00:00' union all select
3 , '2009-7-14 07:15:00' union all select
4 , '2009-7-15 06:55:00'
go
CREATE FUNCTION dbo.f_tb(@id varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + name FROM ktv WHERE time=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
select * into ktv from
(select top 1000 a.id,name,time=CONVERT(varchar(10),time,120) from a cross join b where DATEPART(hh,[TIME]) between 6 and 8
except
select top 1000 a.id,name ,time=CONVERT(varchar(10),time,120) from a join b on a.id=b.id and DATEPART(hh,[TIME]) between 6 and 8 order by time) t
go
select distinct 日期=time ,缺席人=dbo.f_tb(time) from ktv
/*------------(3 行受影响)
日期 缺席人
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009-07-14 张三,李四,老八
2009-07-15 张三,李四,王五
2009-07-16 李四,王五,老八(3 行受影响)
-------*/
这是另外一种现实形似
DECLARE @T1 TABLE(
ID INT,
NAME VARCHAR(10)
)
INSERT INTO @T1
SELECT 1, '张3' UNION ALL
SELECT 2, '李4' UNION ALL
SELECT 3, '王5' DECLARE @T2 TABLE(
ID INT ,
TIME DATETIME
)
INSERT INTO @T2
SELECT 1, '2009-7-16 08:00:00' UNION ALL
SELECT 3, '2009-7-14 07:15:00' UNION ALL
SELECT 4, '2009-7-15 06:55:00'DECLARE @BEGINDATE DATETIME -- 定义要查询的缺席起始日期
SET @BEGINDATE = '2009-7-14'
DECLARE @ENDDATE DATETIME -- 定义要查询的缺席结束日期
SET @ENDDATE ='2009-7-16'
SELECT
A.ID,
A.NAME,
CONVERT(VARCHAR(10),A.DATE,120) AS DATE
FROM
(
SELECT A.*,DATEADD(DAY,NUMBER,@BEGINDATE) AS DATE
FROM @T1 A,MASTER..SPT_VALUES B
WHERE B.TYPE = 'P' AND DATEADD(DAY,NUMBER,@BEGINDATE)<=@ENDDATE
)A
WHERE NOT EXISTS
(SELECT 1 FROM @T2
WHERE ID = A.ID
AND DATEDIFF(DAY,A.DATE,TIME)=0
AND CONVERT(VARCHAR(10),TIME,108) BETWEEN '06:00:00' AND '08:00:00'
)
create table lin1(id int,name varchar(100))
insert into lin1 select 1,'张3'
insert into lin1 select 2,'李4'
insert into lin1 select 3,'王5'
insert into lin1 select 4,'赵6'create table lin2(id int,time datetime)
insert into lin2 select 1,'2009-07-16 06:05.001'
insert into lin2 select 2,'2009-07-16 07:00.001'
insert into lin2 select 2,'2009-07-16 06:00.001'
insert into lin2 select 4,'2009-07-16 09:00.001'
-----
create view lin_view
as
select getdate() as dt
go
-----
create proc lin_proc
as
begin
declare @a varchar(100)
select @a=convert(varchar(10),dt,121) from lin_view
--select @a
select id,name from lin1 where id not in (select id from lin2 where lin2.time between convert(datetime,@a+' 06:00.001') and convert(datetime,@a+' 08:00.999'))
end
go
exec lin_proc
-----
go
drop table lin1
drop table lin2
drop proc lin_proc
---
id name
----------- ----------------------------------------------------------------------------------------------------
3 王5
4 赵6(2 行受影响)