在新表#temp1中,我加入3个字段?into這是生成表 加字段為 alter table #temp1 add 列 int
drop table #temp1Select child_id,date,max(end_date) as max_date into #temp1 From continue_keep group by child_id,date
我打错了,我举个例子说明一下 表continue_keep child_id date end_date 512 2004-3-5 2005-4-5 512 2005-3-5 2006-4-5 512 2006-3-5 2007-4-5想得到如下表#temp1 child_id date max_date 512 2006-3-5 2007-4-5Select child_id,date,max(end_date) as max_date into #temp1 From continue_keep group by child_id,date 得出的结果不对
Select child_id,max(date) as date,max(end_date) as max_date into #temp1 From continue_keep group by child_id
-- 这样就可以了 select child_id, max(date) as date,max(end_date) as max_date from continue_keep group by child
create table continue_keep(child_id int,date datetime,end_date datetime) go insert into continue_keep select 512,'2004-3-5','2005-4-5' insert into continue_keep select 512,'2005-3-5','2006-4-5' insert into continue_keep select 512,'2006-3-5','2007-4-5' go select * from continue_keepselect child_id,convert(char(10),date,120) as date,convert(char(10),end_date,120) as end_date into #temp1 from continue_keep a where not exists(select 1 from continue_keep t where a.child_id=t.child_id and a.end_date<t.end_date)select * from #temp1drop table #temp1 go drop table continue_keep go /* 想得到如下表#temp1 child_id date max_date 512 2006-3-5 2007-4-5Select child_id,date,max(end_date) as max_date into #temp1 From continue_keep group by child_id,date */ --结果 (1 行受影响)(1 行受影响)(1 行受影响) child_id date end_date ----------- ----------------------- ----------------------- 512 2004-03-05 00:00:00.000 2005-04-05 00:00:00.000 512 2005-03-05 00:00:00.000 2006-04-05 00:00:00.000 512 2006-03-05 00:00:00.000 2007-04-05 00:00:00.000(3 行受影响)(1 行受影响)child_id date end_date ----------- ---------- ---------- 512 2006-03-05 2007-04-05(1 行受影响)
加字段為
alter table #temp1 add 列 int
group by child_id,date
表continue_keep
child_id date end_date
512 2004-3-5 2005-4-5
512 2005-3-5 2006-4-5
512 2006-3-5 2007-4-5想得到如下表#temp1
child_id date max_date
512 2006-3-5 2007-4-5Select child_id,date,max(end_date) as max_date into #temp1 From continue_keep
group by child_id,date
得出的结果不对
group by child_id
-- 这样就可以了
select child_id, max(date) as date,max(end_date) as max_date
from continue_keep group by child
create table continue_keep(child_id int,date datetime,end_date datetime)
go
insert into continue_keep select 512,'2004-3-5','2005-4-5'
insert into continue_keep select 512,'2005-3-5','2006-4-5'
insert into continue_keep select 512,'2006-3-5','2007-4-5'
go
select * from continue_keepselect child_id,convert(char(10),date,120) as date,convert(char(10),end_date,120) as end_date into #temp1
from continue_keep a
where not exists(select 1 from continue_keep t where a.child_id=t.child_id and a.end_date<t.end_date)select * from #temp1drop table #temp1
go
drop table continue_keep
go
/*
想得到如下表#temp1
child_id date max_date
512 2006-3-5 2007-4-5Select child_id,date,max(end_date) as max_date into #temp1 From continue_keep
group by child_id,date */
--结果
(1 行受影响)(1 行受影响)(1 行受影响)
child_id date end_date
----------- ----------------------- -----------------------
512 2004-03-05 00:00:00.000 2005-04-05 00:00:00.000
512 2005-03-05 00:00:00.000 2006-04-05 00:00:00.000
512 2006-03-05 00:00:00.000 2007-04-05 00:00:00.000(3 行受影响)(1 行受影响)child_id date end_date
----------- ---------- ----------
512 2006-03-05 2007-04-05(1 行受影响)