62,046
社区成员
发帖
与我相关
我的任务
分享
--并且将tb2表中最早的任务查询出来
select t1.id, t1.name, t2.riqi, t2.renwu from @t1 t1, @t2 t2
where t1.id=t2.id and riqi in (select min(riqi) from @t2 group by id)
select tb1.id,tb1.name,min(tb2.riqi),tb2.renwu from tb1 join tb2 on tb1.id=tb2.id
select t1.id, t1.name, t2.riqi, t2.renwu from @t1 t1, @t2 t2
where t1.id=t2.id and datepart(day, cast(riqi as smalldatetime))=1
/*有两张表
tb1 id name
01 张三
02 李四
tb2 id riqi renwu
01 2010.01.01 aaa
01 2010.02.02 bbb
02 2010.03.01 ccc
02 2010.04.02 ddd
我想得到下面的结果
01 张三 2010.01.01 aaa
02 李四 2010.03.01 ccc
*/
--声明:我不知道表结构,所以都定义成varchar了,见谅
declare @t1 table (id varchar(2), name varchar(10))
declare @t2 table(id varchar(2), riqi varchar(10), renwu varchar(10))
insert into @t1 select '01', '张三'
union select '02', '李四'
insert into @t2 select '01', '2010.01.01', 'aaa'
union select '01', '2010.02.02', 'bbb'
union select '02', '2010.03.01', 'ccc'
union select '02', '2010.04.02', 'ddd'
select * from @t1; select * from @t2
select t1.id, t1.name, t2.riqi, t2.renwu from @t1 as t1
left join (select * from @t2) as t2
on t1.id=t2.id
/* result:
id name riqi renwu
01 张三 2010.01.01 aaa
01 张三 2010.02.02 bbb
02 李四 2010.03.01 ccc
02 李四 2010.04.02 ddd
*/
--LZ要得到那两条记录,不知道这中间还有什么条件限制,我也是在瞎猜,下面代码试下,能得到这样的结果:
select t1.id, t1.name, t2.riqi, t2.renwu from @t1 as t1
left join (select * from @t2) as t2
on t1.id=t2.id
where datepart(day, cast(riqi as smalldatetime))=1
/* result:
id name riqi renwu
01 张三 2010.01.01 aaa
02 李四 2010.03.01 ccc
*/