在mySql中添加RowNumber列 [英] Add RowNumber Column in mySql

问题描述

我正在尝试在我的程序中添加一列Rownumber ..



我的预期输出与Sql相同

  SELECT  ROW_NUMBER（） OVER （ PARTITION   BY  列  ORDER   BY  列  DESC ） AS  RowNumber 

这个在mySQL中输出我是这样的

 DELIMITER $$ 
 
  DROP   PROCEDURE   IF   EXISTS `Test2 `$$ 
  CREATE   PROCEDURE `Test2`（
 from日期 DATETIME ，
 toDATE  DATETIME ，
 sat  int ，
 sun  int ，
 fromhr  int  ，
 tohr  int  
）
  BEGIN  
 
  SELECT   DISTINCT  
 g.name，
 g.graphid，
 i.hostid，
 i.itemid，
   @ row_num ：=  IF （@ prev_value = g.graphid，@ row_num + 1， 1 ） AS  RowNumber ，
  @ prev_value ：= g.graphid， 
  TRUNCATE  （（tu.value_max）/（1024 * 1024）， 2 ） AS  value_max ，
 
 
 FROM_UNIXTIME（tu.clock，' ％H'）小时，
 FROM_UNIXTIME（tu.clock，' ％Y-％m-％d％h： ％i：％s：％p'）日期，
 ci.Bandwidth  AS 容量，
 
  TRUNCATE （（tu.value_min）/（1024 * 1024）， 2 ） AS  value_min，
  TRUNCATE （（tu.value_avg）/（ 1024 * 1024）， 2 ） AS  value_avg，
 ci.NewInterfaceName，
 lm.LocationName，
 bfm.BusinessFunctionCode，
 ltm.LinkTypeCode，
 bm.BuildingCode，
 spm.ProviderCode 
  FROM  
 （ SELECT   @ row_num  =  1 ）x，
（ SELECT   @prev_value ：= ' '）y， 
 trends_uint tu 
 
 
  INNER   JOIN 项目i  ON  i.itemid = tu.itemid 
  INNER   JOIN  hosts_groups hg  ON  hg.hostid = i.hostid 
  INNER   JOIN  graphs_items gi  ON  gi.itemid = i.itemid 
  INNER   JOIN 图g  ON  g.graphid = gi.graphid 
  INNER   JOIN 主机h  ON  h.hostid = hg.hostid 
  LEFT   JOIN  flip_linkutilisation .categorisedinterfaces ci  on  ci.graphid = g.graphid 
  LEFT   JOIN  flip_linkutilisation.buildingmaster bm  ON  bm.BuildingID = ci.BuildingID 
  LEFT   JOIN  flip_linkutilisation.businessfunctionmaster bfm  ON  bfm.BusinessFunctionID = bm.BusinessFunctionID 
 < span class =code-keyword> LEFT   JOIN  flip_linkutilisation.locationmaster lm  ON  lm。 LocationID = bm.LocationID 
  LEFT   JOIN  flip_linkutilisation.linktypemaster ltm  ON  ltm.LinkTypeID = ci.LinkT ype 
  LEFT   JOIN  flip_linkutilisation.serviceprovidermaster spm  ON  spm.ServiceProviderID = ci.ServiceProviderID 
 
  WHERE  g.graphid = 1252 
 
 订单 按 value_max  DESC  
; 
  END  $$ 
 
 DELIMITER; 

但是在输出中行数加倍..

行数正确...但每一行都在重复..

我错了什么确实...

请帮我解决



谢谢

解决方案

< blockquote> DROP PROCEDURE IF EXISTS `Test2`

CREATE PROCEDURE `Test2`（
fromDate DATETIME ，
toDATE DATETIME ，
sat int ，
sun int ，
fromhr < span class =code-keyword> int ，
tohr int
）
BEGIN

