求一SQL,在sql200下运行,数据 Id AnnounceDate(日期) InvestAmount(投资额)
4255 2001-4-7 0:00 45518100
4256 2001-4-7 0:00 52030300
6742 2001-4-7 0:00 49159900
6743 2001-4-7 0:00 48197000
755 2001-4-7 0:00 48434700
896 2001-4-7 0:00 53307000
1645 2003-3-4 0:00 190067000
2032 2003-3-4 0:00 167472200
6745 2003-3-4 0:00 49699600
6746 2003-3-4 0:00 103977900
6748 2003-3-4 0:00 49240200
6749 2003-3-4 0:00 40884500
6750 2003-3-4 0:00 183960500
4259 2003-3-4 0:00 193066600
4260 2003-3-4 0:00 95864500
4261 2003-3-4 0:00 199519000
4262 2003-3-4 0:00 0
4263 2003-3-4 0:00 0
4264 2004-1-15 0:00 20195000
4265 2004-1-15 0:00 68085800
4267 2004-1-15 0:00 14151600
4268 2004-1-15 0:00 29277000
4269 2004-1-15 0:00 24097200
4270 2004-1-15 0:00 49175500
4271 2004-1-15 0:00 44052500
4272 2004-1-15 0:00 51629500
4274 2004-1-15 0:00 105463000
6756 2004-1-15 0:00 52414000
6757 2004-1-15 0:00 50885400
6758 2004-1-15 0:00 120637000
6759 2004-1-15 0:00 59472200
6760 2004-1-15 0:00 51456500
2056 2004-1-15 0:00 6430200
2088 2004-1-15 0:00 51130000
1843 2004-1-15 0:00 60958500
1891 2005-1-29 0:00 126594500
1806 2005-1-29 0:00 48800000
297 2005-1-29 0:00 150592400
6763 2005-1-29 0:00 6193600
6765 2005-1-29 0:00 182392700
6768 2005-1-29 0:00 52095900
6769 2005-1-29 0:00 198648200
6774 2005-1-29 0:00 16237000
4275 2005-1-29 0:00 17790600
4276 2005-1-29 0:00 42407900
4277 2005-1-29 0:00 45916000
4278 2005-1-29 0:00 168363100
4280 2006-4-13 0:00 51189800
4284 2006-4-13 0:00 27855300
4286 2006-4-13 0:00 12500000
4289 2006-4-13 0:00 20000000
6776 2006-4-13 0:00 25000000
6777 2006-4-13 0:00 15000000
6779 2006-4-13 0:00 18860000
6780 2006-4-13 0:00 48800000
6781 2006-4-13 0:00 25000000
1538 2006-4-13 0:00 11248500
6782 2007-4-13 0:00 0 想要的结果 季度 总投资额 投资记录数 投资额不为0的记录数 平均投资额
2001Q2 2.9664 6 6 0.4944
2003Q1 12.7375 12 10 1.2737
2004Q1 8.5951 17 17 0.5055
2005Q1 10.5603 12 12 0.88
2006Q2 2.5545 10 10 0.2554 我已经写出语句了,但是不知道是否还有比较好的效率高的语句,请高手们赐教!select Q1.*,Q2.InvestCount,Q1.InvestTotal/Q2.InvestCount from (SELECT Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate)) AS ItemName, Sum(I.InvestAmount)/100000000 AS InvestTotal,Count(I.Id) AS InvestCount
FROM Invest I GROUP BY Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate))
) Q1inner join (
SELECT Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate)) AS ItemName, Sum(I.InvestAmount)/100000000 AS InvestTotal,Count(I.Id) AS InvestCount
FROM Invest I
WHERE I.InvestAmount>0
GROUP BY Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate))
) Q2on Q1.ItemName=Q2.ItemNameorder by Q1.ItemName
4255 2001-4-7 0:00 45518100
4256 2001-4-7 0:00 52030300
6742 2001-4-7 0:00 49159900
6743 2001-4-7 0:00 48197000
755 2001-4-7 0:00 48434700
896 2001-4-7 0:00 53307000
1645 2003-3-4 0:00 190067000
2032 2003-3-4 0:00 167472200
6745 2003-3-4 0:00 49699600
6746 2003-3-4 0:00 103977900
6748 2003-3-4 0:00 49240200
6749 2003-3-4 0:00 40884500
6750 2003-3-4 0:00 183960500
4259 2003-3-4 0:00 193066600
4260 2003-3-4 0:00 95864500
4261 2003-3-4 0:00 199519000
4262 2003-3-4 0:00 0
4263 2003-3-4 0:00 0
4264 2004-1-15 0:00 20195000
4265 2004-1-15 0:00 68085800
4267 2004-1-15 0:00 14151600
4268 2004-1-15 0:00 29277000
4269 2004-1-15 0:00 24097200
4270 2004-1-15 0:00 49175500
4271 2004-1-15 0:00 44052500
4272 2004-1-15 0:00 51629500
4274 2004-1-15 0:00 105463000
6756 2004-1-15 0:00 52414000
6757 2004-1-15 0:00 50885400
6758 2004-1-15 0:00 120637000
6759 2004-1-15 0:00 59472200
6760 2004-1-15 0:00 51456500
2056 2004-1-15 0:00 6430200
2088 2004-1-15 0:00 51130000
1843 2004-1-15 0:00 60958500
1891 2005-1-29 0:00 126594500
1806 2005-1-29 0:00 48800000
297 2005-1-29 0:00 150592400
6763 2005-1-29 0:00 6193600
6765 2005-1-29 0:00 182392700
6768 2005-1-29 0:00 52095900
6769 2005-1-29 0:00 198648200
6774 2005-1-29 0:00 16237000
4275 2005-1-29 0:00 17790600
4276 2005-1-29 0:00 42407900
4277 2005-1-29 0:00 45916000
4278 2005-1-29 0:00 168363100
4280 2006-4-13 0:00 51189800
4284 2006-4-13 0:00 27855300
4286 2006-4-13 0:00 12500000
4289 2006-4-13 0:00 20000000
6776 2006-4-13 0:00 25000000
6777 2006-4-13 0:00 15000000
6779 2006-4-13 0:00 18860000
6780 2006-4-13 0:00 48800000
6781 2006-4-13 0:00 25000000
1538 2006-4-13 0:00 11248500
6782 2007-4-13 0:00 0 想要的结果 季度 总投资额 投资记录数 投资额不为0的记录数 平均投资额
2001Q2 2.9664 6 6 0.4944
2003Q1 12.7375 12 10 1.2737
2004Q1 8.5951 17 17 0.5055
2005Q1 10.5603 12 12 0.88
2006Q2 2.5545 10 10 0.2554 我已经写出语句了,但是不知道是否还有比较好的效率高的语句,请高手们赐教!select Q1.*,Q2.InvestCount,Q1.InvestTotal/Q2.InvestCount from (SELECT Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate)) AS ItemName, Sum(I.InvestAmount)/100000000 AS InvestTotal,Count(I.Id) AS InvestCount
FROM Invest I GROUP BY Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate))
) Q1inner join (
SELECT Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate)) AS ItemName, Sum(I.InvestAmount)/100000000 AS InvestTotal,Count(I.Id) AS InvestCount
FROM Invest I
WHERE I.InvestAmount>0
GROUP BY Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate))
) Q2on Q1.ItemName=Q2.ItemNameorder by Q1.ItemName
有点
vvvvvvvvvvvvvvvv
FROM Invest
GROUP BY Convert(NVarChar(4),Year(I.AnnounceDate)) + 'Q' + Convert(NVarchar(1),DatePart(q,I.AnnounceDate))