例如一个表记录员工上午下午的考勤记录
A:UserID Date Status Status2
------------------------------------------
1 2009-6-1 正常 早退
2 2009-6-1 正常 <NULL>
2 2009-6-1 <NULL> 早退
由于在视图中,希望一个员工一天只有一条记录,所以需要合并用户“2”的记录,合并后的结果为:V:UserID Date Status Status2
------------------------------------------
1 2009-6-1 正常 早退
2 2009-6-1 正常 早退请问如何实现 非常感谢
A:UserID Date Status Status2
------------------------------------------
1 2009-6-1 正常 早退
2 2009-6-1 正常 <NULL>
2 2009-6-1 <NULL> 早退
由于在视图中,希望一个员工一天只有一条记录,所以需要合并用户“2”的记录,合并后的结果为:V:UserID Date Status Status2
------------------------------------------
1 2009-6-1 正常 早退
2 2009-6-1 正常 早退请问如何实现 非常感谢
UserID,
[Date],
max(Status) as Status,
max(Status2) as Status2
from
a
group by
UserID,
[Date]
from A
group by UserID,Date
insert into @A select 1,'2009-6-1','正常','早退'
insert into @A select 2,'2009-6-1','正常',NULL
insert into @A select 2,'2009-6-1',NULL ,'早退'select UserID,Date,Status=max(Status),Status2=max(Status2) from @A group by UserID,Date/*
UserID Date Status Status2
----------- ------------------------------------------------------ ---------- ----------
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退
*/
MAX()
GROUP BY
insert @t values(1,'2009-6-1','正常','早退')
insert @t values(2,'2009-6-1','正常',NULL)
insert @t values(2,'2009-6-1',NULL,'早退')select UserID,Date,
Status=(select top 1 Status from @t where Status is not null and userid=t.userid and date=t.date),
Status2=(select top 1 Status2 from @t where Status2 is not null and userid=t.userid and date=t.date)
from @t t
group by userid,date/*
UserID Date Status Status2
----------- ----------------------- ---------- ----------
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退
*/
有可能这种情况A: UserID Date Status Status2
------------------------------------------
1 2009-6-1 正常 早退
2 2009-6-1 正常 早退
2 2009-6-1 迟到 早退 .
所以必须特定的取第一条记录的Status和第二条记录的Status2为此可以在表里面加个id 以区别谁是第一条 谁是第二条
if object_id('[tt]')is not null drop tablett
create table tt (UserID int,Date datetime,Status1 varchar(10),Status2 varchar(10))
insert into tt values(1,'2009-6-1','正常','早退')
insert into tt values(2,'2009-6-1','正常',NULL)
insert into tt values(2,'2009-6-1',NULL,'早退')
insert into tt values(3,'2009-6-1','正常',NULL)
insert into tt values(3,'2009-6-1',NULL,NULL)select distinct UserID ,
Status1 = stuff(( select '+' +Status1 from tt where UserID = w.UserID for xml path('')),1,1,''),
Status2 = stuff(( select '+' +Status2 from tt where UserID = w.UserID for xml path('')),1,1,'')
from tt w
如果status为上午考勤记录的话, 应该只有一种结果呀, 如果中途刷卡的话, 会记录是迟到吗>>?
insert @t values(1,'2009-6-1','正常','早退')
insert @t values(2,'2009-6-1','正常','早退')
insert @t values(2,'2009-6-1','迟到','早退');with cte as
(
select *,px = row_number() over(partition by userid order by getdate()) from @t
)
select distinct UserID,
date,
status1 = (select status from cte where userId = a.userid and px = 1),
status2 = isnull((select status2 from cte where userId = a.userid and px = 2),a.status2)
from cte a
/*
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退
*/
declare @A table(UserID int,Date datetime,Status varchar(10),Status2 varchar(10))
insert into @A select 1,'2009-6-1','正常','早退'
insert into @A select 2,'2009-6-1','正常',NULL
insert into @A select 2,'2009-6-1',NULL ,'早退'--SELECT * FROM @A
SELECT T.USERID,T.Date,T.Status,T1.Status2 FROM
(SELECT UserID,Date,Status FROM @A WHERE Status IS NOT NULL) AS T
JOIN
(SELECT UserID,Date,Status2 FROM @A WHERE Status2 IS NOT NULL)AS T1
ON T.USERID=T1.USERID
/*
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)USERID Date Status Status2
----------- ------------------------------------------------------ ---------- ----------
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退(所影响的行数为 2 行)*/没用函数哦!!呵呵
,Status=max(case rn when 1 then Status end)
,Status2=isnull(max(case when rn>1 then Status2 end),max(Status2))
from (select *,rn=row_number() over(order by getdate()) from A) t
group by UserID,Date
select UserID,Date
,Status=max(case rn when 1 then Status end)
,Status2=isnull(max(case when rn>1 then Status2 end),max(Status2))
from (select *,rn=row_number() over(partition by UserID,Date order by getdate()) from A) t
group by UserID,Date
declare @t table(userid int,[date] datetime,Status varchar(50),Status2 varchar(50))
insert @t
select 1,'2009-6-1','正常','早退' Union all
select 2,'2009-6-1','正常',null union all
select 2,'2009-6-1',null,'早退'select distinct a.userid,a.date,a.Status,b.Status2 from @t a inner join @t b on a.userid=b.userid where (a.Status is not null) and (b.Status2 is not null)
row_number() 好像要报错啊
go
create table [A]([UserID] int,[Date] datetime,[Status] varchar(4),[Status2] varchar(4))
insert [A]
select 1,'2009-6-1','正常','早退' union all
select 2,'2009-6-1','正常','早退' union all
select 2,'2009-6-1','迟到','早退'
go
--select * from [A] select id=identity(int,1,1),* into # from A select userid,date
,status=(select top 1 status from # where userid=t.userid and date=t.date order by id)
,status2=(select top 1 status2 from # where userid=t.userid and date=t.date order by id desc)
from # t
group by userid,date
/*
userid date status status2
----------- ----------------------- ------ -------
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退(2 行受影响)
*/
drop table #
insert into @A(UserID,Date,Status,Status2) select 1,'2009-6-1','正常','早退'
insert into @A(UserID,Date,Status,Status2) select 2,'2009-6-1','正常',NULL
insert into @A(UserID,Date,Status,Status2) select 2,'2009-6-1',NULL ,'早退'select distinct
t.UserID,
t.Date,
case when exists (select 1 from @A where UserID=t.UserID and Date=t.Date and ID!=t.ID)
then (select top 1 Status from @A n where n.UserID=t.UserID and n.Date=t.Date order by n.ID)
else Status
end as Status,
case when exists (select 1 from @A where UserID=t.UserID and Date=t.Date and ID!=t.ID)
then (select top 1 Status2 from @A n where n.UserID=t.UserID and n.Date=t.Date and n.ID>(select min(ID) from @A where UserID=t.UserID and Date=t.Date) order by n.ID)
else Status2
end as Status2
from
@A t/*
UserID Date Status Status2
----------- ------------------------------------------------------ ---------- ----------
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退
*/
insert into @A select 1,'2009-6-1','正常','早退'
insert into @A select 2,'2009-6-1','正常',NULL
insert into @A select 2,'2009-6-1',NULL ,'早退'select distinct userid,date,Status=(select max(status) from @a where userid=a.userid and date=a.date),Status2=(select max(status2) from @a where userid=a.userid and date=a.date) from @a auserid date Status Status2
----------- ----------------------- ---------- ----------
1 2009-06-01 00:00:00.000 正常 早退
2 2009-06-01 00:00:00.000 正常 早退
(2 行受影响)
insert into @A select 1,'2009-6-1','正常','早退'
insert into @A select 2,'2009-6-1','正常',NULL
insert into @A select 2,'2009-6-1',NULL ,'早退'select UserID,Date,Status=max(Status),Status2=max(Status2) from @A group by UserID,Date
奉上一例:SELECT * FROM Production.ProductInventory i
create table test_ecsdn
(id int,
Cname varchar(12),
Ccity Varchar(12)
)
--sp_help test_ecsdn
insert into test_ecsdn
select 1,'中和','NULL'
union all
select 1,NULL,'上海'
union all
select 2,'易宝','北京'
union all
select 3,'易宝','深圳'
union all
select 6,'易宝',null
union all
select 9,NULL,'皇软'exec p_getcolumns_inrow 'test_ecsdn',1
--表test_ecsdn的行
id Cname Ccityselect * from test_ecsdn
--查询全部记录的结果
id Cname Ccity
1 中和 NULL
1 NULL 上海
2 易宝 北京
3 易宝 深圳
6 易宝 NULL
6 NULL 皇软--合并NULLS的查询
select * from
(
select A.id,A.cname,ISNULL(A.Ccity,B.ccity)as 'Ccity' from test_ecsdn A
join test_ecsdn B
on A.id=B.id
--And A.id=1
AND B.ccity is NOT NULL
)C
WHERE C.cname is NOT null--合并NULLS后的结果
id Cname Ccity
1 中和 上海
2 易宝 北京
3 易宝 深圳
6 易宝 皇软