sqlserver 问题求指导

jujian19881004 2015-12-07 03:19:54
proj_sid plan_version_sid registerdate
9D82766E-67BB-460D-B2AB-29C1350B9F60 1 2015-12-7
9D82766E-67BB-460D-B2AB-29C1350B9F60 2 2015-12-4
9D82766E-67BB-460D-B2AB-29C1350B9F60 3 2015-12-1
114A8783-BCA2-4A58-8814-C2A977BF9E6F 11 2015-08-04
114A8783-BCA2-4A58-8814-C2A977BF9E6F 21 2015-08-05

想用视图取出,project_sid 唯一 ,录入日期最小的记录,应该怎么取,求各位大神指导。
取出来的效果为:
proj_sid plan_version_sid registerdate
9D82766E-67BB-460D-B2AB-29C1350B9F60 3 2015-12-1
114A8783-BCA2-4A58-8814-C2A977BF9E6F 11 2015-08-04
...全文
90 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
道素 2015-12-09

with t as (
select '9D82766E-67BB-460D-B2AB-29C1350B9F60' AS project_sid,1 AS plan_version_sid,'2015-12-7' AS registerdate union 
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',2 ,'2015-12-4' union 
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',3  ,'2015-12-1' union 
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F',11 ,'2015-08-04' union 
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F',21 ,'2015-08-05'
)
SELECT * FROM (
   select t.*, ROW_NUMBER()OVER (PARTITION BY project_sid ORDER BY registerdate) as SeqNo from t
) tt WHERE tt.SeqNo=1
如果project_sid,registerdate相同的值存在多行记录,还需要其他字典排序,都放到ROW_NUMBER()OVER (PARTITION BY project_sid ORDER BY register date)中
回复
jienyuan 2015-12-07
select min(registerdate) from 你的表名 where proj_sid in (select proj_sid from (select proj_sid,count(proj_sid) from 你的表名 group by proj_sid having count(proj_sid) = 1) a)
回复
等不到来世 2015-12-07
select * from tb t
where not exists(select 1 from tb where project_sid=t.project_sid and registerdate<t.registerdate)
回复
with cte(project_sid,plan_version_sid,registerdate) as (
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',                       1                                 ,'2015-12-7' union 
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',                        2                                ,'2015-12-4' union 
select '9D82766E-67BB-460D-B2AB-29C1350B9F60',                       3                                ,'2015-12-1' union 
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F',                      	   11	                             ,'2015-08-04' union 
select '114A8783-BCA2-4A58-8814-C2A977BF9E6F',	                        21	                             ,'2015-08-05')
select cte.* from cte,(
select project_sid,min(registerdate)as registerdate from cte group by project_sid) a
where cte.project_sid=a.project_sid and cte.registerdate=a.registerdate
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-12-07 03:19
社区公告
暂无公告