mysql查询为每个空记录增加一个计数器或标记？

kmynzznz  于 2021-06-19  发布在  Mysql
关注(0)|答案(3)|浏览(331)

我的table结构如下。。有柱的
id number 我要添加列 counter 下面给出了所需的输出..如1,1,1,1，那么计数器需要在每一个空行上增加1..因此对于下一行，它的2,2,2…直到下一个空行..依此类推。。。如何为此编写查询？？任何帮助都将不胜感激。。提前谢谢。。
表结构：

CREATE TABLE `stack` (
  `id` int(11) NOT NULL auto_increment,
  `number` int(5) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=300 ;

--
-- Dumping data for table `stack`
--

INSERT INTO `stack` (`id`, `number`) VALUES
(1, 75201),
(2, 55008),
(3, 55007),
(4, 75222),
(5, 0),
(6, 74992),
(7, 14553),
(8, 54582),
(9, 54581),
(10, 74991),
(11, 14554),
(12, 0),
(13, 71413),
(14, 71414),
(15, 71415),
(16, 71416),
(17, 0),
(18, 59823),
(19, 59824),
(20, 59821),
(21, 59825),
(22, 59826),
(23, 0),
(24, 58220),
(25, 58702),
(26, 18247),
(27, 51753),
(28, 12854),
(29, 15160),
(30, 18248),
(31, 51606),
(32, 18478),
(33, 68747),
(34, 68749),
(35, 58221),
(36, 18233),
(37, 15159),
(38, 18234),
(39, 58701),
(40, 58222),
(41, 68748),
(42, 51754),
(43, 18477),
(44, 51605),
(45, 68750),
(46, 18235),
(47, 18235),
(48, 12853),
(49, 18236),
(50, 0),
(51, 56617),
(52, 16349),
(53, 56612),
(54, 56614),
(55, 56613),
(56, 56616),
(57, 56362),
(58, 56611),
(59, 56363),
(60, 56610),
(61, 56619),
(62, 56620),
(63, 56621),
(64, 16350),
(65, 0),
(66, 64590),
(67, 64153),
(68, 64162),
(69, 64588),
(70, 64587),
(71, 64156),
(72, 64159),
(73, 64589),
(74, 0),
(75, 19152),
(76, 59425),
(77, 12959),
(78, 59426),
(79, 19151),
(80, 12960),
(81, 0),
(82, 54809),
(83, 54810),
(84, 54826),
(85, 14813),
(86, 54703),
(87, 74835),
(88, 74836),
(89, 54704),
(90, 14814),
(91, 54825),
(92, 0),
(93, 56700),
(94, 16128),
(95, 56319),
(96, 56718),
(97, 16723),
(98, 16724),
(99, 56717),
(100, 56320),
(101, 16127),
(102, 56701),
(103, 0),
(104, 56261),
(105, 22625),
(106, 12691),
(107, 16086),
(108, 12639),
(109, 12680),
(110, 22649),
(111, 12609),
(112, 12679),
(113, 12640),
(114, 66020),
(115, 16089),
(116, 17616),
(117, 12687),
(118, 66019),
(119, 16220),
(120, 12675),
(121, 12608),
(122, 16219),
(123, 16021),
(124, 22650),
(125, 12692),
(126, 12610),
(127, 7115),
(128, 56262),
(129, 16022),
(130, 12688),
(131, 22626),
(132, 22688),
(133, 12607),
(134, 16090),
(135, 12676),
(136, 16085),
(137, 17615),
(138, 12687),
(139, 22687),
(140, 7116),
(141, 0),
(142, 38716),
(143, 38455),
(144, 38302),
(145, 38703),
(146, 38402),
(147, 38404),
(148, 38304),
(149, 38702),
(150, 38803),
(151, 38406),
(152, 38306),
(153, 38408),
(154, 38704),
(155, 38101),
(156, 38401),
(157, 38805),
(158, 38410),
(159, 38403),
(160, 38301),
(161, 38802),
(162, 38051),
(163, 38412),
(164, 38308),
(165, 38807),
(166, 38102),
(167, 38405),
(168, 38706),
(169, 38414),
(170, 38707),
(171, 38310),
(172, 38407),
(173, 38202),
(174, 38303),
(175, 38409),
(176, 38416),
(177, 38809),
(178, 38104),
(179, 38708),
(180, 38204),
(181, 38105),
(182, 38710),
(183, 38811),
(184, 38420),
(185, 38413),
(186, 38415),
(187, 38422),
(188, 38601),
(189, 38106),
(190, 38810),
(191, 38813),
(192, 38424),
(193, 38417),
(194, 38312),
(195, 38419),
(196, 38426),
(197, 38305),
(198, 38709),
(199, 38428),
(200, 38711),
(201, 38812),
(202, 38421),
(203, 38602),
(204, 38501),
(205, 38713),
(206, 38430),
(207, 58002),
(208, 38307),
(209, 38432),
(210, 38814),
(211, 38717),
(212, 38423),
(213, 38434),
(214, 38819),
(215, 38314),
(216, 38425),
(217, 38816),
(218, 38719),
(219, 38316),
(220, 38436),
(221, 38318),
(222, 38818),
(223, 38502),
(224, 38429),
(225, 38718),
(226, 38431),
(227, 38720),
(228, 38438),
(229, 38820),
(230, 38107),
(231, 38721),
(232, 38440),
(233, 38722),
(234, 38109),
(235, 38435),
(236, 68007),
(237, 38201),
(238, 38442),
(239, 38309),
(240, 38437),
(241, 38108),
(242, 38444),
(243, 38311),
(244, 38446),
(245, 38110),
(246, 38441),
(247, 38448),
(248, 38206),
(249, 38723),
(250, 38724),
(251, 38313),
(252, 38450),
(253, 38726),
(254, 38315),
(255, 38445),
(256, 38452),
(257, 38447),
(258, 38826),
(259, 38317),
(260, 38831),
(261, 38728),
(262, 38449),
(263, 38725),
(264, 38454),
(265, 38828),
(266, 38451),
(267, 38727),
(268, 38456),
(269, 38319),
(270, 38453),
(271, 38830),
(272, 38321),
(273, 0),
(274, 19016),
(275, 59050),
(276, 59547),
(277, 59548),
(278, 59049),
(279, 19015),
(280, 0),
(281, 52171),
(282, 52174),
(283, 52172),
(284, 52173),
(285, 0),
(286, 12343),
(287, 55713),
(288, 55749),
(289, 75718),
(290, 7525),
(291, 55750),
(292, 7526),
(293, 75722),
(294, 55751),
(295, 55714),
(296, 75717),
(297, 75721),
(298, 12344),
(299, 55752);

我想要的输出：

CREATE TABLE `stack` (
  `id` int(11) NOT NULL auto_increment,
  `counter` int(5) NOT NULL,
  `number` int(5) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=300 ;

--
-- Dumping data for table `stack`
--

INSERT INTO `stack` (`id`, `counter`, `number`) VALUES
(1, 1, 75201),
(2, 1, 55008),
(3, 1, 55007),
(4, 1, 75222),
(5, 2, 0),
(6, 2, 74992),
(7, 2, 14553),
(8, 2, 54582),
(9, 2, 54581),
(10, 2, 74991),
(11, 2, 14554),
(12, 3, 0),
(13, 3, 71413),
(14, 3, 71414),
(15, 3, 71415),
(16, 3, 71416),
(17, 4, 0),
(18, 4, 59823),
(19, 4, 59824),
(20, 4, 59821),
(21, 4, 59825),
(22, 4, 59826),
(23, 5, 0),
(24, 5, 58220),
(25, 5, 58702),
(26, 5, 18247),
(27, 5, 51753),
(28, 5, 12854),
(29, 5, 15160),
(30, 5, 18248),
(31, 5, 51606),
(32, 5, 18478),
(33, 5, 68747),
(34, 5, 68749),
(35, 5, 58221),
(36, 5, 18233),
(37, 5, 15159),
(38, 5, 18234),
(39, 5, 58701),
(40, 5, 58222),
(41, 5, 68748),
(42, 5, 51754),
(43, 5, 18477),
(44, 5, 51605),
(45, 5, 68750),
(46, 5, 18235),
(47, 5, 18235),
(48, 5, 12853),
(49, 5, 18236),
(50, 6, 0),
(51, 6, 56617),
(52, 6, 16349),
(53, 6, 56612),
(54, 6, 56614),
(55, 6, 56613),
(56, 6, 56616),
(57, 6, 56362),
(58, 6, 56611),
(59, 6, 56363),
(60, 6, 56610),
(61, 6, 56619),
(62, 6, 56620),
(63, 6, 56621),
(64, 6, 16350),
(65, 7, 0),
(66, 7, 64590),
(67, 7, 64153),
(68, 7, 64162),
(69, 7, 64588),
(70, 7, 64587),
(71, 7, 64156),
(72, 7, 64159),
(73, 7, 64589),
(74, 8, 0),
(75, 8, 19152),
(76, 8, 59425),
(77, 8, 12959),
(78, 8, 59426),
(79, 8, 19151),
(80, 8, 12960),
(81, 9, 0),
(82, 9, 54809),
(83, 9, 54810),
(84, 9, 54826),
(85, 9, 14813),
(86, 9, 54703),
(87, 9, 74835),
(88, 9, 74836),
(89, 9, 54704),
(90, 9, 14814),
(91, 9, 54825),
(92, 10, 0),
(93, 10, 56700),
(94, 10, 16128),
(95, 10, 56319),
(96, 10, 56718),
(97, 10, 16723),
(98, 10, 16724),
(99, 10, 56717),
(100, 10, 56320),
(101, 10, 16127),
(102, 10, 56701),
(103, 11, 0),
(104, 11, 56261),
(105, 11, 22625),
(106, 11, 12691),
(107, 11, 16086),
(108, 11, 12639),
(109, 11, 12680),
(110, 11, 22649),
(111, 11, 12609),
(112, 11, 12679),
(113, 11, 12640),
(114, 11, 66020),
(115, 11, 16089),
(116, 11, 17616),
(117, 11, 12687),
(118, 11, 66019),
(119, 11, 16220),
(120, 11, 12675),
(121, 11, 12608),
(122, 11, 16219),
(123, 11, 16021),
(124, 11, 22650),
(125, 11, 12692),
(126, 11, 12610),
(127, 11, 7115),
(128, 11, 56262),
(129, 11, 16022),
(130, 11, 12688),
(131, 11, 22626),
(132, 11, 22688),
(133, 11, 12607),
(134, 11, 16090),
(135, 11, 12676),
(136, 11, 16085),
(137, 11, 17615),
(138, 11, 12687),
(139, 11, 22687),
(140, 11, 7116),
(141, 12, 0),
(142, 12, 38716),
(143, 12, 38455),
(144, 12, 38302),
(145, 12, 38703),
(146, 12, 38402),
(147, 12, 38404),
(148, 12, 38304),
(149, 12, 38702),
(150, 12, 38803),
(151, 12, 38406),
(152, 12, 38306),
(153, 12, 38408),
(154, 12, 38704),
(155, 12, 38101),
(156, 12, 38401),
(157, 12, 38805),
(158, 12, 38410),
(159, 12, 38403),
(160, 12, 38301),
(161, 12, 38802),
(162, 12, 38051),
(163, 12, 38412),
(164, 12, 38308),
(165, 12, 38807),
(166, 12, 38102),
(167, 12, 38405),
(168, 12, 38706),
(169, 12, 38414),
(170, 12, 38707),
(171, 12, 38310),
(172, 12, 38407),
(173, 12, 38202),
(174, 12, 38303),
(175, 12, 38409),
(176, 12, 38416),
(177, 12, 38809),
(178, 12, 38104),
(179, 12, 38708),
(180, 12, 38204),
(181, 12, 38105),
(182, 12, 38710),
(183, 12, 38811),
(184, 12, 38420),
(185, 12, 38413),
(186, 12, 38415),
(187, 12, 38422),
(188, 12, 38601),
(189, 12, 38106),
(190, 12, 38810),
(191, 12, 38813),
(192, 12, 38424),
(193, 12, 38417),
(194, 12, 38312),
(195, 12, 38419),
(196, 12, 38426),
(197, 12, 38305),
(198, 12, 38709),
(199, 12, 38428),
(200, 12, 38711),
(201, 12, 38812),
(202, 12, 38421),
(203, 12, 38602),
(204, 12, 38501),
(205, 12, 38713),
(206, 12, 38430),
(207, 12, 58002),
(208, 12, 38307),
(209, 12, 38432),
(210, 12, 38814),
(211, 12, 38717),
(212, 12, 38423),
(213, 12, 38434),
(214, 12, 38819),
(215, 12, 38314),
(216, 12, 38425),
(217, 12, 38816),
(218, 12, 38719),
(219, 12, 38316),
(220, 12, 38436),
(221, 12, 38318),
(222, 12, 38818),
(223, 12, 38502),
(224, 12, 38429),
(225, 12, 38718),
(226, 12, 38431),
(227, 12, 38720),
(228, 12, 38438),
(229, 12, 38820),
(230, 12, 38107),
(231, 12, 38721),
(232, 12, 38440),
(233, 12, 38722),
(234, 12, 38109),
(235, 12, 38435),
(236, 12, 68007),
(237, 12, 38201),
(238, 12, 38442),
(239, 12, 38309),
(240, 12, 38437),
(241, 12, 38108),
(242, 12, 38444),
(243, 12, 38311),
(244, 12, 38446),
(245, 12, 38110),
(246, 12, 38441),
(247, 12, 38448),
(248, 12, 38206),
(249, 12, 38723),
(250, 12, 38724),
(251, 12, 38313),
(252, 12, 38450),
(253, 12, 38726),
(254, 12, 38315),
(255, 12, 38445),
(256, 12, 38452),
(257, 12, 38447),
(258, 12, 38826),
(259, 12, 38317),
(260, 12, 38831),
(261, 12, 38728),
(262, 12, 38449),
(263, 12, 38725),
(264, 12, 38454),
(265, 12, 38828),
(266, 12, 38451),
(267, 12, 38727),
(268, 12, 38456),
(269, 12, 38319),
(270, 12, 38453),
(271, 12, 38830),
(272, 12, 38321),
(273, 13, 0),
(274, 13, 19016),
(275, 13, 59050),
(276, 13, 59547),
(277, 13, 59548),
(278, 13, 59049),
(279, 13, 19015),
(280, 14, 0),
(281, 14, 52171),
(282, 14, 52174),
(283, 14, 52172),
(284, 14, 52173),
(285, 15, 0),
(286, 15, 12343),
(287, 15, 55713),
(288, 15, 55749),
(289, 15, 75718),
(290, 15, 7525),
(291, 15, 55750),
(292, 15, 7526),
(293, 15, 75722),
(294, 15, 55751),
(295, 15, 55714),
(296, 15, 75717),
(297, 15, 75721),
(298, 15, 12344),
(299, 15, 55752);
sqlmysqlCounterMarker

3条答案

按热度按时间
zazmityj

zazmityj1#

您可以尝试声明一个变量 @Val 与 CASE WHEN 为了成功。
使用 CASE WHEN 判断 number . 如果 number 是 0 或者 null 一定要积累经验 @Val 架构（mysql v5.7）

CREATE TABLE `stack` (
  `id` int(11) NOT NULL auto_increment,
  `number` int(5) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Dumping data for table `stack`
--

INSERT INTO `stack` (`id`, `number`) VALUES
(1, 52201),
(2, 53008),
(3, 55007),
(4, 75222),
(5, 0),
(6, 74992),
(7, 14553),
(8, 54582),
(9, 54581),
(10, 74991),
(11, 14554),
(12, 0),
(13, 11413),
(14, 72414),
(15, 31415),
(16, 71416),
(17, 0),
(18, 59823),
(19, 69824),
(20, 59821),
(21, 69825),
(22, 59826);

查询#1

SET @Val= 1;

没有要显示的结果。
查询#2

SELECT id,  
      (CASE WHEN coalesce(number,0) = 0 THEN @Val:=@Val+1 ELSE @Val END) counter,
      number
FROM `stack` t1 
order by id;

| id  | number | counter |
| --- | ------ | ------- |
| 1   | 52201  | 1       |
| 2   | 53008  | 1       |
| 3   | 55007  | 1       |
| 4   | 75222  | 1       |
| 5   | 0      | 2       |
| 6   | 74992  | 2       |
| 7   | 14553  | 2       |
| 8   | 54582  | 2       |
| 9   | 54581  | 2       |
| 10  | 74991  | 2       |
| 11  | 14554  | 2       |
| 12  | 0      | 3       |
| 13  | 11413  | 3       |
| 14  | 72414  | 3       |
| 15  | 31415  | 3       |
| 16  | 71416  | 3       |
| 17  | 0      | 4       |
| 18  | 59823  | 4       |
| 19  | 69824  | 4       |
| 20  | 59821  | 4       |
| 21  | 69825  | 4       |
| 22  | 59826  | 4       |

db fiddle视图

赞(0）分享  回复(0）举报  2021-06-20
2vuwiymt

2vuwiymt2#

您也可以通过简单地编写一个过程来实现这一点
1） 首先，可以通过添加列计数器来改变表结构

ALTER TABLE stack ADD COLUMN counter  INT NOT NULL DEFAULT  0;

2） 然后创建一个过程，如下所示：

DELIMITER $$
CREATE PROCEDURE `updateVal_Counter`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE result INT DEFAULT 1;
    DECLARE a,b,id1  INT;
    DECLARE cur1 CURSOR FOR SELECT id,number,counter FROM `test`.`stack`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;
    read_loop: 
    LOOP
    FETCH cur1 INTO id1,a,b;
    IF a = 0 THEN
      SET result  = result + 1;
    END IF;
    IF done THEN
    LEAVE read_loop;
    END IF;
    UPDATE `stack` SET counter =result WHERE number=a AND id = id1;
    END LOOP;
    CLOSE cur1;
    END$$
DELIMITER ;

3） 最后将过程调用为

call updateVal_Counter;

这将在表结构中添加一个具有所需值的新列。

赞(0）分享  回复(0）举报  2021-06-19
sycxhyv7

sycxhyv73#

您可以模拟行号功能（对于mysql的旧版本；版本<8.0）。
尝试以下查询（sql fiddle演示）：

SET @row_number = 1;    
SELECT 
    id, 
    number, 
    @row_number:= IF(IFNULL(number,0) = 0, 
                     @row_number + 1, 
                     @row_number) AS counter
FROM
    stack 
ORDER BY id ASC

注：
上述代码还将处理以下情况： number 字段值为 null 而不是0。
如果某个特定的 number 是 null .
利用if（）函数，我们可以检查当前值是否为0，并相应地更新计数器。

赞(0）分享  回复(0）举报  2021-06-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com