表的关系:userinfo和checkinout关系如下
userinfo:
NAME USERID
刘一 1
周二 2
李三 3
徐四 4
王五 5
邹六 6
黄七 7
林八 8
易九 9
蔺十 10checkinout:
CHECKTIME USERID
2011-11-1 8:25 1
2011-11-1 17:33 1
2011-11-1 8:31 2
2011-11-1 17:42 2
2011-11-1 8:26 4
2011-11-1 19:50 4
2011-11-1 8:25 5
2011-11-1 17:32 5
2011-11-1 8:26 7
2011-11-1 17:32 7 2011-11-2 13:57 3
2011-11-2 17:48 3
2011-11-2 8:26 4
2011-11-2 18:51 4
2011-11-2 8:26 5
2011-11-2 17:30 5
2011-11-2 8:27 6
2011-11-2 18:34 6
2011-11-2 8:26 7
2011-11-2 19:49 7
2011-11-2 8:28 8
2011-11-3 8:27 3
2011-11-3 18:47 3
2011-11-3 8:10 4
2011-11-3 17:36 4
2011-11-3 8:28 7
2011-11-3 17:38 7
2011-11-3 8:24 8
2011-11-3 17:31 8
查询结果如下:
MYDATE NAME MINTIME MAXTIME
2011-11-1 刘一 8:25 17:33
2011-11-1 周二 8:31 17:42
2011-11-1 李三 NULL NULL
2011-11-1 徐四 8:26 19:50
2011-11-1 王五 8:25 17:32
2011-11-1 邹六 NULL NULL
2011-11-1 黄七 8:26 17:32
2011-11-1 林八 NULL NULL
2011-11-1 易九 NULL NULL
2011-11-1 蔺十 NULL NULL2011-11-2 刘一 NULL NULL
2011-11-2 周二 NULL NULL
2011-11-2 李三 13:57 17:48
2011-11-2 徐四 8:26 18:51
2011-11-2 王五 8:26 17:30
2011-11-2 邹六 8:27 18:34
2011-11-2 黄七 8:26 19:49
2011-11-2 林八 8:28 8:28
2011-11-2 易九 NULL NULL
2011-11-2 蔺十 NULL NULL2011-11-3 刘一 NULL NULL
2011-11-3 周二 NULL NULL
2011-11-3 李三 8:27 18:47
2011-11-3 徐四 8:10 17:36
2011-11-3 王五 NULL NULL
2011-11-3 邹六 NULL NULL
2011-11-3 黄七 8:28 17:38
2011-11-3 林八 8:24 17:31
2011-11-3 易九 NULL NULL
2011-11-3 蔺十 NULL NULL
userinfo:
NAME USERID
刘一 1
周二 2
李三 3
徐四 4
王五 5
邹六 6
黄七 7
林八 8
易九 9
蔺十 10checkinout:
CHECKTIME USERID
2011-11-1 8:25 1
2011-11-1 17:33 1
2011-11-1 8:31 2
2011-11-1 17:42 2
2011-11-1 8:26 4
2011-11-1 19:50 4
2011-11-1 8:25 5
2011-11-1 17:32 5
2011-11-1 8:26 7
2011-11-1 17:32 7 2011-11-2 13:57 3
2011-11-2 17:48 3
2011-11-2 8:26 4
2011-11-2 18:51 4
2011-11-2 8:26 5
2011-11-2 17:30 5
2011-11-2 8:27 6
2011-11-2 18:34 6
2011-11-2 8:26 7
2011-11-2 19:49 7
2011-11-2 8:28 8
2011-11-3 8:27 3
2011-11-3 18:47 3
2011-11-3 8:10 4
2011-11-3 17:36 4
2011-11-3 8:28 7
2011-11-3 17:38 7
2011-11-3 8:24 8
2011-11-3 17:31 8
查询结果如下:
MYDATE NAME MINTIME MAXTIME
2011-11-1 刘一 8:25 17:33
2011-11-1 周二 8:31 17:42
2011-11-1 李三 NULL NULL
2011-11-1 徐四 8:26 19:50
2011-11-1 王五 8:25 17:32
2011-11-1 邹六 NULL NULL
2011-11-1 黄七 8:26 17:32
2011-11-1 林八 NULL NULL
2011-11-1 易九 NULL NULL
2011-11-1 蔺十 NULL NULL2011-11-2 刘一 NULL NULL
2011-11-2 周二 NULL NULL
2011-11-2 李三 13:57 17:48
2011-11-2 徐四 8:26 18:51
2011-11-2 王五 8:26 17:30
2011-11-2 邹六 8:27 18:34
2011-11-2 黄七 8:26 19:49
2011-11-2 林八 8:28 8:28
2011-11-2 易九 NULL NULL
2011-11-2 蔺十 NULL NULL2011-11-3 刘一 NULL NULL
2011-11-3 周二 NULL NULL
2011-11-3 李三 8:27 18:47
2011-11-3 徐四 8:10 17:36
2011-11-3 王五 NULL NULL
2011-11-3 邹六 NULL NULL
2011-11-3 黄七 8:28 17:38
2011-11-3 林八 8:24 17:31
2011-11-3 易九 NULL NULL
2011-11-3 蔺十 NULL NULL
select convert(varchar,a.CHECKTIME,23) 'MYDATE',
b.name,
min(CHECKTIME) 'MINTIME',
max(CHECKTIME) 'MAXTIME'
from checkinout a
inner join userinfo b
on a.USERID=b.USERID
group by convert(varchar,a.CHECKTIME,23),b.NAME
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-07 15:17:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[userinfo]
if object_id('[userinfo]') is not null drop table [userinfo]
go
create table [userinfo]([NAME] varchar(4),[USERID] int)
insert [userinfo]
select '刘一',1 union all
select '周二',2 union all
select '李三',3 union all
select '徐四',4 union all
select '王五',5 union all
select '邹六',6 union all
select '黄七',7 union all
select '林八',8 union all
select '易九',9 union all
select '蔺十',10
--> 测试数据:[checkinout]
if object_id('[checkinout]') is not null drop table [checkinout]
go
create table [checkinout]([CHECKTIME] datetime,[USERID] int)
insert [checkinout]
select '2011-11-1 8:25',1 union all
select '2011-11-1 17:33',1 union all
select '2011-11-1 8:31',2 union all
select '2011-11-1 17:42',2 union all
select '2011-11-1 8:26',4 union all
select '2011-11-1 19:50',4 union all
select '2011-11-1 8:25',5 union all
select '2011-11-1 17:32',5 union all
select '2011-11-1 8:26',7 union all
select '2011-11-1 17:32',7 union all
select '2011-11-2 13:57',3 union all
select '2011-11-2 17:48',3 union all
select '2011-11-2 8:26',4 union all
select '2011-11-2 18:51',4 union all
select '2011-11-2 8:26',5 union all
select '2011-11-2 17:30',5 union all
select '2011-11-2 8:27',6 union all
select '2011-11-2 18:34',6 union all
select '2011-11-2 8:26',7 union all
select '2011-11-2 19:49',7 union all
select '2011-11-2 8:28',8 union all
select '2011-11-3 8:27',3 union all
select '2011-11-3 18:47',3 union all
select '2011-11-3 8:10',4 union all
select '2011-11-3 17:36',4 union all
select '2011-11-3 8:28',7 union all
select '2011-11-3 17:38',7 union all
select '2011-11-3 8:24',8 union all
select '2011-11-3 17:31',8
--------------开始查询--------------------------
select
distinct a.checktime,a.name,b.mintime,b.maxtime
from
(
select * from (select CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME from checkinout)a cross join (select NAME,USERID from userinfo)b
)a
left join
(
select USERID,Min(CHECKTIME) as mintime,MAX(CHECKTIME) as maxtime from checkinout group by USERID
)b
on
a.USERID=b.USERID
----------------结果----------------------------
/*
(10 行受影响)(29 行受影响)
checktime name mintime maxtime
---------- ---- ----------------------- -----------------------
2011-11-01 黄七 2011-11-01 08:26:00.000 2011-11-03 17:38:00.000
2011-11-01 李三 2011-11-02 13:57:00.000 2011-11-03 18:47:00.000
2011-11-01 林八 2011-11-02 08:28:00.000 2011-11-03 17:31:00.000
2011-11-01 蔺十 NULL NULL
2011-11-01 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.000
2011-11-01 王五 2011-11-01 08:25:00.000 2011-11-02 17:30:00.000
2011-11-01 徐四 2011-11-01 08:26:00.000 2011-11-03 17:36:00.000
2011-11-01 易九 NULL NULL
2011-11-01 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.000
2011-11-01 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.000
2011-11-02 黄七 2011-11-01 08:26:00.000 2011-11-03 17:38:00.000
2011-11-02 李三 2011-11-02 13:57:00.000 2011-11-03 18:47:00.000
2011-11-02 林八 2011-11-02 08:28:00.000 2011-11-03 17:31:00.000
2011-11-02 蔺十 NULL NULL
2011-11-02 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.000
2011-11-02 王五 2011-11-01 08:25:00.000 2011-11-02 17:30:00.000
2011-11-02 徐四 2011-11-01 08:26:00.000 2011-11-03 17:36:00.000
2011-11-02 易九 NULL NULL
2011-11-02 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.000
2011-11-02 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.000
2011-11-03 黄七 2011-11-01 08:26:00.000 2011-11-03 17:38:00.000
2011-11-03 李三 2011-11-02 13:57:00.000 2011-11-03 18:47:00.000
2011-11-03 林八 2011-11-02 08:28:00.000 2011-11-03 17:31:00.000
2011-11-03 蔺十 NULL NULL
2011-11-03 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.000
2011-11-03 王五 2011-11-01 08:25:00.000 2011-11-02 17:30:00.000
2011-11-03 徐四 2011-11-01 08:26:00.000 2011-11-03 17:36:00.000
2011-11-03 易九 NULL NULL
2011-11-03 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.000
2011-11-03 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.000(30 行受影响)
*/
go
create table [userinfo] (NAME nvarchar(4),USERID int)
insert into [userinfo]
select '刘一',1 union all
select '周二',2 union all
select '李三',3 union all
select '徐四',4 union all
select '王五',5 union all
select '邹六',6 union all
select '黄七',7 union all
select '林八',8 union all
select '易九',9 union all
select '蔺十',10if object_id('[checkinout]') is not null drop table [checkinout]
go
create table [checkinout] (CHECKTIME datetime,USERID int)
insert into [checkinout]
select '2011-11-1 8:25',1 union all
select '2011-11-1 17:33',1 union all
select '2011-11-1 8:31',2 union all
select '2011-11-1 17:42',2 union all
select '2011-11-1 8:26',4 union all
select '2011-11-1 19:50',4 union all
select '2011-11-1 8:25',5 union all
select '2011-11-1 17:32',5 union all
select '2011-11-1 8:26',7 union all
select '2011-11-1 17:32',7 union all
select '2011-11-2 13:57',3 union all
select '2011-11-2 17:48',3 union all
select '2011-11-2 8:26',4 union all
select '2011-11-2 18:51',4 union all
select '2011-11-2 8:26',5 union all
select '2011-11-2 17:30',5 union all
select '2011-11-2 8:27',6 union all
select '2011-11-2 18:34',6 union all
select '2011-11-2 8:26',7 union all
select '2011-11-2 19:49',7 union all
select '2011-11-2 8:28',8 union all
select '2011-11-3 8:27',3 union all
select '2011-11-3 18:47',3 union all
select '2011-11-3 8:10',4 union all
select '2011-11-3 17:36',4 union all
select '2011-11-3 8:28',7 union all
select '2011-11-3 17:38',7 union all
select '2011-11-3 8:24',8 union all
select '2011-11-3 17:31',8select * from [userinfo]
select * from [checkinout]
SELECT U.userid,U.NAME,
[日期]=convert(date,C.checktime),
[起始]=MIN(CONVERT(VARCHAR(5),C.checktime,114)),
[终止]=MAX(CONVERT(VARCHAR(5),C.checktime,114))
FROM checkinout C
left JOIN [userinfo] U ON C.userid = U.userid
GROUP BY U.userid,U.NAME,convert(date,C.checktime)
ORDER BY convert(date,C.checktime),U.userid
/*
userid NAME 日期 起始 终止
1 刘一 2011-11-01 08:25 17:33
2 周二 2011-11-01 08:31 17:42
4 徐四 2011-11-01 08:26 19:50
5 王五 2011-11-01 08:25 17:32
7 黄七 2011-11-01 08:26 17:32
3 李三 2011-11-02 13:57 17:48
4 徐四 2011-11-02 08:26 18:51
5 王五 2011-11-02 08:26 17:30
6 邹六 2011-11-02 08:27 18:34
7 黄七 2011-11-02 08:26 19:49
8 林八 2011-11-02 08:28 08:28
3 李三 2011-11-03 08:27 18:47
4 徐四 2011-11-03 08:10 17:36
7 黄七 2011-11-03 08:28 17:38
8 林八 2011-11-03 08:24 17:31*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-07 15:17:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[userinfo]
if object_id('[userinfo]') is not null drop table [userinfo]
go
create table [userinfo]([NAME] varchar(4),[USERID] int)
insert [userinfo]
select '刘一',1 union all
select '周二',2 union all
select '李三',3 union all
select '徐四',4 union all
select '王五',5 union all
select '邹六',6 union all
select '黄七',7 union all
select '林八',8 union all
select '易九',9 union all
select '蔺十',10
--> 测试数据:[checkinout]
if object_id('[checkinout]') is not null drop table [checkinout]
go
create table [checkinout]([CHECKTIME] datetime,[USERID] int)
insert [checkinout]
select '2011-11-1 8:25',1 union all
select '2011-11-1 17:33',1 union all
select '2011-11-1 8:31',2 union all
select '2011-11-1 17:42',2 union all
select '2011-11-1 8:26',4 union all
select '2011-11-1 19:50',4 union all
select '2011-11-1 8:25',5 union all
select '2011-11-1 17:32',5 union all
select '2011-11-1 8:26',7 union all
select '2011-11-1 17:32',7 union all
select '2011-11-2 13:57',3 union all
select '2011-11-2 17:48',3 union all
select '2011-11-2 8:26',4 union all
select '2011-11-2 18:51',4 union all
select '2011-11-2 8:26',5 union all
select '2011-11-2 17:30',5 union all
select '2011-11-2 8:27',6 union all
select '2011-11-2 18:34',6 union all
select '2011-11-2 8:26',7 union all
select '2011-11-2 19:49',7 union all
select '2011-11-2 8:28',8 union all
select '2011-11-3 8:27',3 union all
select '2011-11-3 18:47',3 union all
select '2011-11-3 8:10',4 union all
select '2011-11-3 17:36',4 union all
select '2011-11-3 8:28',7 union all
select '2011-11-3 17:38',7 union all
select '2011-11-3 8:24',8 union all
select '2011-11-3 17:31',8
--------------开始查询--------------------------
select
distinct a.checktime,a.name,b.mintime,b.maxtime
from
(
select * from (select CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME from checkinout)a cross join (select NAME,USERID from userinfo)b
)a
left join
(
select USERID,CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME,Min(CHECKTIME) as mintime,MAX(CHECKTIME) as maxtime from checkinout group by USERID,CONVERT(varchar(10),CHECKTIME,120)
)b
on
a.USERID=b.USERID
and
a.CHECKTIME=b.CHECKTIME
----------------结果----------------------------
/*
(
(10 行受影响)(29 行受影响)
checktime name mintime maxtime
---------- ---- ----------------------- -----------------------
2011-11-01 黄七 2011-11-01 08:26:00.000 2011-11-01 17:32:00.000
2011-11-01 李三 NULL NULL
2011-11-01 林八 NULL NULL
2011-11-01 蔺十 NULL NULL
2011-11-01 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.000
2011-11-01 王五 2011-11-01 08:25:00.000 2011-11-01 17:32:00.000
2011-11-01 徐四 2011-11-01 08:26:00.000 2011-11-01 19:50:00.000
2011-11-01 易九 NULL NULL
2011-11-01 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.000
2011-11-01 邹六 NULL NULL
2011-11-02 黄七 2011-11-02 08:26:00.000 2011-11-02 19:49:00.000
2011-11-02 李三 2011-11-02 13:57:00.000 2011-11-02 17:48:00.000
2011-11-02 林八 2011-11-02 08:28:00.000 2011-11-02 08:28:00.000
2011-11-02 蔺十 NULL NULL
2011-11-02 刘一 NULL NULL
2011-11-02 王五 2011-11-02 08:26:00.000 2011-11-02 17:30:00.000
2011-11-02 徐四 2011-11-02 08:26:00.000 2011-11-02 18:51:00.000
2011-11-02 易九 NULL NULL
2011-11-02 周二 NULL NULL
2011-11-02 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.000
2011-11-03 黄七 2011-11-03 08:28:00.000 2011-11-03 17:38:00.000
2011-11-03 李三 2011-11-03 08:27:00.000 2011-11-03 18:47:00.000
2011-11-03 林八 2011-11-03 08:24:00.000 2011-11-03 17:31:00.000
2011-11-03 蔺十 NULL NULL
2011-11-03 刘一 NULL NULL
2011-11-03 王五 NULL NULL
2011-11-03 徐四 2011-11-03 08:10:00.000 2011-11-03 17:36:00.000
2011-11-03 易九 NULL NULL
2011-11-03 周二 NULL NULL
2011-11-03 邹六 NULL NULL(30 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-07 15:17:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[userinfo]
if object_id('[userinfo]') is not null drop table [userinfo]
go
create table [userinfo]([NAME] varchar(4),[USERID] int)
insert [userinfo]
select '刘一',1 union all
select '周二',2 union all
select '李三',3 union all
select '徐四',4 union all
select '王五',5 union all
select '邹六',6 union all
select '黄七',7 union all
select '林八',8 union all
select '易九',9 union all
select '蔺十',10
--> 测试数据:[checkinout]
if object_id('[checkinout]') is not null drop table [checkinout]
go
create table [checkinout]([CHECKTIME] datetime,[USERID] int)
insert [checkinout]
select '2011-11-1 8:25',1 union all
select '2011-11-1 17:33',1 union all
select '2011-11-1 8:31',2 union all
select '2011-11-1 17:42',2 union all
select '2011-11-1 8:26',4 union all
select '2011-11-1 19:50',4 union all
select '2011-11-1 8:25',5 union all
select '2011-11-1 17:32',5 union all
select '2011-11-1 8:26',7 union all
select '2011-11-1 17:32',7 union all
select '2011-11-2 13:57',3 union all
select '2011-11-2 17:48',3 union all
select '2011-11-2 8:26',4 union all
select '2011-11-2 18:51',4 union all
select '2011-11-2 8:26',5 union all
select '2011-11-2 17:30',5 union all
select '2011-11-2 8:27',6 union all
select '2011-11-2 18:34',6 union all
select '2011-11-2 8:26',7 union all
select '2011-11-2 19:49',7 union all
select '2011-11-2 8:28',8 union all
select '2011-11-3 8:27',3 union all
select '2011-11-3 18:47',3 union all
select '2011-11-3 8:10',4 union all
select '2011-11-3 17:36',4 union all
select '2011-11-3 8:28',7 union all
select '2011-11-3 17:38',7 union all
select '2011-11-3 8:24',8 union all
select '2011-11-3 17:31',8
--------------开始查询--------------------------
select
distinct a.checktime,a.name,b.mintime,b.maxtime
from
(
select * from (select CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME from checkinout)a cross join (select NAME,USERID from userinfo)b
)a
left join
(
select USERID,CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME,Min(convert(varchar(5),CHECKTIME,108)) as mintime,MAX(convert(varchar(5),CHECKTIME,108)) as maxtime from checkinout group by USERID,CONVERT(varchar(10),CHECKTIME,120)
)b
on
a.USERID=b.USERID
and
a.CHECKTIME=b.CHECKTIME
----------------结果----------------------------
/* (10 行受影响)(29 行受影响)
checktime name mintime maxtime
---------- ---- ------- -------
2011-11-01 黄七 08:26 17:32
2011-11-01 李三 NULL NULL
2011-11-01 林八 NULL NULL
2011-11-01 蔺十 NULL NULL
2011-11-01 刘一 08:25 17:33
2011-11-01 王五 08:25 17:32
2011-11-01 徐四 08:26 19:50
2011-11-01 易九 NULL NULL
2011-11-01 周二 08:31 17:42
2011-11-01 邹六 NULL NULL
2011-11-02 黄七 08:26 19:49
2011-11-02 李三 13:57 17:48
2011-11-02 林八 08:28 08:28
2011-11-02 蔺十 NULL NULL
2011-11-02 刘一 NULL NULL
2011-11-02 王五 08:26 17:30
2011-11-02 徐四 08:26 18:51
2011-11-02 易九 NULL NULL
2011-11-02 周二 NULL NULL
2011-11-02 邹六 08:27 18:34
2011-11-03 黄七 08:28 17:38
2011-11-03 李三 08:27 18:47
2011-11-03 林八 08:24 17:31
2011-11-03 蔺十 NULL NULL
2011-11-03 刘一 NULL NULL
2011-11-03 王五 NULL NULL
2011-11-03 徐四 08:10 17:36
2011-11-03 易九 NULL NULL
2011-11-03 周二 NULL NULL
2011-11-03 邹六 NULL NULL(30 行受影响)
*/
from (
select distinct convert(varchar(10),a.checktime,120)dt,b.userid,b.name from checkinout a,userinfo b
)a left join checkinout b on a.dt=convert(varchar(10),b.checktime,120) and a.userid=b.userid
group by a.dt,a.name
order by 1
/*
dt name maxtime mintime
---------- ---------- ------- -------
2011-11-01 黄七 17:32 08:26
2011-11-01 李三 NULL NULL
2011-11-01 林八 NULL NULL
2011-11-01 蔺十 NULL NULL
2011-11-01 刘一 17:33 08:25
2011-11-01 王五 17:32 08:25
2011-11-01 徐四 19:50 08:26
2011-11-01 易九 NULL NULL
2011-11-01 周二 17:42 08:31
2011-11-01 邹六 NULL NULL
2011-11-02 黄七 19:49 08:26
2011-11-02 李三 17:48 13:57
2011-11-02 林八 08:28 08:28
2011-11-02 蔺十 NULL NULL
2011-11-02 刘一 NULL NULL
2011-11-02 王五 17:30 08:26
2011-11-02 徐四 18:51 08:26
2011-11-02 易九 NULL NULL
2011-11-02 周二 NULL NULL
2011-11-02 邹六 18:34 08:27
2011-11-03 黄七 17:38 08:28
2011-11-03 李三 18:47 08:27
2011-11-03 林八 17:31 08:24
2011-11-03 蔺十 NULL NULL
2011-11-03 刘一 NULL NULL
2011-11-03 王五 NULL NULL
2011-11-03 徐四 17:36 08:10
2011-11-03 易九 NULL NULL
2011-11-03 周二 NULL NULL
2011-11-03 邹六 NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(30 行受影响)*/
--谨遵小F要求,前来优化
;with cte as(
select * from userinfo u,(select distinct convert(varchar(10),checktime,120) mydate from checkinout) t
)
select a.mydate,a.userid,a.name,
mintime=convert(varchar(5),min(c.checktime),108),
mintime=convert(varchar(5),max(c.checktime),108)
from cte a left join checkinout c
on (a.userid=c.userid and a.mydate=convert(varchar(10),c.checktime,120))
group by a.mydate,a.userid,a.name
order by 1,2/*
mydate userid name mintime mintime
---------- ----------- ---- ------- -------
2011-11-01 1 刘一 08:25 17:33
2011-11-01 2 周二 08:31 17:42
2011-11-01 3 李三 NULL NULL
2011-11-01 4 徐四 08:26 19:50
2011-11-01 5 王五 08:25 17:32
2011-11-01 6 邹六 NULL NULL
2011-11-01 7 黄七 08:26 17:32
2011-11-01 8 林八 NULL NULL
2011-11-01 9 易九 NULL NULL
2011-11-01 10 蔺十 NULL NULL
2011-11-02 1 刘一 NULL NULL
2011-11-02 2 周二 NULL NULL
2011-11-02 3 李三 13:57 17:48
2011-11-02 4 徐四 08:26 18:51
2011-11-02 5 王五 08:26 17:30
2011-11-02 6 邹六 08:27 18:34
2011-11-02 7 黄七 08:26 19:49
2011-11-02 8 林八 08:28 08:28
2011-11-02 9 易九 NULL NULL
2011-11-02 10 蔺十 NULL NULL
2011-11-03 1 刘一 NULL NULL
2011-11-03 2 周二 NULL NULL
2011-11-03 3 李三 08:27 18:47
2011-11-03 4 徐四 08:10 17:36
2011-11-03 5 王五 NULL NULL
2011-11-03 6 邹六 NULL NULL
2011-11-03 7 黄七 08:28 17:38
2011-11-03 8 林八 08:24 17:31
2011-11-03 9 易九 NULL NULL
2011-11-03 10 蔺十 NULL NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(30 行受影响)
所以先cross join构造主表,再left join即可
create table userinfo(name char(8), USERID int)insert into userinfo
select '刘一', 1 union all
select '周二', 2 union all
select '李三', 3 union all
select '徐四', 4 union all
select '王五', 5 union all
select '邹六', 6 union all
select '黄七', 7 union all
select '林八', 8 union all
select '易九', 9 union all
select '蔺十', 10create table checkinout(CHECKTIME datetime, USERID int)insert into checkinout
select '2011-11-1 8:25', 1 union all
select '2011-11-1 17:33', 1 union all
select '2011-11-1 8:31', 2 union all
select '2011-11-1 17:42', 2 union all
select '2011-11-1 8:26', 4 union all
select '2011-11-1 19:50', 4 union all
select '2011-11-1 8:25', 5 union all
select '2011-11-1 17:32', 5 union all
select '2011-11-1 8:26', 7 union all
select '2011-11-1 17:32', 7 union all
select '2011-11-2 13:57', 3 union all
select '2011-11-2 17:48', 3 union all
select '2011-11-2 8:26', 4 union all
select '2011-11-2 18:51', 4 union all
select '2011-11-2 8:26', 5 union all
select '2011-11-2 17:30', 5 union all
select '2011-11-2 8:27', 6 union all
select '2011-11-2 18:34', 6 union all
select '2011-11-2 8:26', 7 union all
select '2011-11-2 19:49', 7 union all
select '2011-11-2 8:28', 8 union all
select '2011-11-3 8:27', 3 union all
select '2011-11-3 18:47', 3 union all
select '2011-11-3 8:10', 4 union all
select '2011-11-3 17:36', 4 union all
select '2011-11-3 8:28', 7 union all
select '2011-11-3 17:38', 7 union all
select '2011-11-3 8:24', 8 union all
select '2011-11-3 17:31', 8
select c.MYDATE,c.name,d.MINTIME,d.MAXTIME
from
(select *
from
(select distinct convert(varchar,CHECKTIME,23) 'MYDATE' from checkinout) a
cross join
(select * from userinfo) b) c
left join
(select convert(varchar,CHECKTIME,23) 'MYDATE',USERID,
left(convert(varchar,min(CHECKTIME),108),5) 'MINTIME',
left(convert(varchar,max(CHECKTIME),108),5) 'MAXTIME'
from checkinout
group by convert(varchar,CHECKTIME,23),USERID) d
on c.MYDATE=d.MYDATE and c.USERID=d.USERID
MYDATE name MINTIME MAXTIME
-------------- -------- ---------- ----------
2011-11-01 刘一 08:25 17:33
2011-11-01 周二 08:31 17:42
2011-11-01 李三 NULL NULL
2011-11-01 徐四 08:26 19:50
2011-11-01 王五 08:25 17:32
2011-11-01 邹六 NULL NULL
2011-11-01 黄七 08:26 17:32
2011-11-01 林八 NULL NULL
2011-11-01 易九 NULL NULL
2011-11-01 蔺十 NULL NULL
2011-11-02 刘一 NULL NULL
2011-11-02 周二 NULL NULL
2011-11-02 李三 13:57 17:48
2011-11-02 徐四 08:26 18:51
2011-11-02 王五 08:26 17:30
2011-11-02 邹六 08:27 18:34
2011-11-02 黄七 08:26 19:49
2011-11-02 林八 08:28 08:28
2011-11-02 易九 NULL NULL
2011-11-02 蔺十 NULL NULL
2011-11-03 刘一 NULL NULL
2011-11-03 周二 NULL NULL
2011-11-03 李三 08:27 18:47
2011-11-03 徐四 08:10 17:36
2011-11-03 王五 NULL NULL
2011-11-03 邹六 NULL NULL
2011-11-03 黄七 08:28 17:38
2011-11-03 林八 08:24 17:31
2011-11-03 易九 NULL NULL
2011-11-03 蔺十 NULL NULL(30 row(s) affected)
set @tyear=2011
set @tmonth=10
select distinct left(convert(varchar,c.checktime,120),10) as mydate,u.userid,u.name,(select substring(CONVERT(varchar, min(checktime), 120 ),12,5) from checkinout where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as mintime,(select substring(CONVERT(varchar, max(checktime), 120 ),12,5) from checkinout where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as maxtime
from checkinout as c,userinfo as u where year(c.checktime)=@tyear and month(c.checktime)=@tmonth order by u.userid,mydate
正解:
SQL:
一、
select a.dt,a.name,max(convert(varchar(5),b.checktime,108))maxtime,min(convert(varchar(5),b.checktime,108))mintime
from (
select distinct convert(varchar(10),a.checktime,120)dt,b.userid,b.name from checkinout a,userinfo b
)a left join checkinout b on a.dt=convert(varchar(10),b.checktime,120) and a.userid=b.userid
group by a.dt,a.name
order by a.dt ACCESS:
一、
select a.dt,a.name,format(min(b.checktime),"hh:mm") AS intime, format(max(b.checktime),"hh:mm") AS outtime
from (
select distinct format(a.checktime,"YYYY-MM-DD") AS dt,b.userid,b.name from checkinout a,userinfo b
)a left join checkinout b on a.dt=format(b.checktime,"YYYY-MM-DD") and a.userid=b.userid
group by a.dt,a.name
order by a.dt
这个很好很强大,查询速度快了很多,努力学习中,access还没研究出来,
辛苦了,下次一定给你高分,access竟然用不了cross join,access跟SQL还是有很多不一样