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
...全文
121 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 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

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