sql 组内1层排序，双层排序，三层排序

客官°小女子只卖身不卖艺 2022-08-11 03:25 217阅读 0赞

select username,todaydate from  (select username as newname, max(todaydate)  as newdate from test  
group by  username  
order by newdate desc) as t1,test as t2  
where t2.username = t1.newname  
order by t1.newdate desc,t2.todaydate desc

SET FOREIGN\_KEY\_CHECKS=0;  
\-- ----------------------------  
\-- Table structure for test  
\-- ----------------------------  
DROP TABLE IF EXISTS \`test\`;  
CREATE TABLE \`test\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`username\` char(5) DEFAULT NULL,  
  \`todaydate\` char(3) DEFAULT NULL,  
  PRIMARY KEY (\`id\`)  
) ENGINE=MyISAM AUTO\_INCREMENT=7 DEFAULT CHARSET=utf8;

\-- ----------------------------  
\-- Records  
\-- ----------------------------  
INSERT INTO \`test\` VALUES ('1', 'fan', '1');  
INSERT INTO \`test\` VALUES ('2', 'qin', '2');  
INSERT INTO \`test\` VALUES ('3', 'fan', '3');  
INSERT INTO \`test\` VALUES ('4', 'li', '5');  
INSERT INTO \`test\` VALUES ('5', 'fan', '6');  
INSERT INTO \`test\` VALUES ('6', 'qin', '7');

qin 7  
qin 2  
fan 6  
fan 3  
fan 1  
li 5

2. 双层排序

select t1.newname,t2.todaydate,t2.nextdate from(  
select username as newname, max(todaydate) as newdate,max(nextdate) as nextdate from test  
group by  username  
order by newdate desc,nextdate desc  // ke yi delete  
) as t1 , test as t2  
where t1.newname = t2.username  
order by t1.newdate desc,t1.nextdate desc,t2.todaydate desc

li 6 7  
qin 6 6  
qin 2 2  
fan 6 5  
fan 3 3  
fan 1 1

CREATE TABLE \`test\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`username\` char(5) DEFAULT NULL,  
  \`todaydate\` char(3) DEFAULT NULL,  
  \`nextdate\` char(3) DEFAULT NULL,  
  PRIMARY KEY (\`id\`)  
)

3. 三层排序

select t1.newname,t2.todaydate,t2.nextdate,t2.threedate from(  
select username as newname, max(todaydate) as newdate,max(nextdate) as nextdate,max(threedate) as threedate from test  
group by  username  
order by newdate desc,nextdate desc,threedate desc // ke yi delete  
) as t1 , test as t2  
where t1.newname = t2.username  
order by t1.newdate desc,t1.nextdate desc,t1.threedate desc,t2.todaydate desc

fan 6 7 5  
fan 6 3 3  
fan 2 1 1  
li 6 7 4  
qin 6 6 6  
qin 6 3 7  
qin 2 2 2