对表内数据间隔特定的长度求和

 

 

 数据来源于国家统计局，2010年人口普查数据，编写sql语句得到如图每5岁的汇总 

测试数据表结构如下： 

CREATE TABLE person ( [age] int, [personN] float(8), [personW] float(8), [personF] float(8), [percentM] float(8), [percentM1] float(8), [BL] float(8) )
INSERT INTO person
VALUES
( 0, 7461199, 6325235, 1.03, 0.56, 0.47, 117.96 ), 
( 1, 8574973, 7082982, 1.17, 0.64, 0.53, 121.06 ), 
( 2, 8507697, 7109678, 1.17, 0.64, 0.53, 119.66 ), 
( 3, 8272491, 6978314, 1.14, 0.62, 0.52, 118.55 ), 
( 4, 8246206, 6973835, 1.14, 0.62, 0.52, 118.24 ), 
( 5, 7988151, 6743986, 1.11, 0.6, 0.51, 118.45 ), 
( 6, 8034452, 6770018, 1.11, 0.6, 0.51, 118.68 ), 
( 7, 7292300, 6136861, 1.01, 0.55, 0.46, 118.83 ), 
( 8, 7423559, 6243397, 1.03, 0.56, 0.47, 118.9 ), 
( 9, 7726203, 6522622, 1.07, 0.58, 0.49, 118.45 ), 
( 10, 7830808, 6623549, 1.08, 0.59, 0.5, 118.23 ), 
( 11, 7522558, 6413156, 1.05, 0.56, 0.48, 117.3 ), 
( 12, 8288987, 7110572, 1.16, 0.62, 0.53, 116.57 ), 
( 13, 8161000, 7064032, 1.14, 0.61, 0.53, 115.53 ), 
( 14, 8463924, 7429876, 1.19, 0.64, 0.56, 113.92 ), 
( 15, 9524898, 8499586, 1.35, 0.71, 0.64, 112.06 ), 
( 16, 9795181, 8995340, 1.41, 0.73, 0.67, 108.89 ), 
( 17, 10760828, 10014541, 1.56, 0.81, 0.75, 107.45 ), 
( 18, 10744556, 10010718, 1.56, 0.81, 0.75, 107.33 ), 
( 19, 11079367, 10464099, 1.62, 0.83, 0.79, 105.88 ), 
( 20, 14201091, 13825863, 2.1, 1.07, 1.04, 102.71 ), 
( 21, 13357755, 13198894, 1.99, 1, 0.99, 101.2 ), 
( 22, 12281148, 12193044, 1.84, 0.92, 0.91, 100.72 ), 
( 23, 12876542, 12819413, 1.93, 0.97, 0.96, 100.45 ), 
( 24, 11292037, 11366731, 1.7, 0.85, 0.85, 99.34 ), 
( 25, 9969984, 9963699, 1.5, 0.75, 0.75, 100.06 ), 
( 26, 9879292, 9829885, 1.48, 0.74, 0.74, 100.5 ), 
( 27, 9801611, 9679225, 1.46, 0.74, 0.73, 101.26 ), 
( 28, 11271599, 11050548, 1.67, 0.85, 0.83, 102 ), 
( 29, 9914552, 9653457, 1.47, 0.74, 0.72, 102.7 ), 
( 30, 9604727, 9323642, 1.42, 0.72, 0.7, 103.01 ), 
( 31, 10141582, 9724876, 1.49, 0.76, 0.73, 104.28 ), 
( 32, 9909833, 9565041, 1.46, 0.74, 0.72, 103.6 ), 
( 33, 9289224, 8890254, 1.36, 0.7, 0.67, 104.49 ), 
( 34, 10576456, 10112568, 1.55, 0.79, 0.76, 104.59 ), 
( 35, 10817432, 10369084, 1.59, 0.81, 0.78, 104.32 ), 
( 36, 11690644, 11216336, 1.72, 0.88, 0.84, 104.23 ), 
( 37, 12283353, 11706855, 1.8, 0.92, 0.88, 104.92 ), 
( 38, 12662559, 12067901, 1.86, 0.95, 0.91, 104.93 ), 
( 39, 12937116, 12274679, 1.89, 0.97, 0.92, 105.4 ), 
( 40, 13993123, 13404096, 2.06, 1.05, 1.01, 104.39 ), 
( 41, 12723691, 12232606, 1.87, 0.95, 0.92, 104.01 ), 
( 42, 13782610, 13249932, 2.03, 1.03, 0.99, 104.02 ), 
( 43, 10856214, 10499534, 1.6, 0.81, 0.79, 103.4 ), 
( 44, 12253040, 11759118, 1.8, 0.92, 0.88, 104.2 ), 
( 45, 12252515, 11710059, 1.8, 0.92, 0.88, 104.63 ), 
( 46, 11867147, 11488631, 1.75, 0.89, 0.86, 103.29 ), 
( 47, 13803796, 13168361, 2.02, 1.04, 0.99, 104.83 ), 
( 48, 10224798, 9850286, 1.51, 0.77, 0.74, 103.8 ), 
( 49, 5628162, 5600798, 0.84, 0.42, 0.42, 100.49 ), 
( 50, 7205176, 6891832, 1.06, 0.54, 0.52, 104.55 ), 
( 51, 6624865, 6213967, 0.96, 0.5, 0.47, 106.61 ), 
( 52, 8570000, 8047709, 1.25, 0.64, 0.6, 106.49 ), 
( 53, 9422827, 8929153, 1.38, 0.71, 0.67, 105.53 ), 
( 54, 8540366, 8307276, 1.26, 0.64, 0.62, 102.81 ), 
( 55, 8973192, 8637336, 1.32, 0.67, 0.65, 103.89 ), 
( 56, 8981235, 8756892, 1.33, 0.67, 0.66, 102.56 ), 
( 57, 8099033, 7994855, 1.21, 0.61, 0.6, 101.3 ), 
( 58, 8153588, 8014345, 1.21, 0.61, 0.6, 101.74 ), 
( 59, 6875890, 6826108, 1.03, 0.52, 0.51, 100.73 ), 
( 60, 6917026, 6701178, 1.02, 0.52, 0.5, 103.22 ), 
( 61, 6690003, 6339122, 0.98, 0.5, 0.48, 105.54 ), 
( 62, 5719180, 5557673, 0.85, 0.43, 0.42, 102.91 ), 
( 63, 5492805, 5298828, 0.81, 0.41, 0.4, 103.66 ), 
( 64, 5015412, 4936055, 0.75, 0.38, 0.37, 101.61 ), 
( 65, 4564266, 4509145, 0.68, 0.34, 0.34, 101.22 ), 
( 66, 4391409, 4249556, 0.65, 0.33, 0.32, 103.34 ), 
( 67, 4003493, 3938648, 0.6, 0.3, 0.3, 101.65 ), 
( 68, 3904424, 3836444, 0.58, 0.29, 0.29, 101.77 ), 
( 69, 3884879, 3831018, 0.58, 0.29, 0.29, 101.41 ), 
( 70, 3724605, 3664807, 0.55, 0.28, 0.27, 101.63 ), 
( 71, 3116177, 3149541, 0.47, 0.23, 0.24, 98.94 ), 
( 72, 3449237, 3443988, 0.52, 0.26, 0.26, 100.15 ), 
( 73, 3149307, 3194562, 0.48, 0.24, 0.24, 98.58 ), 
( 74, 2964127, 3116046, 0.46, 0.22, 0.23, 95.12 ), 
( 75, 2690547, 2941930, 0.42, 0.2, 0.22, 91.46 ), 
( 76, 2454168, 2721332, 0.39, 0.18, 0.2, 90.18 ), 
( 77, 2420196, 2662187, 0.38, 0.18, 0.2, 90.91 ), 
( 78, 1983724, 2271134, 0.32, 0.15, 0.17, 87.35 ), 
( 79, 1730224, 1976691, 0.28, 0.13, 0.15, 87.53 ), 
( 80, 1716514, 2020745, 0.28, 0.13, 0.15, 84.94 ), 
( 81, 1257795, 1558898, 0.21, 0.09, 0.12, 80.68 ), 
( 82, 1212683, 1545235, 0.21, 0.09, 0.12, 78.48 ), 
( 83, 964710, 1272428, 0.17, 0.07, 0.1, 75.82 ), 
( 84, 765800, 1058390, 0.14, 0.06, 0.08, 72.36 ), 
( 85, 672819, 975341, 0.12, 0.05, 0.07, 68.98 ), 
( 86, 530641, 813574, 0.1, 0.04, 0.06, 65.22 ), 
( 87, 408984, 656292, 0.08, 0.03, 0.05, 62.32 ), 
( 88, 324282, 534597, 0.06, 0.02, 0.04, 60.66 ), 
( 89, 263084, 452314, 0.05, 0.02, 0.03, 58.16 ), 
( 90, 193982, 359823, 0.04, 0.01, 0.03, 53.91 ), 
( 91, 126484, 244595, 0.03, 0.01, 0.02, 51.71 ), 
( 92, 94157, 193519, 0.02, 0.01, 0.01, 48.66 ), 
( 93, 66717, 142574, 0.02, 0.01, 0.01, 46.79 ), 
( 94, 49532, 106924, 0.01, NULL, 0.01, 46.32 ), 
( 95, 36268, 81254, 0.01, NULL, 0.01, 44.64 ), 
( 96, 28664, 62225, 0.01, NULL, NULL, 46.07 ), 
( 97, 22045, 46603, 0.01, NULL, NULL, 47.3 ), 
( 98, 18355, 36334, NULL, NULL, NULL, 50.52 ), 
( 99, 12384, 25847, NULL, NULL, NULL, 47.91 ), 
( 100, 8852, 27082, NULL, NULL, NULL, 32.69 )

