22,209
社区成员
发帖
与我相关
我的任务
分享
select * from receive_list a where r_stock<>0 and not exists(select 1 from receive_list where r_stock<>0 and r_name=a.r_name and r_date<a.r_date)
select t.* from receive_list t inner join (select min(r_date) as r_date from receive_list where r_stock<>0 group by r_name) m on t.r_date=m.r_date
create table receive_list(r_id int,r_name nvarchar(10),r_moedl nvarchar(10),r_spec nvarchar(10),r_price int,r_Ftype nvarchar(10),r_type nvarchar(10),r_stock int,r_date datetime)
insert into receive_list select 1,'微星G31主板','G31','总线1333',380,'电源主件','主板',0,'2011-1-20 12:56:37' --这儿改为0
insert into receive_list select 2,'微星G31主板','G31','总线1333',360,'电脑主件','主板',10,'2011-1-22 14:50:17'
insert into receive_list select 3,'航嘉冷静王电源','V2.1','400W',260,'电脑主件','电源',10,'2011-1-19 16:22:53'
insert into receive_list select 4,'航嘉冷静王电源','V2.1','400W',280,'电脑主件','电源',0,'2011-1-17 11:32:43'
insert into receive_list select 5,'佳能激光打印机','3210','激光打印',700,'办公设备','打印机',2,'2011-1-10 10:25:33'
insert into receive_list select 6,'施乐针式打印机','2020','针式打印',1500,'办公设备','打印机',0,'2011-1-9 11:35:40'
go
select * from receive_list a where r_stock<>0 and not exists(select 1 from receive_list where r_stock<>0 and r_name=a.r_name and r_date<a.r_date)
go
drop table receive_list
/*
r_id r_name r_moedl r_spec r_price r_Ftype r_type r_stock r_date
----------- ---------- ---------- ---------- ----------- ---------- ---------- ----------- -----------------------
2 微星G31主板 G31 总线1333 360 电脑主件 主板 10 2011-01-22 14:50:17.000
3 航嘉冷静王电源 V2.1 400W 260 电脑主件 电源 10 2011-01-19 16:22:53.000
5 佳能激光打印机 3210 激光打印 700 办公设备 打印机 2 2011-01-10 10:25:33.000
(3 行受影响)
*/