原始库id time a 8:30 a 13:30 a 14:00 a 17:30 b 8:30 b 17:30 c 18:30 查询结果 id time1 time2 time3 time4a 8:30 13:30 14:00 17:30 b 8:30 17:30 c 18:30大侠们~~~救命啊~~~
DECLARE @t table(iid int,cardtime datetime) DECLARE @s varchar(8000) SET @s = 'SELECT iid,CONVERT(char(10),cardtime,120) AS cdate'INSERT INTO @t SELECT 1,'2004-9-1 23:54:12' UNION ALL SELECT 1,'2004-9-1 23:01:12' UNION ALL SELECT 1,'2004-9-1 21:01:12' UNION ALL SELECT 1,'2004-9-1 23:01:12' UNION ALL SELECT 1,'2004-9-1 23:54:12' UNION ALL SELECT 1,'2004-9-2 23:01:12' UNION ALL SELECT 1,'2004-9-3 21:01:12' UNION ALL SELECT 2,'2004-9-1 23:54:12' UNION ALL SELECT 3,'2004-9-1 23:01:12' UNION ALL SELECT 2,'2004-9-1 21:01:12' UNION ALL SELECT 1,'2004-9-3 23:01:11' SELECT * into #t1 FROM @tSELECT iid,CONVERT(char(10),cardtime,120) AS cdate,CONVERT(char(8),cardtime,108) AS ctime into #t FROM @tSELECT @s = @s+',[' +ctime+']=SUM(CASE CONVERT(char(8),cardtime,108) WHEN '''+ctime+''' THEN 1 ELSE 0 END)' FROM (SELECT ctime FROM #t GROUP BY ctime) AS aSET @s = @s + 'FROM #t1 GROUP BY iid,CONVERT(char(10),cardtime,120)'--UPDATE #t SET @s = @s + 'CASE WHEN '''+ctime+''' THEN 1 ELSE 0 END AS '''+ctime+''','EXEC (@s)DROP TABLE #t,#t1
我是一点都不懂,帮忙给解释一下啊,学习ing。
--生成测试数据 create table t(id varchar(10),time varchar(10)) insert into t select 'a','8:30' union all select 'a','13:30' union all select 'a','14:00' union all select 'a','17:30' union all select 'b','8:30' union all select 'b','17:30' union all select 'c','18:30' --查询 select [id],[time] ,xh=(select sum(1) from t where [id]=a.[id] and right('0'+[time],5)<=right('0'+a.[time],5)) into #t from t adeclare @s varchar(8000) set @s='' select @s=@s+',time'+cast(xh as varchar)+'=max(case xh when '''+cast(xh as varchar)+''' then [time] else null end)' from #t a join (select top 1 id,cs=count(id) from t group by id order by count(id) desc) b on a.id=b.idset @s=stuff(@s,1,1,'select [id],') set @s=@s+' from #t group by [id]'exec(@s)--删除对象 drop table #t,t--返回 id time1 time2 time3 time4 ---------- ---------- ---------- ---------- ---------- a 8:30 13:30 14:00 17:30 b 8:30 17:30 NULL NULL c 18:30 NULL NULL NULL
--每个人每天打卡得有次序号sn create table tab(iid int,cardtime datetime,sn int) INSERT INTO tab SELECT 1,'2004-9-1 23:54:12',1 UNION ALL SELECT 1,'2004-9-1 23:01:12',2 UNION ALL SELECT 1,'2004-9-1 21:01:12',3 UNION ALL SELECT 1,'2004-9-1 23:01:12',4 UNION ALL SELECT 1,'2004-9-1 23:54:12',5 UNION ALL SELECT 1,'2004-9-2 23:01:12',1 UNION ALL SELECT 1,'2004-9-3 21:01:12',1 UNION ALL SELECT 2,'2004-9-1 23:54:12',1 UNION ALL SELECT 3,'2004-9-1 23:01:12',1 UNION ALL SELECT 2,'2004-9-1 21:01:12',2 UNION ALL SELECT 1,'2004-9-3 23:01:11',2DECLARE @sql varchar(8000)set @sql='select iid,' select @sql=@sql+'time'+cast(sn as varchar(4))+'=max(case when sn='+cast(sn as varchar(4))+' then cardtime else null end),' from tab b,(select top 1 iid,cardtime from tab b where b.sn=(select max(dkcs) as dkcs from (select iid,convert(char(10),cardtime,21) as rq,count(*) as dkcs from tab group by iid,convert(char(10),cardtime,21)) a)) c where b.iid=c.iid and convert(char(10),b.cardtime,21)=convert(char(10),c.cardtime,21)set @sql=left(@sql,len(@sql)-1)+' from tab group by iid,convert(char(10),cardtime,21)'exec(@sql) drop table tab--运行结果 iid time1 time2 time3 time4 time5 ----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 1 2004-09-01 23:54:12.000 2004-09-01 23:01:12.000 2004-09-01 21:01:12.000 2004-09-01 23:01:12.000 2004-09-01 23:54:12.000 2 2004-09-01 23:54:12.000 2004-09-01 21:01:12.000 NULL NULL NULL 3 2004-09-01 23:01:12.000 NULL NULL NULL NULL 1 2004-09-02 23:01:12.000 NULL NULL NULL NULL 1 2004-09-03 21:01:12.000 2004-09-03 23:01:11.000 NULL NULL NULL警告: 聚合或其它 SET 操作消除了空值。
--生成测试数据 create table t(id varchar(10),time varchar(10)) insert into t select 'a','8:30' union all select 'a','13:30' union all select 'a','14:00' union all select 'a','17:30' union all select 'b','8:30' union all select 'b','17:30' union all select 'c','18:30' --查询 select [id],[time] ,xh=(select sum(1) from t where [id]=a.[id] and right('0'+[time],5)<=right('0'+a.[time],5)) into #t from t adeclare @s varchar(8000) set @s='' select @s=@s+',time'+cast(xh as varchar)+'=max(case xh when '''+cast(xh as varchar)+''' then [time] else null end)' from #t a join (select top 1 id,cs=count(id) from t group by id order by count(id) desc) b on a.id=b.idset @s=stuff(@s,1,1,'select [id],') set @s=@s+' from #t group by [id]'exec(@s)--删除对象 drop table #t,t--返回 id time1 time2 time3 time4 ---------- ---------- ---------- ---------- ---------- a 8:30 13:30 14:00 17:30 b 8:30 17:30 NULL NULL c 18:30 NULL NULL NULL还有个问题~~~上面这个东西,怎么把time变成datetime类型的阿???现在报错阿~~ 数据库里面存的是datetime类型的~~~服务器: 消息 241,级别 16,状态 1,行 2 从字符串转换为 datetime 时发生语法错误。
你开始没说清楚。mssql没有“13:00”这样的时间类型。 datetime和smalldatetime类型都是带日期和秒的,如“2003-1-1 13:00:00”。 你实际应该是这样的形式,是否? 另外,也不知道你要返回的数据是否带日期。以下返回以不带日期为准更改: ----------------------------------------------------------------select [emp_id],[sign_time]=convert(varchar(5),[sign_time],108) ,xh=(select sum(1) from TimeRecords where [emp_id]=a.[emp_id] and [sign_time]<=a.[sign_time]) into #t from TimeRecords a……
员工ID 员工姓名 日期 刷卡时间
123 xxx 2004/09/09 08:00,12:00,13:30,17:30,20:00,22:00
也挺好看的嘛
高手啊~~~你在哪里??
救救我吧~~~
a 8:30
a 13:30
a 14:00
a 17:30
b 8:30
b 17:30
c 18:30
查询结果
id time1 time2 time3 time4a 8:30 13:30 14:00 17:30
b 8:30 17:30
c 18:30大侠们~~~救命啊~~~
DECLARE @s varchar(8000)
SET @s = 'SELECT iid,CONVERT(char(10),cardtime,120) AS cdate'INSERT INTO @t
SELECT 1,'2004-9-1 23:54:12' UNION ALL
SELECT 1,'2004-9-1 23:01:12' UNION ALL
SELECT 1,'2004-9-1 21:01:12' UNION ALL
SELECT 1,'2004-9-1 23:01:12' UNION ALL
SELECT 1,'2004-9-1 23:54:12' UNION ALL
SELECT 1,'2004-9-2 23:01:12' UNION ALL
SELECT 1,'2004-9-3 21:01:12' UNION ALL
SELECT 2,'2004-9-1 23:54:12' UNION ALL
SELECT 3,'2004-9-1 23:01:12' UNION ALL
SELECT 2,'2004-9-1 21:01:12' UNION ALL
SELECT 1,'2004-9-3 23:01:11'
SELECT * into #t1 FROM @tSELECT iid,CONVERT(char(10),cardtime,120) AS cdate,CONVERT(char(8),cardtime,108) AS ctime into #t FROM @tSELECT @s = @s+',[' +ctime+']=SUM(CASE CONVERT(char(8),cardtime,108) WHEN '''+ctime+''' THEN 1 ELSE 0 END)'
FROM (SELECT ctime FROM #t GROUP BY ctime) AS aSET @s = @s + 'FROM #t1 GROUP BY iid,CONVERT(char(10),cardtime,120)'--UPDATE #t SET @s = @s + 'CASE WHEN '''+ctime+''' THEN 1 ELSE 0 END AS '''+ctime+''','EXEC (@s)DROP TABLE #t,#t1
create table t(id varchar(10),time varchar(10))
insert into t
select 'a','8:30'
union all select 'a','13:30'
union all select 'a','14:00'
union all select 'a','17:30'
union all select 'b','8:30'
union all select 'b','17:30'
union all select 'c','18:30'
--查询
select [id],[time]
,xh=(select sum(1) from t where [id]=a.[id] and right('0'+[time],5)<=right('0'+a.[time],5))
into #t
from t adeclare @s varchar(8000)
set @s=''
select @s=@s+',time'+cast(xh as varchar)+'=max(case xh when '''+cast(xh as varchar)+''' then [time] else null end)'
from #t a join
(select top 1 id,cs=count(id) from t group by id order by count(id) desc) b
on a.id=b.idset @s=stuff(@s,1,1,'select [id],')
set @s=@s+' from #t group by [id]'exec(@s)--删除对象
drop table #t,t--返回
id time1 time2 time3 time4
---------- ---------- ---------- ---------- ----------
a 8:30 13:30 14:00 17:30
b 8:30 17:30 NULL NULL
c 18:30 NULL NULL NULL
create table tab(iid int,cardtime datetime,sn int)
INSERT INTO tab
SELECT 1,'2004-9-1 23:54:12',1 UNION ALL
SELECT 1,'2004-9-1 23:01:12',2 UNION ALL
SELECT 1,'2004-9-1 21:01:12',3 UNION ALL
SELECT 1,'2004-9-1 23:01:12',4 UNION ALL
SELECT 1,'2004-9-1 23:54:12',5 UNION ALL
SELECT 1,'2004-9-2 23:01:12',1 UNION ALL
SELECT 1,'2004-9-3 21:01:12',1 UNION ALL
SELECT 2,'2004-9-1 23:54:12',1 UNION ALL
SELECT 3,'2004-9-1 23:01:12',1 UNION ALL
SELECT 2,'2004-9-1 21:01:12',2 UNION ALL
SELECT 1,'2004-9-3 23:01:11',2DECLARE @sql varchar(8000)set @sql='select iid,'
select @sql=@sql+'time'+cast(sn as varchar(4))+'=max(case when sn='+cast(sn as varchar(4))+' then cardtime else null end),'
from tab b,(select top 1 iid,cardtime from tab b
where b.sn=(select max(dkcs) as dkcs
from (select iid,convert(char(10),cardtime,21) as rq,count(*) as dkcs
from tab group by iid,convert(char(10),cardtime,21)) a)) c
where b.iid=c.iid and convert(char(10),b.cardtime,21)=convert(char(10),c.cardtime,21)set @sql=left(@sql,len(@sql)-1)+' from tab group by iid,convert(char(10),cardtime,21)'exec(@sql)
drop table tab--运行结果
iid time1 time2 time3 time4 time5
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
1 2004-09-01 23:54:12.000 2004-09-01 23:01:12.000 2004-09-01 21:01:12.000 2004-09-01 23:01:12.000 2004-09-01 23:54:12.000
2 2004-09-01 23:54:12.000 2004-09-01 21:01:12.000 NULL NULL NULL
3 2004-09-01 23:01:12.000 NULL NULL NULL NULL
1 2004-09-02 23:01:12.000 NULL NULL NULL NULL
1 2004-09-03 21:01:12.000 2004-09-03 23:01:11.000 NULL NULL NULL警告: 聚合或其它 SET 操作消除了空值。
create table t(id varchar(10),time varchar(10))
insert into t
select 'a','8:30'
union all select 'a','13:30'
union all select 'a','14:00'
union all select 'a','17:30'
union all select 'b','8:30'
union all select 'b','17:30'
union all select 'c','18:30'
--查询
select [id],[time]
,xh=(select sum(1) from t where [id]=a.[id] and right('0'+[time],5)<=right('0'+a.[time],5))
into #t
from t adeclare @s varchar(8000)
set @s=''
select @s=@s+',time'+cast(xh as varchar)+'=max(case xh when '''+cast(xh as varchar)+''' then [time] else null end)'
from #t a join
(select top 1 id,cs=count(id) from t group by id order by count(id) desc) b
on a.id=b.idset @s=stuff(@s,1,1,'select [id],')
set @s=@s+' from #t group by [id]'exec(@s)--删除对象
drop table #t,t--返回
id time1 time2 time3 time4
---------- ---------- ---------- ---------- ----------
a 8:30 13:30 14:00 17:30
b 8:30 17:30 NULL NULL
c 18:30 NULL NULL NULL还有个问题~~~上面这个东西,怎么把time变成datetime类型的阿???现在报错阿~~
数据库里面存的是datetime类型的~~~服务器: 消息 241,级别 16,状态 1,行 2
从字符串转换为 datetime 时发生语法错误。
datetime和smalldatetime类型都是带日期和秒的,如“2003-1-1 13:00:00”。
你实际应该是这样的形式,是否?
另外,也不知道你要返回的数据是否带日期。以下返回以不带日期为准更改:
----------------------------------------------------------------select [emp_id],[sign_time]=convert(varchar(5),[sign_time],108)
,xh=(select sum(1) from TimeRecords where [emp_id]=a.[emp_id] and [sign_time]<=a.[sign_time])
into #t
from TimeRecords a……