我用SELECT COUNT(*) AS allsum, CONVERT(varchar(7), LogAddtime, 120) AS 月, ItemID
FROM dbo.CubeViewLogs
WHERE (CustomerID = '-1')
GROUP BY CONVERT(varchar(7), LogAddtime, 120), ItemID筛选出
allsum 月 ItemID
1 2006-03 -1
2 2007-01 -1
1 2007-02 -1
142 2007-03 -1
1 2007-03 58
6 2007-03 59我想根据ItemID从这些记录中取出 allsum最大的,最大所在的月,最小的,最小所在的月
如:
最小值 最小值月 最大值 最大值月 ItemID
0 2006-12 142 2007-03 -1
0 2007-02 1 2007-03 58
0 2007-02 6 2007-03 59最大值、最小值如果同值,取最近一个月的记录,最小值数据库中没有记录 就取0,最小值月份同样取为最近的一个月
FROM dbo.CubeViewLogs
WHERE (CustomerID = '-1')
GROUP BY CONVERT(varchar(7), LogAddtime, 120), ItemID筛选出
allsum 月 ItemID
1 2006-03 -1
2 2007-01 -1
1 2007-02 -1
142 2007-03 -1
1 2007-03 58
6 2007-03 59我想根据ItemID从这些记录中取出 allsum最大的,最大所在的月,最小的,最小所在的月
如:
最小值 最小值月 最大值 最大值月 ItemID
0 2006-12 142 2007-03 -1
0 2007-02 1 2007-03 58
0 2007-02 6 2007-03 59最大值、最小值如果同值,取最近一个月的记录,最小值数据库中没有记录 就取0,最小值月份同样取为最近的一个月
麻烦贴下sql语句,我始终连接不出来想要的结果
insert into @t select 1,'2006-03',-1
union all select 2,'2007-01',-1
union all select 1,'2007-02',-1
union all select 142,'2007-03',-1
union all select 1,'2007-03',58
union all select 6,'2007-03',59select ItemID, '最小值'=isnull(min(allsum),0),月 as 最小值月,'最大值'=isnull(max(allsum),0),月 as 最大值月
into #temp from @t group by ItemID,月 order by 最大值 desc
select min(最小值) as 最小值,max(最大值) as 最大值,itemID from #temp group by itemID
drop table #temp那去参考下吧!
FROM dbo.CubeViewLogs
WHERE (CustomerID = '-1')
GROUP BY CONVERT(varchar(7), LogAddtime, 120), ItemIDSelect
IsNull(B.allsum, Min(A.allsum)) As 最小值,
IsNull(B.月, (Select TOP 1 月 From #T Order By allsum, 月 Desc))As 最小值月,
Max(A.allsum) As 最大值,
(Select TOP 1 月 From #T Order By allsum Desc, 月 Desc) As 最大值月,
A.ItemID
From
#T A
Left Join
(Select
0 As allsum,
Max(Convert(Varchar(7), DateAdd(mm, -1, A.月 + '-01'), 120)) As 月,
ItemID
From #T A
Where Not Exists(Select 月 From #T Where DateDiff(mm, 月+ '-01', A.月+ '-01') =1 And ItemID = A.ItemID)
And A.月 > '2006-01'
Group By A.ItemID
) B
On A.ItemID = B.ItemID
Group By
A.ItemID, B.allsum, B.月Drop Table #T
Create Table #T
(allsum Int,
月 Varchar(10),
ItemID Int)
Insert #T Select 1, '2006-03',-1
Union All Select 2, '2007-01',-1
Union All Select 1, '2007-02',-1
Union All Select 142, '2007-03',-1
Union All Select 1, '2007-03',58
Union All Select 6, '2007-03',59
GO
--測試
Select
IsNull(B.allsum, Min(A.allsum)) As 最小值,
IsNull(B.月, (Select TOP 1 月 From #T Order By allsum, 月 Desc))As 最小值月,
Max(A.allsum) As 最大值,
(Select TOP 1 月 From #T Order By allsum Desc, 月 Desc) As 最大值月,
A.ItemID
From
#T A
Left Join
(Select
0 As allsum,
Max(Convert(Varchar(7), DateAdd(mm, -1, A.月 + '-01'), 120)) As 月,
ItemID
From #T A
Where Not Exists(Select 月 From #T Where DateDiff(mm, 月+ '-01', A.月+ '-01') =1 And ItemID = A.ItemID)
And A.月 > '2006-01'
Group By A.ItemID
) B
On A.ItemID = B.ItemID
Group By
A.ItemID, B.allsum, B.月
GO
--刪除測試環境
Drop Table #T
--結果
/*
最小值 最小值月 最大值 最大值月 ItemID
0 2006-12 142 2007-03 -1
0 2007-02 1 2007-03 58
0 2007-02 6 2007-03 59
*/