求一关于计算次数的sql

jyhczg 2008-11-07 06:00:28

求一关于计算次数的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

...全文

81 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

jimoshatan 2008-11-08

打赏
举报

上面的应该是最简单的了

liangCK 2008-11-07

打赏
举报

--> liangCK小梁 于2008-11-07

--> 生成测试数据: @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 行受影响)



*/

等不到来世 2008-11-07

打赏
举报

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' 



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)

*/

liangCK 2008-11-07

打赏
举报

--> liangCK小梁 于2008-11-07

--> 生成测试数据: @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 行受影响)

*/