语句: select uid,num from ( select b.uid,@rownum:=@rownum+1 , if(@pdept=b.uid,@rank:=@rank+1,@rank:=1) as num, @pdept:=b.uid from ( select uid from test order by uid ) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result 结果:
SET FOREIGN_KEY_CHECKS=0;-- ---------------------------- -- Table structure for `a` -- ---------------------------- DROP TABLE IF EXISTS `a`; CREATE TABLE `a` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned NOT NULL DEFAULT '0', `xx` int(11) DEFAULT NULL, `x1` int(11) DEFAULT NULL, `x2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index` (`uid`,`x1`,`x2`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='消息中心表';-- ---------------------------- -- Records of a -- ---------------------------- INSERT INTO `a` VALUES ('1', '1', '1', '1', '1'); INSERT INTO `a` VALUES ('2', '1', null, '1', '1'); INSERT INTO `a` VALUES ('3', '1', null, '1', '1'); INSERT INTO `a` VALUES ('4', '2', null, '2', '2'); INSERT INTO `a` VALUES ('5', '2', null, '2', '2'); INSERT INTO `a` VALUES ('6', '3', null, '3', '3'); INSERT INTO `a` VALUES ('7', '3', null, '3', '3'); INSERT INTO `a` VALUES ('8', '3', null, '3', '3'); INSERT INTO `a` VALUES ('9', '3', null, '3', '3'); INSERT INTO `a` VALUES ('10', '3', null, '3', '3'); INSERT INTO `a` VALUES ('11', '4', null, '4', '4'); INSERT INTO `a` VALUES ('18', '1', null, null, null); INSERT INTO `a` VALUES ('19', '2', null, null, null); INSERT INTO `a` VALUES ('20', '3', null, null, null); INSERT INTO `a` VALUES ('21', '4', null, null, null); INSERT INTO `a` VALUES ('22', '5', null, null, null); INSERT INTO `a` VALUES ('23', '6', null, null, null); INSERT INTO `a` VALUES ('24', '7', null, null, null); INSERT INTO `a` VALUES ('25', '8', null, null, null); INSERT INTO `a` VALUES ('26', '9', null, null, null); INSERT INTO `a` VALUES ('27', '10', null, null, null); INSERT INTO `a` VALUES ('28', '11', null, null, null); INSERT INTO `a` VALUES ('29', '12', null, null, null); INSERT INTO `a` VALUES ('30', '13', null, null, null); INSERT INTO `a` VALUES ('31', '14', null, null, null); INSERT INTO `a` VALUES ('32', '1', null, null, null); INSERT INTO `a` VALUES ('33', '2', null, null, null); INSERT INTO `a` VALUES ('45', '4', null, null, null); INSERT INTO `a` VALUES ('46', '5', null, null, null); INSERT INTO `a` VALUES ('47', '3', null, null, null); INSERT INTO `a` VALUES ('48', '2', null, null, null); INSERT INTO `a` VALUES ('1990', '2', null, null, null); INSERT INTO `a` VALUES ('1991', '3', null, null, null); INSERT INTO `a` VALUES ('1992', '11', null, null, null); INSERT INTO `a` VALUES ('1995', '10', null, null, null); INSERT INTO `a` VALUES ('2665', '3', null, null, null); INSERT INTO `a` VALUES ('2882', '6', null, null, null); INSERT INTO `a` VALUES ('2883', '7', null, null, null); INSERT INTO `a` VALUES ('2884', '8', null, null, null); INSERT INTO `a` VALUES ('2885', '9', null, null, null); INSERT INTO `a` VALUES ('2888', '4', null, null, null); INSERT INTO `a` VALUES ('2999', '5', null, null, null); 以上是SQL文件。然后我执行SQL语句:select id,uid,num from ( select b.id,b.uid,@rownum:=@rownum+1 , if(@pdept=b.uid,@rank:=@rank+1,@rank:=1) as num, @pdept:=b.uid from ( select id,uid from a order by uid ) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result ORDER BY uid asc, num asc 得到的结果 如下: num字段没有按id正序排序对应上。有办法解决吗
select id,uid,num from ( select b.id,b.uid,@rownum:=@rownum+1 , if(@pdept=b.uid,@rank:=@rank+1,@rank:=1) as num, @pdept:=b.uid from ( select id,uid from a order by uid ) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result这是我的SQL语句。 最上面的我的SQL文件
select uid,num from (
select b.uid,@rownum:=@rownum+1 ,
if(@pdept=b.uid,@rank:=@rank+1,@rank:=1) as num,
@pdept:=b.uid
from (
select uid from test order by uid
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
结果:
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `a`
-- ----------------------------
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned NOT NULL DEFAULT '0',
`xx` int(11) DEFAULT NULL,
`x1` int(11) DEFAULT NULL,
`x2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index` (`uid`,`x1`,`x2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='消息中心表';-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1', '1', '1', '1', '1');
INSERT INTO `a` VALUES ('2', '1', null, '1', '1');
INSERT INTO `a` VALUES ('3', '1', null, '1', '1');
INSERT INTO `a` VALUES ('4', '2', null, '2', '2');
INSERT INTO `a` VALUES ('5', '2', null, '2', '2');
INSERT INTO `a` VALUES ('6', '3', null, '3', '3');
INSERT INTO `a` VALUES ('7', '3', null, '3', '3');
INSERT INTO `a` VALUES ('8', '3', null, '3', '3');
INSERT INTO `a` VALUES ('9', '3', null, '3', '3');
INSERT INTO `a` VALUES ('10', '3', null, '3', '3');
INSERT INTO `a` VALUES ('11', '4', null, '4', '4');
INSERT INTO `a` VALUES ('18', '1', null, null, null);
INSERT INTO `a` VALUES ('19', '2', null, null, null);
INSERT INTO `a` VALUES ('20', '3', null, null, null);
INSERT INTO `a` VALUES ('21', '4', null, null, null);
INSERT INTO `a` VALUES ('22', '5', null, null, null);
INSERT INTO `a` VALUES ('23', '6', null, null, null);
INSERT INTO `a` VALUES ('24', '7', null, null, null);
INSERT INTO `a` VALUES ('25', '8', null, null, null);
INSERT INTO `a` VALUES ('26', '9', null, null, null);
INSERT INTO `a` VALUES ('27', '10', null, null, null);
INSERT INTO `a` VALUES ('28', '11', null, null, null);
INSERT INTO `a` VALUES ('29', '12', null, null, null);
INSERT INTO `a` VALUES ('30', '13', null, null, null);
INSERT INTO `a` VALUES ('31', '14', null, null, null);
INSERT INTO `a` VALUES ('32', '1', null, null, null);
INSERT INTO `a` VALUES ('33', '2', null, null, null);
INSERT INTO `a` VALUES ('45', '4', null, null, null);
INSERT INTO `a` VALUES ('46', '5', null, null, null);
INSERT INTO `a` VALUES ('47', '3', null, null, null);
INSERT INTO `a` VALUES ('48', '2', null, null, null);
INSERT INTO `a` VALUES ('1990', '2', null, null, null);
INSERT INTO `a` VALUES ('1991', '3', null, null, null);
INSERT INTO `a` VALUES ('1992', '11', null, null, null);
INSERT INTO `a` VALUES ('1995', '10', null, null, null);
INSERT INTO `a` VALUES ('2665', '3', null, null, null);
INSERT INTO `a` VALUES ('2882', '6', null, null, null);
INSERT INTO `a` VALUES ('2883', '7', null, null, null);
INSERT INTO `a` VALUES ('2884', '8', null, null, null);
INSERT INTO `a` VALUES ('2885', '9', null, null, null);
INSERT INTO `a` VALUES ('2888', '4', null, null, null);
INSERT INTO `a` VALUES ('2999', '5', null, null, null);
以上是SQL文件。然后我执行SQL语句:select id,uid,num from (
select b.id,b.uid,@rownum:=@rownum+1 ,
if(@pdept=b.uid,@rank:=@rank+1,@rank:=1) as num,
@pdept:=b.uid
from (
select id,uid from a order by uid
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
ORDER BY uid asc, num asc
得到的结果 如下:
num字段没有按id正序排序对应上。有办法解决吗
加上组合索引(uid_x1_x2)后,会导致顺序错乱。如上图:
我想是这样的
id为1的num=1
id为18的num=2
id为32的num=3
select id,uid,num from (
select b.id,b.uid,@rownum:=@rownum+1 ,
if(@pdept=b.uid,@rank:=@rank+1,@rank:=1) as num,
@pdept:=b.uid
from (
select id,uid from a order by uid
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result这是我的SQL语句。
最上面的我的SQL文件
你的表数据和我的不一样。你用我的SQL文件创建表试试。然后执行这个SQL。
你的表数据和我的不一样。你用我的SQL文件创建表试试。然后执行这个SQL。
你最后想按那个排序?是id升序,还是num升序?如果是按照id排列可能是下边这种情况
id num
1 10
2 15
3 19如果是按num升序,就是你的那种情况了。因为num都是按照相同的uid自增生成的,没有和id有什么关系。
你的表数据和我的不一样。你用我的SQL文件创建表试试。然后执行这个SQL。
你最后想按那个排序?是id升序,还是num升序?如果是按照id排列可能是下边这种情况
id num
1 10
2 15
3 19如果是按num升序,就是你的那种情况了。因为num都是按照相同的uid自增生成的,没有和id有什么关系。
是想按num升序。即,相同uid的情况下,id最小的 num也最小(为1)。想要的结果:
id uid num
1 1 1
2 1 2
3 1 3
......
4 2 1
5 2 2
......
96 3 1
99 3 2