数据库语法，高手进来
我做的是考勤系统。
表关系如下：
a表:人员信息，主要字段有ID，name（姓名），cardid（卡号）
b表：cardid（卡号），times（刷卡记录）
表里的数据大致如下：
a:
1，张三，kd1
2，李四，kd2
3，王五，kd3
b：
kd1，2012/10/23 08:30……
kd1，2012/10/23 16:30……
kd2，2012/10/23 08:30……
kd2，2012/10/23 16:30……
…………
描述一下，每个人都有张卡，每个人每天都有可能不只刷一两次卡。现在我们考勤统计，比如说10月份张三的考勤记录。
1到31号都要，当天没有刷卡记录就显示缺勤。
每一列代表一天，不管你刷了多少次卡，只显示最早和最晚。
好了就这样，我写的有点多，对高手来说可能很简单，希望各位高手不惜赐教。先谢过！！
[最优解释]
;WITH a(ID, Name, cardid) AS
(
select 1, '张三', 'kd1' union all
select 1, '李四', 'kd2' union all
select 1, '王五', 'kd3'
),
b(cardid, refreshtime) AS --卡号，刷卡时间
(
select 'kd1', '2012/10/23 08:30' union all
select 'kd1', '2012/10/23 16:30' union all
select 'kd2', '2012/10/23 08:30' union all
select 'kd2', '2012/10/23 16:30' union all
select 'kd2', '2012/10/23 17:00'
)--准备表a和表b数据
---查询
select distinct
a.ID,
a.Name,
(select MIN(b.refreshtime) from b where b.cardid = a.cardid) '最早刷卡时间',
(select MAX(b.refreshtime) from b where b.cardid = a.cardid) '最晚刷卡时间'
from a
ID Name 最早刷卡时间 最晚刷卡时间
----------- ---- ------------------ ----------------
1 张三 2012/10/23 08:30 2012/10/23 16:30
1 李四 2012/10/23 08:30 2012/10/23 17:00
1 王五 NULL NULL
(3 row(s) affected)
use Tempdb
go
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
create table #1 (cardid varchar(4),times datetime)
insert into #1
select 'kd1','2012-10-23 08:00:00' union all
select 'kd1','2012-10-23 18:00:00' union all
select 'kd2','2012-10-23 07:00:00' union all
select 'kd2','2012-10-23 19:00:00' union all
select 'kd1','2012-10-24 08:00:00' union all
select 'kd1','2012-10-24 18:00:00'
GO
if not object_id(N'Tempdb..#2') is null
drop table #2
Go
create table #2 (name varchar(8),cardid varchar(4))
insert into #2
select '张三','kd1' union all
select '李四','kd2' union all
select '王五','kd3'
GO
----------------------------
declare @s varchar(8000)
set @s=''
Select
@s=@s+','+quotename(convert(varchar(10),[Times],120))+'='
+'isnull('
+'convert(varchar(8),min(case when convert(varchar(10),[Times],120)='+quotename(convert(varchar(10),[Times],120),'''')+' then [Times] else null end),108)'
+'+''-''+'
+'convert(varchar(8),max(case when convert(varchar(10),[Times],120)='+quotename(convert(varchar(10),[Times],120),'''')+' then [Times] else null end),108)'
+',''缺勤'')'
from (select distinct convert(varchar(10),[Times],120)[times] from #1 ) a group by[Times]
exec('select [name]'+@s+' from #1 right join #2 on #1.cardid=#2.cardid group by [name]')
/*---------------------------
name2012-10-23 2012-10-24
李四07:00:00-19:00:00缺勤
王五缺勤 缺勤
张三08:00:00-18:00:0008:00:00-18:00:00
*/----------------------------
---------kaoqinA---------------------------------------
drop table kaoqinA
create table kaoqinA(id int,name varchar(20),cardno varchar(20))
insert into kaoqinA select 1,'张三','kd1'
union all
select 2,'李四','kd2'
union all
select 3,'王五','kd3'
union all
select 4,'赵柳','kd4'
select * from kaoqinA
---------kaoqinB---------------------------------------
drop table kaoqinB
create table kaoqinB(cardno varchar(20),times datetime)
insert into kaoqinB select 'kd1','2012-11-07 08:30'
union all
select 'kd1','2012-11-07 18:30'
union all
select 'kd2','2012-11-07 08:30'
union all
select 'kd3','2012-11-07 08:30'
union all
select 'kd4','2012-11-07 08:30'
-------------------------------------------------
;with tmp as
(
select a.name,a.cardno,b.times from kaoqinA a join kaoqinB b on a.cardno = b.cardno
)
select name 姓名,cardno 卡号,MIN(times) 上班,
(case when MIN(times) = MAX(times) then null else MAX(times) end) 下班 from tmp group by cardno,name
--结果--
姓名 卡号 上班 下班
-------------------- -------------------- ----------------------- -----------------------
张三 kd1 2012-11-07 08:30:00.000 2012-11-07 18:30:00.000
李四 kd2 2012-11-07 08:30:00.000 NULL
王五 kd3 2012-11-07 08:30:00.000 NULL
赵柳 kd4 2012-11-07 08:30:00.000 NULL
(4 行受影响)
select a.cardid,a.name,CAST(times as DATE) as 日期,MIN(times) as 上班时间,MAX(times) as 下班时间
from A inner join B on a.cardid=b.cardid
where times between 'xxxx-xx-xx' and 'xxxx-xx-xx'
group by a.cardid,a.name,CAST(times as DATE)
;WITH a(ID, Name, cardid) AS
(
select 1, '张三', 'kd1' union all
select 1, '李四', 'kd2' union all
select 1, '王五', 'kd3'
),
b(cardid, times) AS --卡号，刷卡时间
(
select 'kd1', '2012/10/23 08:30' union all
select 'kd1', '2012/10/23 16:30' union all
select 'kd2', '2012/10/23 08:30' union all
select 'kd2', '2012/10/23 16:30' union all
select 'kd2', '2012/10/23 17:00'
)--准备表a和表b数据
,
c1 AS
(
SELECT b.cardid, MIN(times) 最早刷卡时间, MAX(times) 最晚刷卡时间, CAST(b.times AS DATE) 日期
FROM b
GROUP BY b.cardid, CAST(b.times AS DATE)
)
select a.Name, a.cardid, c1.日期, c1.最早刷卡时间, c1.最晚刷卡时间
from c1
LEFT JOIN a ON c1.cardid = a.cardid
WHERE c1.日期 BETWEEN '2012-10-01' AND '2012-10-31' ---查询整个月刷卡记录
Name cardid 日期 最早刷卡时间 最晚刷卡时间
---- ------ ---------- ---------------- ----------------
张三 kd1 2012-10-23 2012/10/23 08:30 2012/10/23 16:30
李四 kd2 2012-10-23 2012/10/23 08:30 2012/10/23 17:00
(2 row(s) affected)
;WITH a(ID, Name, cardid) AS
(
select 1, '张三', 'kd1' union all
select 2, '李四', 'kd2' union all
select 3, '王五', 'kd3'
),
b(cardid, times) AS --卡号，刷卡时间
(
select 'kd1', '2012/10/23 08:30' union all
select 'kd1', '2012/10/23 16:30' union all
select 'kd2', '2012/10/23 08:30' union all
select 'kd2', '2012/10/23 16:30' union all
select 'kd2', '2012/10/23 17:00'
) --准备表a和表b数据
,c_combination as
(
SELECT *
FROM
(
SELECT
a.Name,
cardid,
'2012/10/' + CAST(t.number AS VARCHAR(5)) 日期
FROM a, master..spt_values t
where t.number between 1 and 31 and t.type = 'P'
) tempA
OUTER APPLY
(
SELECT
b.times
FROM b
WHERE b.cardid = tempA.cardid and CAST(b.times AS DATE) = tempA.日期
) tempB
)
SELECT
Name,
日期,
ISNULL(MIN(times), '缺勤') 最早刷卡时间,
ISNULL(MAX(times), '缺勤') 最晚刷卡时间
FROM c_combination
group by Name, 日期
...................
王五 2012/10/22 缺勤 缺勤
张三 2012/10/22 缺勤 缺勤
李四 2012/10/23 2012/10/23 08:30 2012/10/23 17:00
王五 2012/10/23 缺勤 缺勤
张三 2012/10/23 2012/10/23 08:30 2012/10/23 16:30
李四 2012/10/24 缺勤 缺勤
王五 2012/10/24 缺勤 缺勤
............
Warning: Null value is eliminated by an aggregate or other SET operation.
(93 row(s) affected)
