SQL按条件导出数据1

发布网友发布时间：2023-10-24 22:12

我来回答

共5个回答

热心网友时间：2024-11-29 19:19

select * from t1 b,

(select a.stb_id,max(a.change_date) as change_date from t1 a where a.after_status = 'active'

group by a.stb_id)c

where b.stb_id = c.stb_id and b.change_date = c.change_date;

sqlserver 2005试过可以

是不是我给的sql查到的记录多了

可能是std_id和change_date相同的记录不止一条

增加条件,如果std_id和change_date相同的记录不止一条则选id最大的

改为

select * from t1 b,

(select a.stb_id,max(a.change_date) as change_date,max(a.id) as id from t1 a where a.after_status = 'active'

group by a.stb_id)c

where b.stb_id = c.stb_id and b.change_date = c.change_date and b.id = c.id;

新需求

insert into 新表

select b.* from t1 b,

(select a.stb_id,max(a.change_date) as change_date,max(a.id) as id from t1 a where a.after_status = 'active'

group by a.stb_id)c

where b.stb_id = c.stb_id and b.change_date = c.change_date and b.id = c.id;

试试看,b.*这种写法应该支持，插入的是所有的时间最近的数据，如果要取部分，那你再加上新的过滤条件了

热心网友时间：2024-11-29 19:20

select * from (select t.*,rownum() over (partition by t.stb_id order by t.change_date desc) rn from t where t.after_status = 'active') a where a.rn=1

rownum() over (partition by t.stb_id order by t.change_date desc) 实现的是“在 stb_id重复的数据中我要取出距离当钱时间最近的一条”功能。

热心网友时间：2024-11-29 19:20

select std_id,max(change_date) from A group by std_id

热心网友时间：2024-11-29 19:21

select * from tbl order by stb_id, change_date desc

热心网友时间：2024-11-29 19:21

--select top 10 * from aaa where after_status = 'active' order by chang_date desc

select * from aaa a, (select after_status,max(chang_date) from aaa group by after_status) b,
where a.after_status=b.after_status and a.chang_date=b.chang_date