593
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([workshopid] int,[BeginData] Date,[PrincipalBalance] int)
Insert #T
select 1,'2016-10-14',100 union all
select 1,'2016-10-17',70 union all
select 2,'2016-10-14',100 union all
select 2,'2016-10-15',60 union ALL
select 2,'2016-11-15',60 union all
select 3,'2016-10-14',100
Go
--测试数据结束
SELECT CONVERT(NVARCHAR(6), #T.[BeginData], 112) AS [BeginMonth] ,
SUM([PrincipalBalance]) AS [PrincipalBalance]
FROM #T
JOIN ( SELECT [workshopid] ,
CONVERT(NVARCHAR(6), [BeginData], 112) AS [BeginMonth] ,
MAX([BeginData]) AS [BeginData]
FROM #T
GROUP BY [workshopid] ,
CONVERT(NVARCHAR(6), [BeginData], 112)
) t ON t.BeginData = #T.BeginData
AND t.workshopid = #T.workshopid
GROUP BY CONVERT(NVARCHAR(6), #T.[BeginData], 112)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([workshopid] int,[BeginData] Date,[PrincipalBalance] int)
Insert #T
select 1,'2016-10-14',100 union all
select 1,'2016-10-31',70 union all
select 2,'2016-10-15',100 union all
select 2,'2016-10-31',60 union ALL
select 2,'2016-11-30',60 union all
select 3,'2016-10-31',100
Go
--测试数据结束
SELECT #T.[BeginData] ,
SUM(#T.[PrincipalBalance]) AS allPrincipalBalance
FROM #T
JOIN ( SELECT CONVERT(NVARCHAR(6), [BeginData], 112) AS [BeginMonth] ,
MAX([BeginData]) AS [BeginData]
FROM #T
GROUP BY CONVERT(NVARCHAR(6), [BeginData], 112)
) t ON t.BeginData = #T.BeginData
GROUP BY #T.[BeginData]
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([workshopid] int,[BeginData] Date,[PrincipalBalance] int)
Insert #T
select 1,'2016-10-14',100 union all
select 1,'2016-10-17',70 union all
select 2,'2016-10-14',100 union all
select 2,'2016-10-15',60 union ALL
select 2,'2016-11-15',60 union all
select 3,'2016-10-14',100
Go
--测试数据结束
SELECT #T.[workshopid] ,
#T.[BeginData] ,
#T.[PrincipalBalance]
FROM #T
JOIN ( SELECT [workshopid] ,
CONVERT(NVARCHAR(6), [BeginData], 112) AS [BeginMonth] ,
MAX([BeginData]) AS [BeginData]
FROM #T
GROUP BY [workshopid] ,
CONVERT(NVARCHAR(6), [BeginData], 112)
) t ON t.BeginData = #T.BeginData
AND t.workshopid = #T.workshopid
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(workshopid INT,begindate DATETIME,PrincipalBalance NUMERIC(18,2))
Insert #T
select 1,'2017-1-1',100 UNION ALL
select 1,'2017-1-31',101 UNION ALL
select 1,'2017-2-7',102 UNION ALL
select 1,'2017-2-11',103 UNION ALL
select 2,'2017-1-1',104 UNION ALL
select 2,'2017-1-6',105
Go
--测试数据结束
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER(
PARTITION BY workshopid,
YEAR(begindate),
MONTH(begindate) ORDER BY t.begindate DESC
) AS nt
FROM #T AS t
) a
WHERE nt = 1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(workshopid INT,begindate DATETIME,PrincipalBalance NUMERIC(18,2))
Insert #T
select 1,'2017-1-1',100 UNION ALL
select 1,'2017-1-31',101 UNION ALL
select 1,'2017-2-7',102 UNION ALL
select 1,'2017-2-11',103 UNION ALL
select 2,'2017-1-1',104 UNION ALL
select 2,'2017-1-6',105
Go
--测试数据结束
SELECT YEAR(a.begindate) AS 年,MONTH(a.begindate) AS 月,SUM(a.PrincipalBalance) AS 余额
FROM (
SELECT t.*,
ROW_NUMBER() OVER(
PARTITION BY workshopid,
YEAR(begindate),
MONTH(begindate) ORDER BY t.begindate DESC
) AS nt
FROM #T AS t
) a
WHERE nt = 1
GROUP BY YEAR(a.begindate),MONTH(a.begindate)
ORDER BY YEAR(a.begindate),MONTH(a.begindate)