select语句序号mysql_mysql为select结果集添加序号列

两个函数简单搞定

DELIMITER $$

USE `oso_isp`$$

DROP FUNCTION IF EXISTS `rownum_reset`$$

CREATE FUNCTION `rownum_reset`() RETURNS INT(11)

BEGIN

SET @rno = 0;

RETURN 1;

END$$

DELIMITER ;

DELIMITER $$

USE `oso_isp`$$

DROP FUNCTION IF EXISTS `rownum`$$

CREATE FUNCTION `rownum`() RETURNS INT(11)

BEGIN

/* 用法如：

SELECT

`rownum`(

) AS `id`,

t.*

FROM TABLE t

WHERE rownum_reset() = 1

*/

SET @rno = @rno + 1;

RETURN @rno;

END$$

DELIMITER ;

查询结果测试

测试代码：

SELECT

`rownum`(

)AS `row_num`

,`a`.`stock_in_date` AS `stock_in_date`

,`a`.`dinner_type` AS `dinner_type`

,`a`.`stockroom_id` AS `stockroom_id`

,SUM(`a`.`dinner_num`)

AS `dinner_num`

,SUM((SELECT SUM(`wh_stock_in_order_item`.`total_price`) FROM `wh_stock_in_order_item` WHERE (`wh_stock_in_order_item`.`stock_in_order_id` = `a`.`id`)))

AS `total_price`

,COUNT(0)

AS `order_num`

,GROUP_CONCAT(DISTINCT (SELECT `staff`.`name` FROM `staff` WHERE (`staff`.`id` = `a`.`buyer_id`)) SEPARATOR ',') -- 行变列

AS `buyer_name`

FROM `wh_stock_in_order` `a`

WHERE ((`a`.`is_deleted` = 0)

AND (`a`.`status` = 1)

AND (`rownum_reset`() = 1)) -- 初始化序号列从1开始

GROUP BY `a`.`stock_in_date`,`a`.`dinner_type`,`a`.`stockroom_id`

ORDER BY `a`.`stock_in_date`,`a`.`dinner_type`

返回结果：