22,207
社区成员
发帖
与我相关
我的任务
分享
--建立数据表(实际数据有2333行,这里仅仅用37行数据举例)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb ([id] [int] IDENTITY(1,1),[期号]int,notext nvarchar(255),[NO1]int,[NO2] int,[NO3] int,[NO4]INT,[NO5] int,[NO6] int);
go
insert into #tb
SELECT '2003077','04 12 16 22 24 25','4','12','16','22','24','25' UNION ALL
SELECT '2003078','07 12 23 26 29 30','7','12','23','26','29','30' UNION ALL
SELECT '2003079','12 15 22 23 26 31','12','15','22','23','26','31' UNION ALL
SELECT '2004077','08 09 10 14 16 26','8','9','10','14','16','26' UNION ALL
SELECT '2004078','04 05 10 21 24 26','4','5','10','21','24','26' UNION ALL
SELECT '2004079','07 13 14 17 19 30','7','13','14','17','19','30' UNION ALL
SELECT '2005077','08 17 22 24 32 33','8','17','22','24','32','33' UNION ALL
SELECT '2005078','03 11 15 20 26 32','3','11','15','20','26','32' UNION ALL
SELECT '2005079','03 09 20 24 25 28','3','9','20','24','25','28' UNION ALL
SELECT '2006077','08 09 12 13 19 33','8','9','12','13','19','33' UNION ALL
SELECT '2006078','03 05 17 22 31 33','3','5','17','22','31','33' UNION ALL
SELECT '2006079','06 11 13 17 20 32','6','11','13','17','20','32' UNION ALL
SELECT '2007077','06 08 14 21 28 29','6','8','14','21','28','29' UNION ALL
SELECT '2007078','04 06 07 23 25 32','4','6','7','23','25','32' UNION ALL
SELECT '2007079','03 04 14 20 21 25','3','4','14','20','21','25' UNION ALL
SELECT '2008077','04 12 22 26 30 33','4','12','22','26','30','33' UNION ALL
SELECT '2008078','06 13 16 26 30 33','6','13','16','26','30','33' UNION ALL
SELECT '2008079','03 04 05 10 20 32','3','4','5','10','20','32' UNION ALL
SELECT '2009077','01 09 14 16 28 32','1','9','14','16','28','32' UNION ALL
SELECT '2009078','05 07 12 14 15 20','5','7','12','14','15','20' UNION ALL
SELECT '2009079','02 09 16 21 30 31','2','9','16','21','30','31' UNION ALL
SELECT '2010077','02 08 14 20 21 24','2','8','14','20','21','24' UNION ALL
SELECT '2010078','01 03 09 11 17 23','1','3','9','11','17','23' UNION ALL
SELECT '2010079','08 11 12 14 18 22','8','11','12','14','18','22' UNION ALL
SELECT '2011077','01 07 08 15 26 29','1','7','8','15','26','29' UNION ALL
SELECT '2011078','03 05 13 20 22 29','3','5','13','20','22','29' UNION ALL
SELECT '2011079','03 14 15 16 24 29','3','14','15','16','24','29' UNION ALL
SELECT '2012077','02 04 13 18 26 28','2','4','13','18','26','28' UNION ALL
SELECT '2012078','08 15 22 24 28 33','8','15','22','24','28','33' UNION ALL
SELECT '2012079','06 07 12 24 30 33','6','7','12','24','30','33' UNION ALL
SELECT '2013077','09 14 23 24 26 29','9','14','23','24','26','29' UNION ALL
SELECT '2013078','03 05 17 18 26 27','3','5','17','18','26','27' UNION ALL
SELECT '2013079','07 13 17 19 22 26','7','13','17','19','22','26' UNION ALL
SELECT '2014077','08 09 12 15 19 22','8','9','12','15','19','22' UNION ALL
SELECT '2014078','02 03 05 06 09 17','2','3','5','6','9','17' UNION ALL
SELECT '2014079','02 07 16 22 27 28','2','7','16','22','27','28' UNION ALL
SELECT '2015077','01 06 08 10 13 27','1','6','8','10','13','27'
--历史同期号就是 [期号] like '20%%077' 或者[期号]like '20%%078'' 或者[期号]like '20%%079'
--查询历史同期号记录里后一行记录与前一行记录有什么相同的号码(data)和相同号码的个数(num),
if object_id('tempdb.dbo.#33') is not null drop table #33
go
select a.id,a.[期号],a.notext,a.no1,a.no2,a.no3,a.no4,a.no5,a.no6
,isnull(tt.query('<e> { for $i in e/r return data($i)} </e>').value('.','varchar(20)'),' ') as data
,isnull(tt.value('count(e/*)','int'),0) as num into #33
from #tb a left join #tb b on a.id-1=b.id
outer apply (select xmlcode =cast('<n>'+REPLACE(a.notext,' ','</n><n>')+'</n>'
+'<r>'+REPLACE(b.notext,' ','</r><r>')+'</r>' as xml)) c1
outer apply(select tt=xmlcode.query('
<e> { for $i in /r
where data($i) =data(/n)
return $i
}
</e>
'))c3
select * from #33
select *
from #33
order by RIGHT([期号],3),LEFT([期号],4)
2004092
2005092
2006092
2007092
2008092
2009092
2010092
2011092
2012092
2013092
2014092
2004093
2005093
2006093
2007093
2008093
2009093
2010093
2011093
2012093
2013093
2014093
2004094
2005094
2006094
2007094
2008094
2009094
2010094
2011094
2012094
2013094
2014094
2004095
2005095
2006095
2007095
2008095
2009095
2010095
2011095
2012095
2013095
2014095
2004096
2005096
2006096
2007096
2008096
2009096
2010096
2011096
2012096
2013096
2014096
2004097
2005097
2006097
2007097
2008097
2009097
2010097
2011097
2012097
2013097
2014097
2004098
2005098
2006098
2007098
2008098
2009098
2010098
2011098
2012098
2013098
2014098
2004099
2005099
2006099
2007099
2008099
2009099
2010099
2011099
2012099
2013099
2014099
2004100
2005100
2006100
2007100
2008100
2009100
2010100
2011100
2012100
2013100
2014100
2004101
2005101
2006101
2007101
2008101
2009101
2010101
2011101
2012101
2013101
2014101
2004102
2005102
2006102
2007102
2008102
2009102
2010102
2011102
2012102
2013102
2014102
2004103
2005103
2006103
2007103
2008103
2009103
2010103
2011103
2012103
2013103
2014103
2004104
2005104
2006104
2007104
2008104
2009104
2010104
2011104
2012104
2013104
2014104
2004105
2005105
2006105
2007105
2008105
2009105
2010105
2011105
2012105
2013105
2014105
2004106
2005106
2006106
2007106
2008106
2009106
2010106
2011106
2012106
2013106
2014106
2004107
2005107
2006107
2007107
2008107
2009107
2010107
2011107
2012107
2013107
2014107
2004108
2005108
2006108
2007108
2008108
2009108
2010108
2011108
2012108
2013108
2014108
2004109
2005109
2006109
2007109
2008109
2009109
2010109
2011109
2012109
2013109
2014109
2004110
2005110
2006110
2007110
2008110
2009110
2010110
2011110
2012110
2013110
2014110
2004111
2005111
2006111
2007111
2008111
2009111
2010111
2011111
2012111
2013111
2014111
2004112
2005112
2006112
2007112
2008112
2009112
2010112
2011112
2012112
2013112
2014112
2004113
2005113
2006113
2007113
2008113
2009113
2010113
2011113
2012113
2013113
2014113
2004114
2005114
2006114
2007114
2008114
2009114
2010114
2011114
2012114
2013114
2014114
2004115
2005115
2006115
2007115
2008115
2009115
2010115
2011115
2012115
2013115
2014115
2004116
2005116
2006116
2007116
2008116
2009116
2010116
2011116
2012116
2013116
2014116
2004117
2005117
2006117
2007117
2008117
2009117
2010117
2011117
2012117
2013117
2014117
2004118
2005118
2006118
2007118
2008118
2009118
2010118
2011118
2012118
2013118
2014118
2004119
2005119
2006119
2007119
2008119
2009119
2010119
2011119
2012119
2013119
2014119
2004120
2005120
2006120
2007120
2008120
2009120
2010120
2011120
2012120
2013120
2014120
2004121
2005121
2006121
2007121
2008121
2009121
2010121
2011121
2012121
2013121
2014121
2004122
2005122
2006122
2007122
2008122
2009122
2010122
2011122
2012122
2013122
2014122
2005123
2006123
2007123
2008123
2009123
2010123
2011123
2012123
2013123
2014123
2005124
2006124
2007124
2008124
2009124
2010124
2011124
2012124
2013124
2014124
2005125
2006125
2007125
2008125
2009125
2010125
2011125
2012125
2013125
2014125
2005126
2006126
2007126
2008126
2009126
2010126
2011126
2012126
2013126
2014126
2005127
2006127
2007127
2008127
2009127
2010127
2011127
2012127
2013127
2014127
2005128
2006128
2007128
2008128
2009128
2010128
2011128
2012128
2013128
2014128
2005129
2006129
2007129
2008129
2009129
2010129
2011129
2012129
2013129
2014129
2005130
2006130
2007130
2008130
2009130
2010130
2011130
2012130
2013130
2014130
2005131
2006131
2007131
2008131
2009131
2010131
2011131
2012131
2013131
2014131
2005132
2006132
2007132
2008132
2009132
2010132
2011132
2012132
2013132
2014132
2005133
2006133
2007133
2008133
2009133
2010133
2011133
2012133
2013133
2014133
2005134
2006134
2007134
2008134
2009134
2010134
2011134
2012134
2013134
2014134
2005135
2006135
2007135
2008135
2009135
2010135
2011135
2012135
2013135
2014135
2005136
2006136
2007136
2008136
2009136
2010136
2011136
2012136
2013136
2014136
2005137
2006137
2007137
2008137
2009137
2010137
2011137
2012137
2013137
2014137
2005138
2006138
2007138
2008138
2009138
2010138
2011138
2012138
2013138
2014138
2005139
2006139
2007139
2008139
2009139
2010139
2011139
2012139
2013139
2014139
2005140
2006140
2007140
2008140
2009140
2010140
2011140
2012140
2013140
2014140
2005141
2006141
2007141
2008141
2009141
2010141
2011141
2012141
2013141
2014141
2005142
2006142
2007142
2008142
2009142
2010142
2011142
2012142
2013142
2014142
2005143
2006143
2007143
2008143
2009143
2010143
2011143
2012143
2013143
2014143
2005144
2006144
2007144
2008144
2009144
2010144
2011144
2012144
2013144
2014144
2005145
2006145
2007145
2008145
2009145
2010145
2011145
2012145
2013145
2014145
2005146
2006146
2007146
2008146
2009146
2010146
2011146
2012146
2013146
2014146
2005147
2006147
2007147
2008147
2009147
2010147
2011147
2012147
2013147
2014147
2005148
2006148
2007148
2008148
2009148
2010148
2011148
2012148
2013148
2014148
2005149
2006149
2007149
2008149
2009149
2010149
2011149
2012149
2013149
2014149
2005150
2006150
2007150
2008150
2009150
2010150
2011150
2012150
2013150
2014150
2005151
2006151
2007151
2008151
2009151
2010151
2011151
2012151
2013151
2014151
2005152
2006152
2007152
2008152
2009152
2010152
2011152
2012152
2013152
2014152
2005153
2006153
2007153
2008153
2009153
2010153
2011153
2012153
2013153
2006154
2008154
2009154
2012154
2013154
2003001
2004001
2005001
2006001
2007001
2008001
2009001
2010001
2011001
2012001
2013001
2014001
2015001
2003002
2004002
2005002
2006002
2007002
2008002
2009002
2010002
2011002
2012002
2013002
2014002
2015002
2003003
2004003
2005003
2006003
2007003
2008003
2009003
2010003
2011003
2012003
2013003
2014003
2015003
2003004
2004004
2005004
2006004
2007004
2008004
2009004
2010004
2011004
2012004
2013004
2014004
2015004
2003005
2004005
2005005
2006005
2007005
2008005
2009005
2010005
2011005
2012005
2013005
2014005
2015005
2003006
2004006
2005006
2006006
2007006
2008006
2009006
2010006
2011006
2012006
2013006
2014006
2015006
2003007
2004007
2005007
2006007
2007007
2008007
2009007
2010007
2011007
2012007
2013007
2014007
2015007
2003008
2004008
2005008
2006008
2007008
2008008
2009008
2010008
2011008
2012008
2013008
2014008
2015008
2003009
2004009
2005009
2006009
2007009
2008009
2009009
2010009
2011009
2012009
2013009
2014009
2015009
2003010
2004010
2005010
2006010
2007010
2008010
2009010
2010010
2011010
2012010
2013010
2014010
2015010
2003011
2004011
2005011
2006011
2007011
2008011
2009011
2010011
2011011
2012011
2013011
2014011
2015011
2003012
2004012
2005012
2006012
2007012
2008012
2009012
2010012
2011012
2012012
2013012
2014012
2015012
2003013
2004013
2005013
2006013
2007013
2008013
2009013
2010013
2011013
2012013
2013013
2014013
2015013
2003014
2004014
2005014
2006014
2007014
2008014
2009014
2010014
2011014
2012014
2013014
2014014
2015014
2003015
2004015
2005015
2006015
2007015
2008015
2009015
2010015
2011015
2012015
2013015
2014015
2015015
2003016
2004016
2005016
2006016
2007016
2008016
2009016
2010016
2011016
2012016
2013016
2014016
2015016
2003017
2004017
2005017
2006017
2007017
2008017
2009017
2010017
2011017
2012017
2013017
2014017
2015017
2003018
2004018
2005018
2006018
2007018
2008018
2009018
2010018
2011018
2012018
2013018
2014018
2015018
2003019
2004019
2005019
2006019
2007019
2008019
2009019
2010019
2011019
2012019
2013019
2014019
2015019
2003020
2004020
2005020
2006020
2007020
2008020
2009020
2010020
2011020
2012020
2013020
2014020
2015020
2003021
2004021
2005021
2006021
2007021
2008021
2009021
2010021
2011021
2012021
2013021
2014021
2015021
2003022
2004022
2005022
2006022
2007022
2008022
2009022
2010022
2011022
2012022
2013022
2014022
2015022
2003023
2004023
2005023
2006023
2007023
2008023
2009023
2010023
2011023
2012023
2013023
2014023
2015023
2003024
2004024
2005024
2006024
2007024
2008024
2009024
2010024
2011024
2012024
2013024
2014024
2015024
2003025
2004025
2005025
2006025
2007025
2008025
2009025
2010025
2011025
2012025
2013025
2014025
2015025
2003026
2004026
2005026
2006026
2007026
2008026
2009026
2010026
2011026
2012026
2013026
2014026
2015026
2003027
2004027
2005027
2006027
2007027
2008027
2009027
2010027
2011027
2012027
2013027
2014027
2015027
2003028
2004028
2005028
2006028
2007028
2008028
2009028
2010028
2011028
2012028
2013028
2014028
2015028
2003029
2004029
2005029
2006029
2007029
2008029
2009029
2010029
2011029
2012029
2013029
2014029
2015029
2003030
2004030
2005030
2006030
2007030
2008030
2009030
2010030
2011030
2012030
2013030
2014030
2015030
2003031
2004031
2005031
2006031
2007031
2008031
2009031
2010031
2011031
2012031
2013031
2014031
2015031
2003032
2004032
2005032
2006032
2007032
2008032
2009032
2010032
2011032
2012032
2013032
2014032
2015032
2003033
2004033
2005033
2006033
2007033
2008033
2009033
2010033
2011033
2012033
2013033
2014033
2015033
2003034
2004034
2005034
2006034
2007034
2008034
2009034
2010034
2011034
2012034
2013034
2014034
2015034
2003035
2004035
2005035
2006035
2007035
2008035
2009035
2010035
2011035
2012035
2013035
2014035
2015035
2003036
2004036
2005036
2006036
2007036
2008036
2009036
2010036
2011036
2012036
2013036
2014036
2015036
2003037
2004037
2005037
2006037
2007037
2008037
2009037
2010037
2011037
2012037
2013037
2014037
2015037
2003038
2004038
2005038
2006038
2007038
2008038
2009038
2010038
2011038
2012038
2013038
2014038
2015038
2003039
2004039
2005039
2006039
2007039
2008039
2009039
2010039
2011039
2012039
2013039
2014039
2015039
2003040
2004040
2005040
2006040
2007040
2008040
2009040
2010040
2011040
2012040
2013040
2014040
2015040
2003041
2004041
2005041
2006041
2007041
2008041
2009041
2010041
2011041
2012041
2013041
2014041
2015041
2003042
2004042
2005042
2006042
2007042
2008042
2009042
2010042
2011042
2012042
2013042
2014042
2015042
2003043
2004043
2005043
2006043
2007043
2008043
2009043
2010043
2011043
2012043
2013043
2014043
2015043
2003044
2004044
2005044
2006044
2007044
2008044
2009044
2010044
2011044
2012044
2013044
2014044
2015044
2003045
2004045
2005045
2006045
2007045
2008045
2009045
2010045
2011045
2012045
2013045
2014045
2015045
2003046
2004046
2005046
2006046
2007046
2008046
2009046
2010046
2011046
2012046
2013046
2014046
2015046
2003047
2004047
2005047
2006047
2007047
2008047
2009047
2010047
2011047
2012047
2013047
2014047
2015047
2003048
2004048
2005048
2006048
2007048
2008048
2009048
2010048
2011048
2012048
2013048
2014048
2015048
2003049
2004049
2005049
2006049
2007049
2008049
2009049
2010049
2011049
2012049
2013049
2014049
2015049
2003050
2004050
2005050
2006050
2007050
2008050
2009050
2010050
2011050
2012050
2013050
2014050
2015050
2003051
2004051
2005051
2006051
2007051
2008051
2009051
2010051
2011051
2012051
2013051
2014051
2015051
2003052
2004052
2005052
2006052
2007052
2008052
2009052
2010052
2011052
2012052
2013052
2014052
2015052
2003053
2004053
2005053
2006053
2007053
2008053
2009053
2010053
2011053
2012053
2013053
2014053
2015053
2003054
2004054
2005054
2006054
2007054
2008054
2009054
2010054
2011054
2012054
2013054
2014054
2015054
2003055
2004055
2005055
2006055
2007055
2008055
2009055
2010055
2011055
2012055
2013055
2014055
2015055
2003056
2004056
2005056
2006056
2007056
2008056
2009056
2010056
2011056
2012056
2013056
2014056
2015056
2003057
2004057
2005057
2006057
2007057
2008057
2009057
2010057
2011057
2012057
2013057
2014057
2015057
2003058
2004058
2005058
2006058
2007058
2008058
2009058
2010058
2011058
2012058
2013058
2014058
2015058
2003059
2004059
2005059
2006059
2007059
2008059
2009059
2010059
2011059
2012059
2013059
2014059
2015059
2003060
2004060
2005060
2006060
2007060
2008060
2009060
2010060
2011060
2012060
2013060
2014060
2015060
2003061
2004061
2005061
2006061
2007061
2008061
2009061
2010061
2011061
2012061
2013061
2014061
2015061
2003062
2004062
2005062
2006062
2007062
2008062
2009062
2010062
2011062
2012062
2013062
2014062
2015062
2003063
2004063
2005063
2006063
2007063
2008063
2009063
2010063
2011063
2012063
2013063
2014063
2015063
2003064
2004064
2005064
2006064
2007064
2008064
2009064
2010064
2011064
2012064
2013064
2014064
2015064
2003065
2004065
2005065
2006065
2007065
2008065
2009065
2010065
2011065
2012065
2013065
2014065
2015065
2003066
2004066
2005066
2006066
2007066
2008066
2009066
2010066
2011066
2012066
2013066
2014066
2015066
2003067
2004067
2005067
2006067
2007067
2008067
2009067
2010067
2011067
2012067
2013067
2014067
2015067
2003068
2004068
2005068
2006068
2007068
2008068
2009068
2010068
2011068
2012068
2013068
2014068
2015068
2003069
2004069
2005069
2006069
2007069
2008069
2009069
2010069
2011069
2012069
2013069
2014069
2015069
2003070
2004070
2005070
2006070
2007070
2008070
2009070
2010070
2011070
2012070
2013070
2014070
2015070
2003071
2004071
2005071
2006071
2007071
2008071
2009071
2010071
2011071
2012071
2013071
2014071
2015071
2003072
2004072
2005072
2006072
2007072
2008072
2009072
2010072
2011072
2012072
2013072
2014072
2015072
2003073
2004073
2005073
2006073
2007073
2008073
2009073
2010073
2011073
2012073
2013073
2014073
2015073
2003074
2004074
2005074
2006074
2007074
2008074
2009074
2010074
2011074
2012074
2013074
2014074
2015074
2003075
2004075
2005075
2006075
2007075
2008075
2009075
2010075
2011075
2012075
2013075
2014075
2015075
2003076
2004076
2005076
2006076
2007076
2008076
2009076
2010076
2011076
2012076
2013076
2014076
2015076
2003077
2004077
2005077
2006077
2007077
2008077
2009077
2010077
2011077
2012077
2013077
2014077
2015077
2003078
2004078
2005078
2006078
2007078
2008078
2009078
2010078
2011078
2012078
2013078
2014078
2003079
2004079
2005079
2006079
2007079
2008079
2009079
2010079
2011079
2012079
2013079
2014079
2003080
2004080
2005080
2006080
2007080
2008080
2009080
2010080
2011080
2012080
2013080
2014080
2003081
2004081
2005081
2006081
2007081
2008081
2009081
2010081
2011081
2012081
2013081
2014081
2003082
2004082
2005082
2006082
2007082
2008082
2009082
2010082
2011082
2012082
2013082
2014082
2003083
2004083
2005083
2006083
2007083
2008083
2009083
2010083
2011083
2012083
2013083
2014083
2003084
2004084
2005084
2006084
2007084
2008084
2009084
2010084
2011084
2012084
2013084
2014084
2003085
2004085
2005085
2006085
2007085
2008085
2009085
2010085
2011085
2012085
2013085
2014085
2003086
2004086
2005086
2006086
2007086
2008086
2009086
2010086
2011086
2012086
2013086
2014086
2003087
2004087
2005087
2006087
2007087
2008087
2009087
2010087
2011087
2012087
2013087
2014087
2003088
2004088
2005088
2006088
2007088
2008088
2009088
2010088
2011088
2012088
2013088
2014088
2003089
2004089
2005089
2006089
2007089
2008089
2009089
2010089
2011089
2012089
2013089
2014089
2004090
2005090
2006090
2007090
2008090
2009090
2010090
2011090
2012090
2013090
2014090
2004091
2005091
2006091
2007091
2008091
2009091
2010091
2011091
2012091
2013091
2014091
select *
from #33
order by LEFT([期号],2)+RIGHT([期号],3)
2003010
2004010
2005010
2006010
2007010
2008010
2009010
2010010
2011010
2012010
2013010
2014010
2015010
2015010
2014010
2013010
2012010
2011010
2010010
2009010
2008010
2007010
2006010
2005010
2004010
2003010
如果需要从小到大的排序,该如何修改代码?
想要的是从小到大的排序,例如
2007010
2008010
2009010
2010010
2011010
2012010
2013010
2014010
2015010
2003001
2004001
2005001 --后一年同期号码与前一年同期号码对比,找出相同的号码和相同号码的个数
2006001 --后一年同期号码与前一年同期号码对比,找出相同的号码和相同号码的个数
2007001 --后一年同期号码与前一年同期号码对比,找出相同的号码和相同号码的个数
2008001
2009001
2010001
2011001
2012001
2013001
2014001
2015001
077期共有
2003077
2004077
2005077 --后一年同期号码与前一年同期号码对比,找出相同的号码和相同号码的个数
2006077 --后一年同期号码与前一年同期号码对比,找出相同的号码和相同号码的个数
2007077 --后一年同期号码与前一年同期号码对比,找出相同的号码和相同号码的个数
2008077
2009077
2010077
2011077
2012077
2013077
2014077
2015077
left join #tb b on ltrim(left(a.[期号],4)-1)+RIGHT(a.[期号],3)=b.[期号]