求教一个表中进行统计的问题
ID 股票代码 日期 开盘价 最高价 最低价 收盘价 成交量 成交额
16000001999-11-10 29.5029.8027.0027.75174085000.004859102208.00
26000001999-11-11 27.5828.3827.5327.7129403400.00821582208.00
36000001999-11-12 27.8628.3027.7728.0515007900.00421591616.00
46000001999-11-15 28.2028.2527.7027.7511921000.00332952800.00
56000001999-11-16 27.8827.9726.4826.5523223100.00628908288.00
66000001999-11-17 26.5027.1826.3727.1810052500.00268995040.00
76000001999-11-18 27.2027.5826.7827.028446500.00229577872.00
86000001999-11-19 27.5027.5326.8026.885374900.00145887120.00
96000001999-11-22 26.8826.9526.3026.455535400.00147086160.00
106000001999-11-23 26.4526.5526.1026.453843900.00101224496.00
116000001999-11-24 26.4426.5526.0126.434098000.00107344464.00
126000001999-11-25 26.3026.6626.0226.405725200.00150528192.00
136000001999-11-26 26.4326.6626.1526.452282600.0060508936.00
146000001999-11-29 26.4526.8326.2226.332681200.0071099576.00
156000001999-11-30 26.3026.5026.1126.402371300.0062335744.00
166000001999-12-01 26.3126.9026.2126.602865100.0076287992.00
176000001999-12-02 26.5226.7226.2226.261938400.0051110240.00
186000001999-12-03 26.2526.6526.2026.362552500.0067291880.00
196000001999-12-06 26.3026.3525.6025.666983900.00180516080.00
206000001999-12-07 25.6025.8525.5025.603955700.00101467904.00
216000001999-12-08 25.6025.7625.5025.532236500.0057214360.00
226000001999-12-09 25.5025.5525.3025.362564600.0065136972.00
236000001999-12-10 25.3626.1025.2225.973553900.0091041272.00
246000001999-12-13 25.9826.4525.6025.927058400.00184655968.00
256000001999-12-14 25.7026.0025.7026.001618400.0041845144.00
266000001999-12-15 26.0026.9025.9026.456797800.00180407904.00
276000001999-12-16 26.5026.5025.9526.003616200.0094601472.00
286000001999-12-21 25.4525.5525.1425.244215300.00106523992.00
296000001999-12-22 25.2425.3525.0525.153083500.0077614088.00
306000001999-12-23 25.2025.3024.7024.784161500.00103557904.00
316000001999-12-24 24.6025.0724.5924.752832400.0070142720.00
326000001999-12-27 24.7525.0024.5024.582233700.0055170692.00
336000001999-12-28 24.5425.0824.5024.603176600.0078430312.00
346000001999-12-29 24.7124.9724.5724.662284200.0056436028.00
356000001999-12-30 24.9024.9924.6524.752333100.0057888236.00
366000002000-01-04 24.9825.7824.7525.574496000.00113946784.00
376000002000-01-05 25.5725.9825.1525.285252800.00134465424.00
386000002000-01-06 25.1826.3025.0525.996229600.00160059792.00
396000002000-01-07 26.3027.5026.1226.9021355300.00575751104.00
406000002000-01-10 27.0027.8526.7127.2516539700.00450453504.00
416000002000-01-11 27.2527.3026.1226.209390700.00251055904.00
426000002000-01-12 26.0026.0024.8025.1235274900.00889867328.00
436000002000-01-13 25.0025.2524.8024.907975600.00199244304.00
这是股票数据，我想求股票收盘价大于开盘价的连续次数的概率分布
形成的结果是：
连续上涨天数 次数
1 15
2 20
3 3
[解决办法]
Group By
[解决办法]
好熟悉的数据 你哪儿的楼主？
[解决办法]
连续上涨天数?
这个连续如何理解？如果遇到断了，又继续上涨了几天，算不？
[解决办法]
create table tb(ID int,dm varchar(6),dt datetime,s decimal(6,2),m decimal(6,2),n decimal(6,2),e decimal(6,2),vl decimal(12,2),ve decimal(12,2))insert into tb select 1,'600000','1999-11-10',29.50,29.80,27.00,27.75,174085000.00,4859102208.00 union all select 2,'600000','1999-11-11',27.58,28.38,27.53,27.71,29403400.00,821582208.00 union all select3,'600000','1999-11-12',27.86,28.30,27.77,28.05,15007900.00,421591616.00 union all select4,'600000','1999-11-15',28.20,28.25,27.70,27.75,11921000.00,332952800.00 union all select5,'600000','1999-11-16',27.88,27.97,26.48,26.55,23223100.00,628908288.00 union all select6,'600000','1999-11-17',26.50,27.18,26.37,27.18,10052500.00,268995040.00 union all select7,'600000','1999-11-18',27.20,27.58,26.78,27.02,8446500.00,229577872.00 union all select8,'600000','1999-11-19',27.50,27.53,26.80,26.88,5374900.00,145887120.00 union all select9,'600000','1999-11-22',26.88,26.95,26.30,26.45,5535400.00,147086160.00 union all select10,'600000','1999-11-23',26.45,26.55,26.10,26.45,3843900.00,101224496.00 union all select11,'600000','1999-11-24',26.44,26.55,26.01,26.43,4098000.00,107344464.00 union all select12,'600000','1999-11-25',26.30,26.66,26.02,26.40,5725200.00,150528192.00 union all select13,'600000','1999-11-26',26.43,26.66,26.15,26.45,2282600.00,60508936.00 union all select14,'600000','1999-11-29',26.45,26.83,26.22,26.33,2681200.00,71099576.00 union all select15,'600000','1999-11-30',26.30,26.50,26.11,26.40,2371300.00,62335744.00 union all select16,'600000','1999-12-01',26.31,26.90,26.21,26.60,2865100.00,76287992.00 union all select17,'600000','1999-12-02',26.52,26.72,26.22,26.26,1938400.00,51110240.00 union all select18,'600000','1999-12-03',26.25,26.65,26.20,26.36,2552500.00,67291880.00 union all select19,'600000','1999-12-06',26.30,26.35,25.60,25.66,6983900.00,180516080.00 union all select20,'600000','1999-12-07',25.60,25.85,25.50,25.60,3955700.00,101467904.00 union all select21,'600000','1999-12-08',25.60,25.76,25.50,25.53,2236500.00,57214360.00 union all select22,'600000','1999-12-09',25.50,25.55,25.30,25.36,2564600.00,65136972.00 union all select23,'600000','1999-12-10',25.36,26.10,25.22,25.97,3553900.00,91041272.00 union all select24,'600000','1999-12-13',25.98,26.45,25.60,25.92,7058400.00,184655968.00 union all select25,'600000','1999-12-14',25.70,26.00,25.70,26.00,1618400.00,41845144.00 union all select26,'600000','1999-12-15',26.00,26.90,25.90,26.45,6797800.00,180407904.00 union all select27,'600000','1999-12-16',26.50,26.50,25.95,26.00,3616200.00,94601472.00 union all select28,'600000','1999-12-21',25.45,25.55,25.14,25.24,4215300.00,106523992.00 union all select29,'600000','1999-12-22',25.24,25.35,25.05,25.15,3083500.00,77614088.00 union all select30,'600000','1999-12-23',25.20,25.30,24.70,24.78,4161500.00,103557904.00 union all select31,'600000','1999-12-24',24.60,25.07,24.59,24.75,2832400.00,70142720.00 union all select32,'600000','1999-12-27',24.75,25.00,24.50,24.58,2233700.00,55170692.00 union all select33,'600000','1999-12-28',24.54,25.08,24.50,24.60,3176600.00,78430312.00 union all select34,'600000','1999-12-29',24.71,24.97,24.57,24.66,2284200.00,56436028.00 union all select35,'600000','1999-12-30',24.90,24.99,24.65,24.75,2333100.00,57888236.00 union all select36,'600000','2000-01-04',24.98,25.78,24.75,25.57,4496000.00,113946784.00 union all select37,'600000','2000-01-05',25.57,25.98,25.15,25.28,5252800.00,134465424.00 union all select38,'600000','2000-01-06',25.18,26.30,25.05,25.99,6229600.00,160059792.00 union all select39,'600000','2000-01-07',26.30,27.50,26.12,26.90,21355300.00,575751104.00 union all select40,'600000','2000-01-10',27.00,27.85,26.71,27.25,16539700.00,450453504.00 union all select41,'600000','2000-01-11',27.25,27.30,26.12,26.20,9390700.00,251055904.00 union all select42,'600000','2000-01-12',26.00,26.00,24.80,25.12,35274900.00,889867328.00 union all select43,'600000','2000-01-13',25.00,25.25,24.80,24.90,7975600.00,199244304.00gowith cte as(select id from tb where e>s)select 1,convert(decimal(4,2),count(*))*100/(select count(*) from tb) from cte a where not exists(select 1 from cte where id=a.id+1 or id=a.id-1)union allselect 2,convert(decimal(4,2),count(*))*100/(select count(*) from tb) from cte a where not exists(select 1 from cte where id=a.id+2 or id=a.id-1) and exists(select 1 from cte where id=a.id+1)union allselect 3,convert(decimal(4,2),count(*))*100/(select count(*) from tb) from cte a where not exists(select 1 from cte where id=a.id+3 or id=a.id-1) and exists(select 1 from cte where id=a.id+1) and exists(select 1 from cte where id=a.id+2)union allselect 4,convert(decimal(4,2),count(*))*100/(select count(*) from tb) from cte a where not exists(select 1 from cte where id=a.id+4 or id=a.id-1) and exists(select 1 from cte where id=a.id+1) and exists(select 1 from cte where id=a.id+2) and exists(select 1 from cte where id=a.id+3)godrop table tb/*----------- ---------------------------------------1 13.95348837209302 9.30232558139533 2.32558139534884 0.0000000000000(4 行受影响)*/
[解决办法]
create table tb(ID int,dm varchar(6),dt datetime,s decimal(6,2),m decimal(6,2),n decimal(6,2),e decimal(6,2),vl decimal(12,2),ve decimal(12,2))insert into tb select 1,'600000','1999-11-10',29.50,29.80,27.00,27.75,174085000.00,4859102208.00 union all select 2,'600000','1999-11-11',27.58,28.38,27.53,27.71,29403400.00,821582208.00 union all select3,'600000','1999-11-12',27.86,28.30,27.77,28.05,15007900.00,421591616.00 union all select4,'600000','1999-11-15',28.20,28.25,27.70,27.75,11921000.00,332952800.00 union all select5,'600000','1999-11-16',27.88,27.97,26.48,26.55,23223100.00,628908288.00 union all select6,'600000','1999-11-17',26.50,27.18,26.37,27.18,10052500.00,268995040.00 union all select7,'600000','1999-11-18',27.20,27.58,26.78,27.02,8446500.00,229577872.00 union all select8,'600000','1999-11-19',27.50,27.53,26.80,26.88,5374900.00,145887120.00 union all select9,'600000','1999-11-22',26.88,26.95,26.30,26.45,5535400.00,147086160.00 union all select10,'600000','1999-11-23',26.45,26.55,26.10,26.45,3843900.00,101224496.00 union all select11,'600000','1999-11-24',26.44,26.55,26.01,26.43,4098000.00,107344464.00 union all select12,'600000','1999-11-25',26.30,26.66,26.02,26.40,5725200.00,150528192.00 union all select13,'600000','1999-11-26',26.43,26.66,26.15,26.45,2282600.00,60508936.00 union all select14,'600000','1999-11-29',26.45,26.83,26.22,26.33,2681200.00,71099576.00 union all select15,'600000','1999-11-30',26.30,26.50,26.11,26.40,2371300.00,62335744.00 union all select16,'600000','1999-12-01',26.31,26.90,26.21,26.60,2865100.00,76287992.00 union all select17,'600000','1999-12-02',26.52,26.72,26.22,26.26,1938400.00,51110240.00 union all select18,'600000','1999-12-03',26.25,26.65,26.20,26.36,2552500.00,67291880.00 union all select19,'600000','1999-12-06',26.30,26.35,25.60,25.66,6983900.00,180516080.00 union all select20,'600000','1999-12-07',25.60,25.85,25.50,25.60,3955700.00,101467904.00 union all select21,'600000','1999-12-08',25.60,25.76,25.50,25.53,2236500.00,57214360.00 union all select22,'600000','1999-12-09',25.50,25.55,25.30,25.36,2564600.00,65136972.00 union all select23,'600000','1999-12-10',25.36,26.10,25.22,25.97,3553900.00,91041272.00 union all select24,'600000','1999-12-13',25.98,26.45,25.60,25.92,7058400.00,184655968.00 union all select25,'600000','1999-12-14',25.70,26.00,25.70,26.00,1618400.00,41845144.00 union all select26,'600000','1999-12-15',26.00,26.90,25.90,26.45,6797800.00,180407904.00 union all select27,'600000','1999-12-16',26.50,26.50,25.95,26.00,3616200.00,94601472.00 union all select28,'600000','1999-12-21',25.45,25.55,25.14,25.24,4215300.00,106523992.00 union all select29,'600000','1999-12-22',25.24,25.35,25.05,25.15,3083500.00,77614088.00 union all select30,'600000','1999-12-23',25.20,25.30,24.70,24.78,4161500.00,103557904.00 union all select31,'600000','1999-12-24',24.60,25.07,24.59,24.75,2832400.00,70142720.00 union all select32,'600000','1999-12-27',24.75,25.00,24.50,24.58,2233700.00,55170692.00 union all select33,'600000','1999-12-28',24.54,25.08,24.50,24.60,3176600.00,78430312.00 union all select34,'600000','1999-12-29',24.71,24.97,24.57,24.66,2284200.00,56436028.00 union all select35,'600000','1999-12-30',24.90,24.99,24.65,24.75,2333100.00,57888236.00 union all select36,'600000','2000-01-04',24.98,25.78,24.75,25.57,4496000.00,113946784.00 union all select37,'600000','2000-01-05',25.57,25.98,25.15,25.28,5252800.00,134465424.00 union all select38,'600000','2000-01-06',25.18,26.30,25.05,25.99,6229600.00,160059792.00 union all select39,'600000','2000-01-07',26.30,27.50,26.12,26.90,21355300.00,575751104.00 union all select40,'600000','2000-01-10',27.00,27.85,26.71,27.25,16539700.00,450453504.00 union all select41,'600000','2000-01-11',27.25,27.30,26.12,26.20,9390700.00,251055904.00 union all select42,'600000','2000-01-12',26.00,26.00,24.80,25.12,35274900.00,889867328.00 union all select43,'600000','2000-01-13',25.00,25.25,24.80,24.90,7975600.00,199244304.00gowith cte as(select id from tb where e>s)select 1 as 连续上涨天数,convert(decimal(4,2),count(*)) as 次数from cte a where not exists(select 1 from cte where id=a.id+1 or id=a.id-1)union allselect 2,convert(decimal(4,2),count(*)) from cte a where not exists(select 1 from cte where id=a.id+2 or id=a.id-1) and exists(select 1 from cte where id=a.id+1)union allselect 3,convert(decimal(4,2),count(*)) from cte a where not exists(select 1 from cte where id=a.id+3 or id=a.id-1) and exists(select 1 from cte where id=a.id+1) and exists(select 1 from cte where id=a.id+2)union allselect 4,convert(decimal(4,2),count(*)) from cte a where not exists(select 1 from cte where id=a.id+4 or id=a.id-1) and exists(select 1 from cte where id=a.id+1) and exists(select 1 from cte where id=a.id+2) and exists(select 1 from cte where id=a.id+3)godrop table tb/*连续上涨天数 次数1 6.002 4.003 1.004 0.00*/
[解决办法]
create table tb(ID int,dm varchar(6),dt datetime,s decimal(6,2),m decimal(6,2),n decimal(6,2),e decimal(6,2),vl decimal(12,2),ve decimal(12,2))insert into tb select 1,'600000','1999-11-10',29.50,29.80,27.00,27.75,174085000.00,4859102208.00 union all select 2,'600000','1999-11-11',27.58,28.38,27.53,27.71,29403400.00,821582208.00 union all select3,'600000','1999-11-12',27.86,28.30,27.77,28.05,15007900.00,421591616.00 union all select4,'600000','1999-11-15',28.20,28.25,27.70,27.75,11921000.00,332952800.00 union all select5,'600000','1999-11-16',27.88,27.97,26.48,26.55,23223100.00,628908288.00 union all select6,'600000','1999-11-17',26.50,27.18,26.37,27.18,10052500.00,268995040.00 union all select7,'600000','1999-11-18',27.20,27.58,26.78,27.02,8446500.00,229577872.00 union all select8,'600000','1999-11-19',27.50,27.53,26.80,26.88,5374900.00,145887120.00 union all select9,'600000','1999-11-22',26.88,26.95,26.30,26.45,5535400.00,147086160.00 union all select10,'600000','1999-11-23',26.45,26.55,26.10,26.45,3843900.00,101224496.00 union all select11,'600000','1999-11-24',26.44,26.55,26.01,26.43,4098000.00,107344464.00 union all select12,'600000','1999-11-25',26.30,26.66,26.02,26.40,5725200.00,150528192.00 union all select13,'600000','1999-11-26',26.43,26.66,26.15,26.45,2282600.00,60508936.00 union all select14,'600000','1999-11-29',26.45,26.83,26.22,26.33,2681200.00,71099576.00 union all select15,'600000','1999-11-30',26.30,26.50,26.11,26.40,2371300.00,62335744.00 union all select16,'600000','1999-12-01',26.31,26.90,26.21,26.60,2865100.00,76287992.00 union all select17,'600000','1999-12-02',26.52,26.72,26.22,26.26,1938400.00,51110240.00 union all select18,'600000','1999-12-03',26.25,26.65,26.20,26.36,2552500.00,67291880.00 union all select19,'600000','1999-12-06',26.30,26.35,25.60,25.66,6983900.00,180516080.00 union all select20,'600000','1999-12-07',25.60,25.85,25.50,25.60,3955700.00,101467904.00 union all select21,'600000','1999-12-08',25.60,25.76,25.50,25.53,2236500.00,57214360.00 union all select22,'600000','1999-12-09',25.50,25.55,25.30,25.36,2564600.00,65136972.00 union all select23,'600000','1999-12-10',25.36,26.10,25.22,25.97,3553900.00,91041272.00 union all select24,'600000','1999-12-13',25.98,26.45,25.60,25.92,7058400.00,184655968.00 union all select25,'600000','1999-12-14',25.70,26.00,25.70,26.00,1618400.00,41845144.00 union all select26,'600000','1999-12-15',26.00,26.90,25.90,26.45,6797800.00,180407904.00 union all select27,'600000','1999-12-16',26.50,26.50,25.95,26.00,3616200.00,94601472.00 union all select28,'600000','1999-12-21',25.45,25.55,25.14,25.24,4215300.00,106523992.00 union all select29,'600000','1999-12-22',25.24,25.35,25.05,25.15,3083500.00,77614088.00 union all select30,'600000','1999-12-23',25.20,25.30,24.70,24.78,4161500.00,103557904.00 union all select31,'600000','1999-12-24',24.60,25.07,24.59,24.75,2832400.00,70142720.00 union all select32,'600000','1999-12-27',24.75,25.00,24.50,24.58,2233700.00,55170692.00 union all select33,'600000','1999-12-28',24.54,25.08,24.50,24.60,3176600.00,78430312.00 union all select34,'600000','1999-12-29',24.71,24.97,24.57,24.66,2284200.00,56436028.00 union all select35,'600000','1999-12-30',24.90,24.99,24.65,24.75,2333100.00,57888236.00 union all select36,'600000','2000-01-04',24.98,25.78,24.75,25.57,4496000.00,113946784.00 union all select37,'600000','2000-01-05',25.57,25.98,25.15,25.28,5252800.00,134465424.00 union all select38,'600000','2000-01-06',25.18,26.30,25.05,25.99,6229600.00,160059792.00 union all select39,'600000','2000-01-07',26.30,27.50,26.12,26.90,21355300.00,575751104.00 union all select40,'600000','2000-01-10',27.00,27.85,26.71,27.25,16539700.00,450453504.00 union all select41,'600000','2000-01-11',27.25,27.30,26.12,26.20,9390700.00,251055904.00 union all select42,'600000','2000-01-12',26.00,26.00,24.80,25.12,35274900.00,889867328.00 union all select43,'600000','2000-01-13',25.00,25.25,24.80,24.90,7975600.00,199244304.00gowith cte as(select id from tb where e>s)select 1 as 连续上涨天数,count(*) as 次数from cte a where not exists(select 1 from cte where id=a.id+1 or id=a.id-1)union allselect 2,count(*) from cte a where not exists(select 1 from cte where id=a.id+2 or id=a.id-1) and exists(select 1 from cte where id=a.id+1)union allselect 3,count(*) from cte a where not exists(select 1 from cte where id=a.id+3 or id=a.id-1) and exists(select 1 from cte where id=a.id+1) and exists(select 1 from cte where id=a.id+2)union allselect 4,count(*) from cte a where not exists(select 1 from cte where id=a.id+4 or id=a.id-1) and exists(select 1 from cte where id=a.id+1) and exists(select 1 from cte where id=a.id+2) and exists(select 1 from cte where id=a.id+3)godrop table tb/*连续上涨天数 次数1 62 43 14 0*/
