本文介绍了T-SQL: - 查询按位置查找表中的最新和第二最新数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 大家好,我有一个名为DataTable的表,我必须提取每个区域类型的行数 这是表脚本和示例数据 CREATE TABLE DataTable (`Data_NId` int ,`IUSNId` int ,`Area_Name` varchar ( 18 ),`TimePeriod` varchar ( 9 ),`Data_Value` int ); INSERT INTO DataTable (`Data_NId`,`IUSNId`,`Area_Name `,`TimePeriod`,`Data_Value`) VALUES ( 74495 , 133 ,' Pashchim Champaran',' 2010-2011', 73 。 2 ),( 74496 , 133 ,' Kishanganj',' 2010-2011', 71 。 9 ),( 74497 , 133 ,' Katihar',' 2010-2011', 75 。 9 ),( 74498 , 133 ,' Saharsa',' 2010-2011', 76 。 9 ),( 74499 , 133 ,' Darbhanga',' 2010-2011', 54 。 4 ), ( 74500 , 133 ,' Muzaffarpur',' 2010-2011', 82 。 2 ) ,( 74501 , 133 ,' Gopalganj',' 2010-2011', 68 。 3 ),( 74502 , 133 ,' Siwan ',' 2010-2011', 73 。 4 ),( 74503 , 133 ,' Saran',' 2010-2011', 56 。 4 ),( 74504 , 133 ,' Vaishali',' 2010-2011', 81 。 4 ),( 74505 , 133 ,' Khagaria',' 2010-2011', 84 。 8 ),( 74506 , 133 ,' Bhagalpur',' 2010-2011' , 62 。 4 ), ( 74507 , 133 ,' Munger',' 2010-2011', 87 。 1 ),( 74508 , 133 ,' Lakhisarai' ,' 2010-2011', 67 。 2 ),( 74509 , 133 ,' Sheikhpura',' 2010-2011', 78 。 5 ),( 74510 , 133 ,' Nalanda',' 2010-2011', 62 。 6 ),( 74511 , 133 ,' Patna',' 2010-2011', 88 。 5 ), ( 74512 , 133 ,' Bhojpur',' 2010-2011', 88 ),( 74513 , 133 ,' Buxar', ' 2010-2011', 69 。 8 ),( 74514 , 133 ,' Rohtas',' 2010-2011', 75 。 6 ),( 74515 , 133 , ' Aurangabad',' 2010-2011 ', 76 。 7 ),( 74516 , 133 ,' Gaya',' 20 10-2011', 67 。 1 ),( 74517 , 133 ,' Nawada',' 2010-2011', 75 。 6 ),( 74518 , 133 ,' Jehanabad',' 2010-2011', 83 。 7 ),( 74528 , 133 ,' Pashchim Champaran',' 2011-2012', 74 。 1 ),( 74529 , 133 ,' Kishanganj',' 2011-2012', 82 ),( 74530 , 133 ,' Katihar',' 2011-2012', 80 ),( 74531 , 133 ,' Saharsa',' 2011-2012', 81 。 5 ), ( 74532 , 133 ,' Darbhanga',' 2011-2012', 59 。 9 ),( 74533 , 133 ,' Muzaffarpur',' 2011-2012' , 79 。 4 ),( 74534 , 133 ,' Gopalganj',' 2011-2012', 78 。 7 ),( 74535 , 133 ,' Siwan',' 2011-2012', 77 ),( 74536 , 133 ,' Saran',' 2011-2012', 62 。 3 ),( 74537 , 133 ,' Vaishali',' 2011-2012', 86 。 7 ),( 74538 , 133 ,' Khagaria',' 2011-2012', 85 。 9 ),( 74539 , 133 ,' Bhagalpur' ,' 2011-2012', 76 。 9 ),( 74540 , 133 ,' Munger',' 2011-2012', 81 。 4 ),( 74541 , 133 ,' Lakhisarai',' 2011-2012', 75 。 8 ),( 74542 , 133 ,' Sheikhpura',' 2011-2012', 84 。 7 ),( 74543 , 133 ,' Nalanda',' 2011-2012', 68 。 7 ),( 74544 , 133 ,' Patna',' 2011-2012', 88 。 4 ),( 74545 , 133 ,' Bhojpur',' 2011-2012', 86 。 4 ),( 74546 , 133 ,' Buxar',' 2011-2012', 73 。 4 ),( 74547 , 133 ,' Rohtas',' 2011 -2012', 77 。 2 ),( 74548 , 133 ,' Aurangabad',' 2011-2012', 75 。 7 ),( 74549 , 133 ,' Gaya',' 2011-2012', 66 。 3 ),( 74550 , 133 ,' Nawada',' 2011-2012', 75 。 1 ),( 74551 , 133 ,' Jehanabad',' 2011-2012', 80 。 7 ),( 74561 , 133 ,' Pashchim Champaran',' 2012-2013', 79 。 1 ),( 74562 , 133 , ' Kishanganj',' 2012-2013 ', 81 。 9 ),( 74563 , 133 ,' Katihar',' 2012-2013', 83 。 3 ),( 74564 , 133 ,' Saharsa',' 2012-2013', 87 ),( 74565 , 133 ,' Darbhanga',' 2012-2013', 64 。 4 ),( 74566 , 133 , ' Muzaffarpur',' 2012-2013 ', 83 。 7 ),( 74567 , 133 ,' Gopalganj',' 2012-2013', 83 。 4 ),( 74568 , 133 ,' Siwan',' 2012-2013', 76 。 7 ),( 74569 , 133 ,' Saran',' 2012-2013', 64 。 9 ),( 74570 , 133 ,' Vaishali',' 2012-2013', 78 。 4 ),( 74571 , 133 ,' Khagaria',' 2012-2013', 87 。 6 ), ( 74572 , 133 ,' Bhagalpur',' 2012-2013', 78 ),( 74573 , 133 ,' Munger',' 2012-2013', 84 。 8 ),( 74574 , 133 ,' Lakhisarai',' 2012-2013', 83 。 5 ),( 74575 , 133 , ' Sheikhpura',' 2012-2013', 82 。 4 ),( 74576 , 133 ,' Nalanda',' 2012-2013', 76 。 2 ) ,( 74577 , 133 ,' Patna',' 2012-2013', 89 。 7 ),( 74578 , 133 ,' Bhojpur ',' 2012-2013', 86 。 9 ),( 74579 , 133 ,' Buxar',' 2012-2013', 76 。 3 ),( 74580 , 133 ,' Rohtas',' 2012-2013', 81 。 5 ),( 74581 , 133 ,' Aurangabad',' 2012-2013', 73 。 3 ),( 74582 , 133 ,' Gaya',' 2012-2013' , 70 。 5 ),( 74583 , 133 ,' Nawada',' 2012-2013', 79 。 2 ),( 74584 , 133 ,' Jehanabad',' 2012-2013', 78 。 4 ) 这就是我想要的: Data_NId IUSNId Area_Name TimePeriod Data_Value P_Cange Trend 74496 133 Kishanganj 2010-2011 71.9 -0.1 N 74497 133 Katihar 2010-2011 75.9 3.3 P 74498 133 Saharsa 2010-2011 76.9 5.5 P 74499 133 Darbhanga 2010-2011 54.4 4.5 P 74500 1 33 Muzaffarpur 2010-2011 82.2 4.3 P 74501 133 Gopalganj 2010-2011 68.3 4.7 P 74502 133 Siwan 2010-2011 73.4 -0.3 N 74503 133 Saran 2010-2011 56.4 2.6 P 74504 133 Vaishali 2010-2011 81.4 -8.3 N 74505 133 Khagaria 2010-2011 84.8 1.7 P 74495 133 Pashchim Cha ... 2010-2011 73.2 5 P 74506 133 Bhagalpur 2010 -2011 62.4 1.1 P 74507 133 Munger 2010-2011 87.1 3.4 P 74508 133 Lakhisarai 2010-2011 67.2 7.7 P 74509 133 Sheikhpura 2010-2011 78.5 -2.3 N 74510 133 Nalanda 2010-2011 62.6 7.5 P 74511 133 Patna 2010-2011 88.5 1.3 P 74512 133 Bhojpur 2010-2011 88 0.5 P 74513 133 Buxar 2010-2011 69.8 2.9 P 74514 133 Rohtas 2010-2011 75.6 4.3 P 74515 133 Aurangabad 2010-2011 76.7 -2.4 N 74516 133 Gaya 2010-2011 67.1 4.2 P 74517 133 Nawada 2010-2011 75.6 4.1 P 74518 133 Jehanabad 2010-2011 83.7 -2.3 N 我尝试过: alter PROCEDURE 数据 @ iusnid int = 133 , @ sourcenid int = 124 , @ arealevel int = 3 , @ timeperiodnid int = 64 , @ toptime int = 0 , @ Stoptime int = 0 AS BEGIN SET NOCOUNT ON ; set @ toptime =( select max(TimePeriod_NId)来自 View_5 其中 IUSNId = @ iusnid 和 Source_NId = @ sourcenid 和 Area_Level = @ arealevel ) set @Stoptime =(选择 MAX(TimePeriod_NId)来自 View_5 where TimePeriod_NId<( select max(TimePeriod_NId)来自 View_5 其中 IUSNId = @ iusnid 和酸ce_NId = @ sourcenid 和 Area_Level = @ arealevel )和 IUSNId = @ iusnid 和 Source_NId = @ sourcenid 和 Area_Level = @ arealevel ) 选择 * - hi =(从View_5中选择Data_value,其中IUSNId = @iusnid,Source_NId = @sourcenid,Area_Level = @arealevel和TimePeriod_NId = @toptime), - lo =(从View_5中选择Data_value,其中IUSNId = iusnid,Source_NId = @sourcenid,Area_Level = @arealevel和TimePeriod_NId = @Stoptime) 来自 View_5 其中​​ IUSNId = @ iusnid 和 Source_NId = @ sourcenid 和 Area_Level = @ arealevel 解决方案 选择大小写时(maxDataTable.Data_Value- SecMaxDataTable.Data_Value)> 0然后'P'否则'N'以趋势结束,(maxDataTable.Data_Value-SecMaxDataTable.Data_Value)作为P_Change,maxDataTable.TimePeriod,maxDataTable.Area_Name,Data_NId,IUSNId来自(从DataTable 中选择Data_Value,TimePeriod,Area_Name,Data_NId,IUSNId,其中TimePeriod =(从DataTable中选择max(TimePeriod)))作为maxDataTable 内部联接(选择Data_Value ,DataPable的TimePeriod,Area_Name 其中TimePeriod =(从DataTable中选择max(TimePeriod),其中TimePeriod<(选择max(TimePeri) od)来自DataTable)))作为SecMaxDataTable on maxDataTable.Area_Name = SecMaxDataTable.Area_Name hi Deonadan, 我希望这个将解决您的问题。 Hi all, I have a table called DataTable and I have to extract the number of rows for each Area typeThis is the Table Script and sample dataCREATE TABLE DataTable (`Data_NId` int, `IUSNId` int, `Area_Name` varchar(18), `TimePeriod` varchar(9), `Data_Value` int);INSERT INTO DataTable (`Data_NId`, `IUSNId`, `Area_Name`, `TimePeriod`, `Data_Value`)VALUES (74495, 133, 'Pashchim Champaran', '2010-2011', 73.2), (74496, 133, 'Kishanganj', '2010-2011', 71.9), (74497, 133, 'Katihar', '2010-2011', 75.9), (74498, 133, 'Saharsa', '2010-2011', 76.9), (74499, 133, 'Darbhanga', '2010-2011', 54.4), (74500, 133, 'Muzaffarpur', '2010-2011', 82.2), (74501, 133, 'Gopalganj', '2010-2011', 68.3), (74502, 133, 'Siwan', '2010-2011', 73.4), (74503, 133, 'Saran', '2010-2011', 56.4), (74504, 133, 'Vaishali', '2010-2011', 81.4), (74505, 133, 'Khagaria', '2010-2011', 84.8), (74506, 133, 'Bhagalpur', '2010-2011', 62.4), (74507, 133, 'Munger', '2010-2011', 87.1), (74508, 133, 'Lakhisarai', '2010-2011', 67.2), (74509, 133, 'Sheikhpura', '2010-2011', 78.5), (74510, 133, 'Nalanda', '2010-2011', 62.6), (74511, 133, 'Patna', '2010-2011', 88.5), (74512, 133, 'Bhojpur', '2010-2011', 88), (74513, 133, 'Buxar', '2010-2011', 69.8), (74514, 133, 'Rohtas', '2010-2011', 75.6), (74515, 133, 'Aurangabad', '2010-2011', 76.7), (74516, 133, 'Gaya', '2010-2011', 67.1), (74517, 133, 'Nawada', '2010-2011', 75.6), (74518, 133, 'Jehanabad', '2010-2011', 83.7), (74528, 133, 'Pashchim Champaran', '2011-2012', 74.1), (74529, 133, 'Kishanganj', '2011-2012', 82), (74530, 133, 'Katihar', '2011-2012', 80), (74531, 133, 'Saharsa', '2011-2012', 81.5), (74532, 133, 'Darbhanga', '2011-2012', 59.9), (74533, 133, 'Muzaffarpur', '2011-2012', 79.4), (74534, 133, 'Gopalganj', '2011-2012', 78.7), (74535, 133, 'Siwan', '2011-2012', 77), (74536, 133, 'Saran', '2011-2012', 62.3), (74537, 133, 'Vaishali', '2011-2012', 86.7), (74538, 133, 'Khagaria', '2011-2012', 85.9), (74539, 133, 'Bhagalpur', '2011-2012', 76.9), (74540, 133, 'Munger', '2011-2012', 81.4), (74541, 133, 'Lakhisarai', '2011-2012', 75.8), (74542, 133, 'Sheikhpura', '2011-2012', 84.7), (74543, 133, 'Nalanda', '2011-2012', 68.7), (74544, 133, 'Patna', '2011-2012', 88.4), (74545, 133, 'Bhojpur', '2011-2012', 86.4), (74546, 133, 'Buxar', '2011-2012', 73.4), (74547, 133, 'Rohtas', '2011-2012', 77.2), (74548, 133, 'Aurangabad', '2011-2012', 75.7), (74549, 133, 'Gaya', '2011-2012', 66.3), (74550, 133, 'Nawada', '2011-2012', 75.1), (74551, 133, 'Jehanabad', '2011-2012', 80.7), (74561, 133, 'Pashchim Champaran', '2012-2013', 79.1), (74562, 133, 'Kishanganj', '2012-2013', 81.9), (74563, 133, 'Katihar', '2012-2013', 83.3), (74564, 133, 'Saharsa', '2012-2013', 87), (74565, 133, 'Darbhanga', '2012-2013', 64.4), (74566, 133, 'Muzaffarpur', '2012-2013', 83.7), (74567, 133, 'Gopalganj', '2012-2013', 83.4), (74568, 133, 'Siwan', '2012-2013', 76.7), (74569, 133, 'Saran', '2012-2013', 64.9), (74570, 133, 'Vaishali', '2012-2013', 78.4), (74571, 133, 'Khagaria', '2012-2013', 87.6), (74572, 133, 'Bhagalpur', '2012-2013', 78), (74573, 133, 'Munger', '2012-2013', 84.8), (74574, 133, 'Lakhisarai', '2012-2013', 83.5), (74575, 133, 'Sheikhpura', '2012-2013', 82.4), (74576, 133, 'Nalanda', '2012-2013', 76.2), (74577, 133, 'Patna', '2012-2013', 89.7), (74578, 133, 'Bhojpur', '2012-2013', 86.9), (74579, 133, 'Buxar', '2012-2013', 76.3), (74580, 133, 'Rohtas', '2012-2013', 81.5), (74581, 133, 'Aurangabad', '2012-2013', 73.3), (74582, 133, 'Gaya', '2012-2013', 70.5), (74583, 133, 'Nawada', '2012-2013', 79.2), (74584, 133, 'Jehanabad', '2012-2013', 78.4)This is what I’m trying to get:Data_NIdIUSNIdArea_NameTimePeriod Data_Value P_Cange Trend74496133Kishanganj2010-2011 71.9 -0.1 N74497133Katihar2010-2011 75.9 3.3 P74498133Saharsa2010-2011 76.9 5.5 P74499133Darbhanga2010-2011 54.4 4.5 P74500133Muzaffarpur2010-2011 82.2 4.3 P74501133Gopalganj2010-2011 68.3 4.7 P74502133Siwan2010-2011 73.4 -0.3 N74503133Saran2010-2011 56.4 2.6 P74504133Vaishali2010-2011 81.4 -8.3 N74505133Khagaria2010-2011 84.8 1.7 P74495133Pashchim Cha... 2010-2011 73.2 5 P74506133Bhagalpur2010-2011 62.4 1.1 P74507133Munger2010-2011 87.1 3.4 P74508133Lakhisarai2010-2011 67.2 7.7 P74509133Sheikhpura2010-2011 78.5 -2.3 N74510133Nalanda2010-2011 62.6 7.5 P74511133Patna2010-2011 88.5 1.3 P74512133Bhojpur2010-2011 88 0.5 P74513133Buxar2010-2011 69.8 2.9 P74514133Rohtas2010-2011 75.6 4.3 P74515133Aurangabad2010-2011 76.7 -2.4 N74516133Gaya2010-2011 67.1 4.2 P74517133Nawada2010-2011 75.6 4.1 P74518133Jehanabad2010-2011 83.7 -2.3 NWhat I have tried:alter PROCEDURE data@iusnid int = 133, @sourcenid int = 124, @arealevel int = 3, @timeperiodnid int = 64,@toptime int = 0, @Stoptime int = 0ASBEGINSET NOCOUNT ON;set @toptime = ( select max(TimePeriod_NId) from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel) set @Stoptime = (select MAX(TimePeriod_NId) from View_5 where TimePeriod_NId < ( select max(TimePeriod_NId) from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel ) and IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel ) select * --hi = (select Data_value from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel and TimePeriod_NId = @toptime) , -- lo=(select Data_value from View_5 where IUSNId= iusnid and Source_NId = @sourcenid and Area_Level = @arealevel and TimePeriod_NId = @Stoptime) from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel 解决方案 select case when (maxDataTable.Data_Value-SecMaxDataTable.Data_Value)>0 then 'P' else 'N' end as Trend,(maxDataTable.Data_Value-SecMaxDataTable.Data_Value) as P_Change ,maxDataTable.TimePeriod , maxDataTable.Area_Name,Data_NId ,IUSNId from (Select Data_Value,TimePeriod , Area_Name,Data_NId ,IUSNId from DataTable where TimePeriod = (select max(TimePeriod) from DataTable)) as maxDataTableinner join(Select Data_Value, TimePeriod, Area_Name from DataTable where TimePeriod = (select max(TimePeriod) from DataTable where TimePeriod<(select max(TimePeriod) from DataTable))) as SecMaxDataTable on maxDataTable.Area_Name=SecMaxDataTable.Area_Namehi Deonadan,I hope this will resolve your problem. 这篇关于T-SQL: - 查询按位置查找表中的最新和第二最新数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
10-26 20:22