SELECT DISTINCT
g.name，
g.graphid，
i.hostid，
i.itemid，
@ row_num ：= IF （@ prev_value = g.graphid，@ row_num + 1， 1 ） AS RowNumber，
@ prev_value ：= g.graphid，
TRUNCATE （（tu.value_max）/（1024 * 1024）， 2 ） AS value_max，


FROM_UNIXTIME（tu.clock，' ％H'）小时，
FROM_UNIXTIME（tu.clock，' ％Y-％m-％d％h：％i：％s：％p'）日期，
ci.Bandwidth AS 容量，

TRUNCATE （（tu.value_min）/（1024 * 1024）， 2 ） AS value_min，
TRUNCATE （ （tu.value_avg）/（1024 * 1024）， 2 ） AS value_avg，
ci .NewInterfaceName，
lm.LocationName，
bfm.BusinessFunctionCode，
ltm.LinkTypeCode，
bm.BuildingCode，
spm.ProviderCode
FROM
（ SELECT @ row_num = 1 ）x，
（ SELECT @ prev_value ：= ' '）y，
trends_uint tu


INNER JOIN 项目i ON i.itemid = tu.itemid
INNER JOIN hosts_groups hg ON hg.hostid = i.hostid
INNER JOIN graphs_items gi ON gi.itemid = i.itemid
INNER JOIN 图g ON g.graphid = gi.graphid
INNER JOIN hosts h ON h.hostid = hg.hostid
LEFT JOIN flip_linkutilisation.categorisedinterfaces ci on ci.graphid = g.graphid
LEFT JOIN flip_linkutilisati on.buildingmaster bm ON bm.BuildingID = ci.BuildingID
LEFT JOIN flip_linkutilisation.businessfunctionmaster bfm ON bfm.BusinessFunctionID = bm.BusinessFunctionID
LEFT JOIN flip_linkutilisation.locationmaster lm ON lm.LocationID = bm.LocationID
LEFT JOIN flip_linkutilisation.linktypemaster ltm ON ltm .LinkTypeID = ci.LinkType
LEFT JOIN flip_linkutilisation.serviceprovidermaster spm ON spm.ServiceProviderID = ci.ServiceProviderID

WHERE g.graphid = 1252

订单 按 value_max DESC
;
END

DELIMITER;

但是在输出中行数加倍..

行数正确...但每一行都在重复..

什么错我做了...

请帮我解决



谢谢

I am trying to add one column Rownumber in my procedure..

My expected out put is same like Sql

SELECT  ROW_NUMBER() OVER (PARTITION BY Column ORDER BY Column DESC) AS RowNumber

For this out put in mySQL I did like this

DELIMITER $$

DROP PROCEDURE IF EXISTS `Test2` $$
CREATE PROCEDURE `Test2`(
  fromDate DATETIME,
  toDATE DATETIME,
  sat      int,
  sun      int,
  fromhr   int,
  tohr     int
)
BEGIN

  SELECT DISTINCT
    g.name,
    g.graphid,
    i.hostid,
    i.itemid,
    @row_num := IF(@prev_value=g.graphid,@row_num+1,1) AS RowNumber,
    @prev_value := g.graphid,
    TRUNCATE((tu.value_max)/(1024*1024),2) AS value_max,


    FROM_UNIXTIME(tu.clock,'%H') Hour,
    FROM_UNIXTIME(tu.clock,'%Y-%m-%d %h:%i:%s:%p') Date,
    ci.Bandwidth AS Capacity,

    TRUNCATE((tu.value_min)/(1024*1024),2) AS value_min,
    TRUNCATE((tu.value_avg)/(1024*1024),2) AS value_avg,
    ci.NewInterfaceName,
    lm.LocationName,
    bfm.BusinessFunctionCode,
    ltm.LinkTypeCode,
    bm.BuildingCode,
    spm.ProviderCode
  FROM
      (SELECT @row_num = 1) x,
      (SELECT @prev_value := '') y,
      trends_uint  tu


  INNER JOIN items i ON i.itemid=tu.itemid
  INNER JOIN hosts_groups hg ON hg.hostid=i.hostid
  INNER JOIN graphs_items gi ON gi.itemid=i.itemid
  INNER JOIN graphs g ON g.graphid=gi.graphid
  INNER JOIN hosts h ON h.hostid=hg.hostid
  LEFT JOIN flip_linkutilisation.categorisedinterfaces ci on ci.graphid=g.graphid
  LEFT JOIN flip_linkutilisation.buildingmaster bm ON bm.BuildingID=ci.BuildingID
  LEFT JOIN flip_linkutilisation.businessfunctionmaster bfm ON bfm.BusinessFunctionID=bm.BusinessFunctionID
  LEFT JOIN flip_linkutilisation.locationmaster lm ON lm.LocationID=bm.LocationID
  LEFT JOIN flip_linkutilisation.linktypemaster ltm ON ltm.LinkTypeID=ci.LinkType
  LEFT JOIN flip_linkutilisation.serviceprovidermaster spm ON spm.ServiceProviderID=ci.ServiceProviderID

  WHERE g.graphid=1252 

  order by value_max DESC