--DROP TABLE person

 

我是编写如下sql实现的，思路是找出需要汇总的年龄段，年龄除5余数为0的正好是汇总的分隔点，表自关联得到所有的年龄
注意点：字符串拼接要类型一致，sum求和时要对NULL值进行处理

WITH cet
AS (
SELECT SUM(a.personN + ISNULL(b.personN, 0) + ISNULL(c.personN, 0) + ISNULL(d.personN, 0) + ISNULL(e.personN, 0)) p1
,SUM(a.personW + ISNULL(b.personW, 0) + ISNULL(c.personW, 0) + ISNULL(d.personW, 0) + ISNULL(e.personW, 0)) p2
,CONVERT(VARCHAR(10), a.age) + '-' + CONVERT(VARCHAR(10), (a.age + 4)) + '岁' age
FROM person a
LEFT JOIN dbo.person b ON b.age = a.age + 1
LEFT JOIN dbo.person c ON c.age = a.age + 2
LEFT JOIN dbo.person d ON d.age = a.age + 3
LEFT JOIN dbo.person e ON e.age = a.age + 4
WHERE a.age % 5 = 0
GROUP BY a.age
WITH CUBE
UNION ALL
SELECT personN
,personW
,CONVERT(VARCHAR(10), age)
FROM dbo.person
)
SELECT SUM(p1 + p2) '总人口'
,MAX(p1) '男性人口'
,MAX(p2) '女性人口'
,age
FROM cet
GROUP BY age