如有以下的表qf_table
user_id fee_date fee
55 200801 3.00
55 200802 4.00
55 200803 5.00
66 200802 8.00
66 200803 9.00
66 200804 10.00
66 200805 11.00需要查询出的结果为:55 200803 5.00
66 200804 10.00
66 200505 11.00或者一次查询出如下结果更好:55 200803 5.00
66 200804 21.00请高手赐教SQL语句.
INSERT @TB
SELECT 55, '200801', 3.00 UNION ALL
SELECT 55, '200802', 4.00 UNION ALL
SELECT 55, '200803', 5.00 UNION ALL
SELECT 66, '200802', 8.00 UNION ALL
SELECT 66, '200803', 9.00 UNION ALL
SELECT 66, '200804', 10.00 UNION ALL
SELECT 66, '200805', 11.00SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB
SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE user_id<A.user_id) INTO #T FROM # AS ASELECT user_id,MIN(fee_date) AS fee_date,SUM(fee) AS fee
FROM (
SELECT user_id , fee_date, fee FROM #T WHERE SEQ>=3
) A
GROUP BY user_idDROP TABLE #
DROP TABLE #T
/*
user_id fee_date fee
----------- -------- ----------------------------------------
55 200803 5.00
66 200804 21.00
*/
DECLARE @TB TABLE(user_id INT, fee_date VARCHAR(6), fee DECIMAL(10,2))
INSERT @TB
SELECT 55, '200801', 3.00 UNION ALL
SELECT 55, '200802', 4.00 UNION ALL
SELECT 55, '200803', 5.00 UNION ALL
SELECT 66, '200802', 8.00 UNION ALL
SELECT 66, '200803', 9.00 UNION ALL
SELECT 66, '200804', 10.00 UNION ALL
SELECT 66, '200805', 11.00
select * from @TBselect rn=row_number() over (partition by user_id order by fee_date),* from @TBselect user_id , fee_date , fee from (
select rn=row_number() over (partition by user_id order by fee_date),* from @TB
)a where rn=3
(user_id varchar,
fee_date vhar(6),
fee DECIMAL(9,2)表中有90多万条记录.
执行时提示"找不到预期的from关键字",请帮忙再看看!
INSERT @TB
SELECT 55, '200801', 3.00 UNION ALL
SELECT 55, '200802', 4.00 UNION ALL
SELECT 55, '200803', 5.00 UNION ALL
SELECT 66, '200802', 8.00 UNION ALL
SELECT 66, '200803', 9.00 UNION ALL
SELECT 66, '200804', 10.00 UNION ALL
SELECT 66, '200805', 11.00
if object_id('tempdb..#Temp') is not null drop table #Temp; select user_id,fee_date,fee,row_number() over (partition by user_id order by fee desc)
as fee_order into #Temp
from @TB select user_id,fee_date,fee
from #Temp
where fee_order<2 if object_id('tempdb..#Temp') is not null drop table #Temp;