;
END $$

DELIMITER ;

But In the out put the number of rows are doubled..
Row number coming correctly...but each row is repeating..
What mistake i did...
Please help me to solve

Thank you

解决方案

DROP PROCEDURE IF EXISTS `Test2`

CREATE PROCEDURE `Test2`( fromDate DATETIME, toDATE DATETIME, sat int, sun int, fromhr int, tohr int ) BEGIN SELECT DISTINCT g.name, g.graphid, i.hostid, i.itemid, @row_num := IF(@prev_value=g.graphid,@row_num+1,1) AS RowNumber, @prev_value := g.graphid, TRUNCATE((tu.value_max)/(1024*1024),2) AS value_max, FROM_UNIXTIME(tu.clock,'%H') Hour, FROM_UNIXTIME(tu.clock,'%Y-%m-%d %h:%i:%s:%p') Date, ci.Bandwidth AS Capacity, TRUNCATE((tu.value_min)/(1024*1024),2) AS value_min, TRUNCATE((tu.value_avg)/(1024*1024),2) AS value_avg, ci.NewInterfaceName, lm.LocationName, bfm.BusinessFunctionCode, ltm.LinkTypeCode, bm.BuildingCode, spm.ProviderCode FROM (SELECT @row_num = 1) x, (SELECT @prev_value := '') y, trends_uint tu INNER JOIN items i ON i.itemid=tu.itemid INNER JOIN hosts_groups hg ON hg.hostid=i.hostid INNER JOIN graphs_items gi ON gi.itemid=i.itemid INNER JOIN graphs g ON g.graphid=gi.graphid INNER JOIN hosts h ON h.hostid=hg.hostid LEFT JOIN flip_linkutilisation.categorisedinterfaces ci on ci.graphid=g.graphid LEFT JOIN flip_linkutilisation.buildingmaster bm ON bm.BuildingID=ci.BuildingID LEFT JOIN flip_linkutilisation.businessfunctionmaster bfm ON bfm.BusinessFunctionID=bm.BusinessFunctionID LEFT JOIN flip_linkutilisation.locationmaster lm ON lm.LocationID=bm.LocationID LEFT JOIN flip_linkutilisation.linktypemaster ltm ON ltm.LinkTypeID=ci.LinkType LEFT JOIN flip_linkutilisation.serviceprovidermaster spm ON spm.ServiceProviderID=ci.ServiceProviderID WHERE g.graphid=1252 order by value_max DESC ; END

DELIMITER ;

But In the out put the number of rows are doubled..
Row number coming correctly...but each row is repeating..
What mistake i did...
Please help me to solve

Thank you

这篇关于在mySql中添加RowNumber列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！