今天,接到了这样一个需求,有这样一张表:
要求,做到下面这样的效果:
表结构及示例数据如下:
CREATE TABLE `merchant_vip` (
`id_merchant_vip` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(50) NOT NULL COMMENT '会员名称',
`money` INT(11) NOT NULL COMMENT '消费达标门槛',
PRIMARY KEY (`id_merchant_vip`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='等级表';
insert into `merchant_vip` (`id_merchant_vip`, `name`, `money`) values('7','新手','0');
insert into `merchant_vip` (`id_merchant_vip`, `name`, `money`) values('8','普通','200');
insert into `merchant_vip` (`id_merchant_vip`, `name`, `money`) values('9','银卡','500');
insert into `merchant_vip` (`id_merchant_vip`, `name`, `money`) values('10','金卡','1000');
insert into `merchant_vip` (`id_merchant_vip`, `name`, `money`) values('11','白金','3000');
insert into `merchant_vip` (`id_merchant_vip`, `name`, `money`) values('12','钻石','10000');
本来想用行转列的方式去做,结果发现行不通,只好另想办法,写了sql:
SELECT
a.id_merchant_vip,
a. NAME,
a.money,
b.money AS money_max
FROM
(
SELECT
@rownum :=@rownum + 1 AS rn1,
t.*
FROM
(SELECT @rownum := 0) r,
merchant_vip t
) a
LEFT JOIN (
SELECT
@rownums :=@rownums + 1 AS rn2,
t.*
FROM
(SELECT @rownums := 0) r,
merchant_vip t
WHERE
money > 0
ORDER BY
money
) b ON a.rn1 = b.rn2;
SELECT
*
FROM
merchant_vip;
嗯,思路就是先按money字段排序,然后使用rownum变量存储数据编号,过滤掉第一条记录,
然后错行进行2张表的左连接,拼装好数据。