如何用SQL 实现上面的逻辑

怎么用SQL 实现下面的逻辑？
有一张社保信息表 A
字段如下：

Card_ID         nvarchar(20) 省份证号
Person_Type     int 人员类型 (1在职   2 退休)
Fee_Date        DateTime  消费日期
Fee_Amount      Decimal   消费金额   

现有如下数据:

card_id       person_Type  Fee_Date  Fee_Amount
1001           1           2013-1-1  20
1002           1           2013-1-2  20
1003           2           2013-1-3  20
1004           2           2013-1-1  20
1001           2           2013-3-1  20
1001           1           2013-1-1  20
1004           1           2013-2-1  20

写出SQL语句统计出不同人员类型 消费的次数 ，结果如下:

person_Type  次数  Fee_Amount
1            6    100
2            1    20   

备注: 如果card_id 为1001【一个card_id 表示一个人】的人消费了三次【如上数据】，以最后一次消费时的persont_Type为最终的人员类型，比如：第一次为 1 ，第二次为2，第三次为1，那么card_id 为1001的人的最终消费类型为1，并且次数为 3次

[解决办法]

-- data
if object_id('tempdb.dbo.#') is not null drop table #
create table #(card_id int, person_Type int, Fee_Date date, Fee_Amount int)
insert into #
select 1001, 1, '2013-1-1', 20 union all
select 1002, 1, '2013-1-2', 20 union all
select 1003, 2, '2013-1-3', 20 union all
select 1004, 2, '2013-1-1', 20 union all
select 1001, 2, '2013-3-1', 20 union all
select 1001, 1, '2013-1-1', 20 union all
select 1004, 1, '2013-2-1', 20

-- query
;with data as
(
--select id=row_number()over(order by Fee_Date), * from # -- 按Fee_Date衡量1001最后1次的person_Type是2
select id=row_number()over(order by getdate()), * from #
), a as
(
select * from data t where not exists (select 1 from data where card_id=t.card_id and id>t.id)
)
select a.person_type, count(1)cn, sum(b.Fee_Amount)Fee_Amount from a, # b where a.card_id=b.card_id group by a.person_type

/*
person_type cn          Fee_Amount 
 
----------- ----------- -----------
1           6           120
2           1           20
*/

[解决办法]

declare @T table([card_id] int,[person_Type] int,[Fee_Date] datetime,[Fee_Amount] int)
insert @T
select 1001,1,'2013-1-1',20 union all
select 1002,1,'2013-1-2',20 union all
select 1003,2,'2013-1-3',20 union all
select 1004,2,'2013-1-1',20 union all
select 1001,2,'2013-3-1',20 union all
select 1001,1,'2013-1-1',20 union all
select 1004,1,'2013-2-1',20

;WITH  M1 AS ( SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) AS ID , * FROM @T),
       M2 AS ( SELECT   [card_id] ,[Fee_Amount] ,
                        ( SELECT TOP 1 person_Type FROM  M1
                          WHERE card_id = T.card_id ORDER BY  ID DESC
                        ) AS person_Type FROM  M1 t)
SELECT  person_Type ,COUNT(1) 次数 ,SUM(Fee_Amount) AS Fee_Amount FROM M2 GROUP BY person_Type
/*
person_Type 次数          Fee_Amount
----------- ----------- -----------
1           6           120
2           1           20
*/

查看更多 下一篇