求一关于计算次数的sql有表产品编码,不良日期,求得最后两次的记录create table t(item_no varchar(30),dt datetime null)insert into t(item_no,dt)
select 'a001','2008-10-01'
union all
select 'a001','2008-10-02'
union all
select 'a001','2008-11-02'
union all
select 'a001','2008-11-04'
union all
select 'b001','2008-11-05'
union all
select 'b001','2008-11-06'
union all
select 'b001','2008-11-02'查询结果
a001,2008-11-02
a001,2008-11-04
b001,2008-11-05
b001,2008-11-06最好可以求出第多次(日期倒排)
a001,2008-10-01,4
a001,2008-10-02,3
a001,2008-11-02,2
a001,2008-11-04,1
b001,2008-11-02,3
b001,2008-11-05,2
b001,2008-11-06,1
select 'a001','2008-10-01'
union all
select 'a001','2008-10-02'
union all
select 'a001','2008-11-02'
union all
select 'a001','2008-11-04'
union all
select 'b001','2008-11-05'
union all
select 'b001','2008-11-06'
union all
select 'b001','2008-11-02'查询结果
a001,2008-11-02
a001,2008-11-04
b001,2008-11-05
b001,2008-11-06最好可以求出第多次(日期倒排)
a001,2008-10-01,4
a001,2008-10-02,3
a001,2008-11-02,2
a001,2008-11-04,1
b001,2008-11-02,3
b001,2008-11-05,2
b001,2008-11-06,1
--> 生成测试数据: @T
DECLARE @T TABLE (item_no VARCHAR(4),dt DATETIME)
INSERT INTO @T
SELECT 'a001','2008-10-01 00:00:00.000' UNION ALL
SELECT 'a001','2008-10-02 00:00:00.000' UNION ALL
SELECT 'a001','2008-11-02 00:00:00.000' UNION ALL
SELECT 'a001','2008-11-04 00:00:00.000' UNION ALL
SELECT 'b001','2008-11-05 00:00:00.000' UNION ALL
SELECT 'b001','2008-11-06 00:00:00.000' UNION ALL
SELECT 'b001','2008-11-02 00:00:00.000'--SQL查询如下:SELECT item_no,dt,
(SELECT COUNT(*)
FROM @T
WHERE t.item_no=item_no
AND t.dt<dt)+1 AS no
FROM @T AS t/*
item_no dt no
------- ----------------------- -----------
a001 2008-10-01 00:00:00.000 4
a001 2008-10-02 00:00:00.000 3
a001 2008-11-02 00:00:00.000 2
a001 2008-11-04 00:00:00.000 1
b001 2008-11-05 00:00:00.000 2
b001 2008-11-06 00:00:00.000 1
b001 2008-11-02 00:00:00.000 3(7 行受影响)
*/
select 'a001','2008-10-01'
union all
select 'a001','2008-10-02'
union all
select 'a001','2008-11-02'
union all
select 'a001','2008-11-04'
union all
select 'b001','2008-11-05'
union all
select 'b001','2008-11-06'
union all
select 'b001','2008-11-02' select *,rn=row_number() over(partition by item_no order by dt desc) from t
order by item_no,rn desc/*
item_no dt rn
------------------------------ ----------------------- --------------------
a001 2008-10-01 00:00:00.000 4
a001 2008-10-02 00:00:00.000 3
a001 2008-11-02 00:00:00.000 2
a001 2008-11-04 00:00:00.000 1
b001 2008-11-02 00:00:00.000 3
b001 2008-11-05 00:00:00.000 2
b001 2008-11-06 00:00:00.000 1(7 row(s) affected)
*/
--> 生成测试数据: @T
DECLARE @T TABLE (item_no VARCHAR(4),dt DATETIME)
INSERT INTO @T
SELECT 'a001','2008-10-01 00:00:00.000' UNION ALL
SELECT 'a001','2008-10-02 00:00:00.000' UNION ALL
SELECT 'a001','2008-11-02 00:00:00.000' UNION ALL
SELECT 'a001','2008-11-04 00:00:00.000' UNION ALL
SELECT 'b001','2008-11-05 00:00:00.000' UNION ALL
SELECT 'b001','2008-11-06 00:00:00.000' UNION ALL
SELECT 'b001','2008-11-02 00:00:00.000'--SQL查询如下:SELECT item_no,dt,
ROW_NUMBER()
OVER(PARTITION BY item_no
ORDER BY dt DESC) AS no
FROM @T
ORDER BY item_no,dt/*
item_no dt no
------- ----------------------- --------------------
a001 2008-10-01 00:00:00.000 4
a001 2008-10-02 00:00:00.000 3
a001 2008-11-02 00:00:00.000 2
a001 2008-11-04 00:00:00.000 1
b001 2008-11-02 00:00:00.000 3
b001 2008-11-05 00:00:00.000 2
b001 2008-11-06 00:00:00.000 1(7 行受影响)*/