--构建表a
if object_id('a') is not null drop table a
go
create table a([date] datetime,[id] nvarchar(10),xm nvarchar(10),re nvarchar(20))
insert a
select '2010-05-17','1001','张三','迟到' union all
select '2010-05-18','1001','张三','迟到' union all
select '2010-05-19','1001','张三','迟到' union all
select '2010-05-17','1002','李四','迟到' union all
select '2010-05-18','1003','王五','迟到' union all
select '2010-05-19','1004','小明','迟到' union all
select '2010-05-17','1005','小红','迟到' union all
select '2010-05-18','1005','小红','迟到'
select * from a--构建表b
if object_id('b') is not null drop table b
go
create table b([id] nvarchar(10),[ip] nvarchar(10))
insert b
select '1001','456' union all
select '1002','457' union all
select '1003','458' union all
select '1004','459' union all
select '1005','460'
select * from b --查询结果1:
select a.[id],a.xm,ip,[date]
from a join b on a.[id]=b.[id]
where re='迟到' and date between dateadd(day,-3,getdate()) and getdate()
order by a.[id]--现想把 查询结果1 变为:
[id] xm ip 前天 昨天 今天
1001 张三 456 2010-05-17 2010-05-18 2010-05-19
1002 李四 457 2010-05-17 null null
1003 王五 458 null 2010-05-18 null
1004 小明 459 null null 2010-05-19
1005 小红 460 2010-05-17 2010-05-18 null请各位大侠帮忙,十分感谢
if object_id('a') is not null drop table a
go
create table a([date] datetime,[id] nvarchar(10),xm nvarchar(10),re nvarchar(20))
insert a
select '2010-05-17','1001','张三','迟到' union all
select '2010-05-18','1001','张三','迟到' union all
select '2010-05-19','1001','张三','迟到' union all
select '2010-05-17','1002','李四','迟到' union all
select '2010-05-18','1003','王五','迟到' union all
select '2010-05-19','1004','小明','迟到' union all
select '2010-05-17','1005','小红','迟到' union all
select '2010-05-18','1005','小红','迟到'
select * from a--构建表b
if object_id('b') is not null drop table b
go
create table b([id] nvarchar(10),[ip] nvarchar(10))
insert b
select '1001','456' union all
select '1002','457' union all
select '1003','458' union all
select '1004','459' union all
select '1005','460'
select * from b --查询结果1:
select a.[id],a.xm,ip,[date]
from a join b on a.[id]=b.[id]
where re='迟到' and date between dateadd(day,-3,getdate()) and getdate()
order by a.[id]--现想把 查询结果1 变为:
[id] xm ip 前天 昨天 今天
1001 张三 456 2010-05-17 2010-05-18 2010-05-19
1002 李四 457 2010-05-17 null null
1003 王五 458 null 2010-05-18 null
1004 小明 459 null null 2010-05-19
1005 小红 460 2010-05-17 2010-05-18 null请各位大侠帮忙,十分感谢
insert a
select '2010-05-17','1001','张三','迟到' union all
select '2010-05-18','1001','张三','迟到' union all
select '2010-05-19','1001','张三','迟到' union all
select '2010-05-17','1002','李四','迟到' union all
select '2010-05-18','1003','王五','迟到' union all
select '2010-05-19','1004','小明','迟到' union all
select '2010-05-17','1005','小红','迟到' union all
select '2010-05-18','1005','小红','迟到'
create table b([id] nvarchar(10),[ip] nvarchar(10))
insert b
select '1001','456' union all
select '1002','457' union all
select '1003','458' union all
select '1004','459' union all
select '1005','460'select b.id ,
xm = (select top 1 xm from a where a.id = b.id order by a.date),
b.ip,
前天 = (select top 1 date from a where a.id = b.id and datediff(day , date,getdate()) = 2),
昨天 = (select top 1 date from a where a.id = b.id and datediff(day , date,getdate()) = 1),
今天 = (select top 1 date from a where a.id = b.id and datediff(day , date,getdate()) = 0)
from bdrop table a , b/*
id xm ip 前天 昨天 今天
---------- ---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
1001 张三 456 2010-05-17 00:00:00.000 2010-05-18 00:00:00.000 2010-05-19 00:00:00.000
1002 李四 457 2010-05-17 00:00:00.000 NULL NULL
1003 王五 458 NULL 2010-05-18 00:00:00.000 NULL
1004 小明 459 NULL NULL 2010-05-19 00:00:00.000
1005 小红 460 2010-05-17 00:00:00.000 2010-05-18 00:00:00.000 NULL(所影响的行数为 5 行)*/
create proc [dbo].[IpTimeGetUser]
@parToday datetime
as
declare
@cols as nvarchar(max),
@sql as nvarchar(max),
@today datetime;set @today = @parToday;set @cols = STUFF(
(select N',' + QUOTENAME(times) as [text()]
from (select distinct CAST(DATEPART(HOUR,StatisticsTime) as nvarchar)
+':'+CAST(DATEPART(MINUTE,StatisticsTime) as nvarchar) as times
from UserCountDetail where StatisticsTime >= convert(nvarchar(10),@today,120)
and StatisticsTime < convert(nvarchar(10),@today+1,120) ) AS Y
order by times for XML PATH('')),1,1,N'');set @sql = N'select * from(select * from (select ip,CAST(DATEPART(HOUR,StatisticsTime) as nvarchar)
+ '':'' + CAST(DATEPART(MINUTE,StatisticsTime) as nvarchar) as times,UserCount
from UserCountDetail where StatisticsTime >= ''' + convert(nvarchar(10),@today,120) + N'''
and StatisticsTime < '''+ convert(nvarchar(10),@today+1,120) +''')
as sd pivot(sum(UserCount) for times in(' + @cols + N')) as pvt) as c ;'