一条 SQL 查询排名

最新推荐文章于 2023-12-26 08:15:51 发布

blackcat王文俊

最新推荐文章于 2023-12-26 08:15:51 发布

阅读量179

点赞数

分类专栏：程序代码文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/blackcat123/article/details/126800634

版权

程序代码专栏收录该内容

17 篇文章 0 订阅

订阅专栏

CREATE TABLE `osx_introducer_relation` (
  `ir_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ir_uid` varchar(10) DEFAULT NULL,
  `ir_bind_leader` int(10) NOT NULL DEFAULT '0' COMMENT '被介绍团长的uid',
  `ir_create_at` bigint(30) NOT NULL DEFAULT '0',
  `ir_update_at` bigint(30) NOT NULL DEFAULT '0',
  `ir_del_at` bigint(30) NOT NULL DEFAULT '0',
  `ir_create_by` varchar(255) NOT NULL DEFAULT '',
  `ir_del_by` varchar(255) NOT NULL DEFAULT '',
  `ir_first_percent` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '首单返佣百分比',
  `ir_unit_reward` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '非首单返现金额',
  `ir_order_num` int(10) NOT NULL DEFAULT '0' COMMENT '被介绍团队下产生的订单数量',
  `ir_unit_num` int(10) NOT NULL DEFAULT '0' COMMENT '被介绍团队下购买的产品数量',
  `ir_last_back` bigint(30) NOT NULL DEFAULT '0' COMMENT '被介绍团队最近返佣时间',
  `ir_total_score` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '被介绍团队下产生的积分总数',
  `ir_total_price` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '被介绍团队产生订单金额',
  `ir_status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '关系状态，1正常2过期',
  `ir_remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`ir_id`) USING BTREE,
  KEY `osx_introducer_relation_ir_uid_IDX` (`ir_uid`,`ir_bind_leader`,`ir_create_at`,`ir_del_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4345 DEFAULT CHARSET=utf8mb4 COMMENT='介绍人绑定关系表'



INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('270', 270, 1626757950, 0, 0, '', '', 5.00, 10.00, 0, 0, 1626757950, 0.00, 0.00, 2, '过期处理');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('200', 1440, 1626770132, 0, 0, '', '', 5.00, 10.00, 0, 0, 1626770132, 0.00, 0.00, 2, '过期处理');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('19', 3781, 1627273843, 1643385601, 0, '', '', 5.00, 10.00, 10, 38, 1643385601, 507.92, 19971.60, 1, '');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('19', 3439, 1627273869, 0, 0, '', '', 5.00, 10.00, 0, 0, 1627273869, 0.00, 0.00, 2, '过期处理');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('4430', 4561, 1627380381, 0, 1627383559, '', '', 5.00, 10.00, 0, 0, 1627380381, 0.00, 0.00, 1, '后台删除');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('1513', 4592, 1627543565, 0, 0, '', '', 5.00, 10.00, 0, 0, 1627543565, 0.00, 0.00, 2, '过期处理');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('1513', 3234, 1627868707, 0, 1627900214, '', '', 5.00, 10.00, 0, 0, 1627868707, 0.00, 0.00, 1, '后台删除');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('1513', 2880, 1627868724, 0, 1627900209, '', '', 5.00, 10.00, 0, 0, 1627868724, 0.00, 0.00, 1, '后台删除');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('3843', 4822, 1629437386, 1632322276, 0, '', '', 5.00, 10.00, 1, 1, 1632322276, 13.76, 275.20, 1, '');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('4750', 2672, 1631541516, 0, 0, '', '', 5.00, 10.00, 0, 0, 1631541516, 0.00, 0.00, 1, '');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('3031', 4750, 1631541556, 0, 0, '', '', 5.00, 10.00, 0, 0, 1631541556, 0.00, 0.00, 1, '');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('1520', 5065, 1632302851, 0, 0, '', '', 5.00, 10.00, 0, 0, 1632302851, 0.00, 0.00, 1, '');
INSERT INTO osx_introducer_relation
(ir_uid, ir_bind_leader, ir_create_at, ir_update_at, ir_del_at, ir_create_by, ir_del_by, ir_first_percent, ir_unit_reward, ir_order_num, ir_unit_num, ir_last_back, ir_total_score, ir_total_price, ir_status, ir_remark)
VALUES('896', 5081, 1632636060, 0, 0, '', '', 5.00, 10.00, 0, 0, 1632636060, 0.00, 0.00, 1, '');




select
	  t.*, @rowNum := @rowNum + 1 as row_num
from
      (SELECT (@rowNum := 0)) as rn,
      (
          select
  	          ir_uid as uid,
		      count(*) as total_count
	      from
		      osx_introducer_relation oir
	      where ir_del_at=0
	      group by ir_uid
       ) as t
order by t.total_count desc