日期:2014-05-18 浏览次数:20672 次
create table HJ (单别 CHAR(4),单号 CHAR(11),序号 CHAR(4),料号 CHAR(20),日期 CHAR(8),单价 NUMERIC(16,4) ) INSERT INTO HJ VALUES('A1','20120100001','0001','钢笔','20120101','100') INSERT INTO HJ VALUES('A1','20120100002','0001','铅笔','20120101','100') INSERT INTO HJ VALUES('A1','20120100003','0001','圆珠笔','20120101','100') INSERT INTO HJ VALUES('A1','20120200001','0001','钢笔','20120201','90') INSERT INTO HJ VALUES('A1','20120200002','0001','铅笔','20120201','96') INSERT INTO HJ VALUES('A1','20120200003','0001','圆珠笔','20120201','106') INSERT INTO HJ VALUES('A1','20120300001','0001','钢笔','20120301','91') INSERT INTO HJ VALUES('A1','20120300002','0001','铅笔','20120301','99') INSERT INTO HJ VALUES('A1','20120300003','0001','圆珠笔','20120301','101') SELECT * FROM HJ
create table HJ (单别 CHAR(4),单号 CHAR(11),序号 CHAR(4),料号 CHAR(20),日期 CHAR(8),单价 NUMERIC(16,4) ) INSERT INTO HJ VALUES('A1','20120100001','0001','钢笔','20120101','100') INSERT INTO HJ VALUES('A1','20120100002','0001','铅笔','20120101','100') INSERT INTO HJ VALUES('A1','20120100003','0001','圆珠笔','20120101','100') INSERT INTO HJ VALUES('A1','20120200001','0001','钢笔','20120201','90') INSERT INTO HJ VALUES('A1','20120200002','0001','铅笔','20120201','96') INSERT INTO HJ VALUES('A1','20120200003','0001','圆珠笔','20120201','106') INSERT INTO HJ VALUES('A1','20120300001','0001','钢笔','20120301','91') INSERT INTO HJ VALUES('A1','20120300002','0001','铅笔','20120301','99') INSERT INTO HJ VALUES('A1','20120300003','0001','圆珠笔','20120301','101') select 单别,单号,序号,料号 ,日期,单价 from( select px=ROW_NUMBER()over(partition by 料号 order by 日期 desc), * from HJ)t where px<=2 /* 单别 单号 序号 料号 日期 单价 A1 20120300001 0001 钢笔 20120301 91.0000 A1 20120200001 0001 钢笔 20120201 90.0000 A1 20120300002 0001 铅笔 20120301 99.0000 A1 20120200002 0001 铅笔 20120201 96.0000 A1 20120300003 0001 圆珠笔 20120301 101.0000 A1 20120200003 0001 圆珠笔 20120201 106.0000 */ row_number是可以的
------解决方案--------------------
[Quote=引用:]
楼主说的同日期多单问题,看看这样修改下如何?
select 单别,单号,序号,料号 ,日期,单价 from( select px=ROW_NUMBER()over(partition by 料号 order by 日期 desc), * from HJ)t [/Quote] ++ 楼主说的同日期多单问题,看看这样修改下如何? [code=SQL]select 单别,单号,序号,料号 ,日期,单价 from( select px=ROW_NUMBER()over(partition by 料号 order by 单号 desc), * from HJ)t where px<=2
------解决方案--------------------
用ROW_NUMBER的1楼写了,给你写一个用TOP的
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'HJ') BEGIN DROP TABLE HJ END GO create table HJ (单别 CHAR(4),单号 CHAR(11),序号 CHAR(4),料号 CHAR(20),日期 CHAR(8),单价 NUMERIC(16,4) ) INSERT INTO HJ VALUES('A1','20120100001','0001','钢笔','201201