mysql 组合排序 排名。已有shop_id,item_id,num三列

同学遇到的一个面试题，hivesql没有接触过，用sql实现一下

其实一开始没有看懂“a”这列代表什么意思，后来想明白，按照shop_id分组num求和之后，item_id在总和的百分比。

而“b”代表的就是 num在每个分组的排序，并且从小到大。

mysql 5.7版本

创建数据表

CREATE TABLE `test02`  (
  `shop_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `item_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `num` int(11) DEFAULT NULL
) ;

INSERT INTO `test02` VALUES ('A', 'a', 10);
INSERT INTO `test02` VALUES ('A', 'b', 12);
INSERT INTO `test02` VALUES ('B', 'a', 8);
INSERT INTO `test02` VALUES ('A', 'c', 5);
INSERT INTO `test02` VALUES ('B', 'c', 8);
INSERT INTO `test02` VALUES ('C', 'b', 9);

然后实现第一步

求a列

SELECT
    t.shop_id,
    t.item_id,
    t.num,
    TRUNCATE (num / c.s, 2) a
FROM
    test02 t
LEFT JOIN (
    SELECT
        sum(num) s,
        shop_id sc
    FROM
        test02
    GROUP BY
        shop_id
) AS c ON t.shop_id = c.sc

执行结果：

然后求b列，b列值相当于按照shop_id 进行分组，num在组里的大小排序，此处倒序

例如，按照A进行分组，item_id的num=12 最大，所以它对应的b为1，其次a为2，c为3，B组中，a和c的num值相同，所以都为1

所以我们声明三个个变量，一个是 @i ，@s_shop_id，@s_num

当s_shop_id等于当前的shop_id，并且@s_num大于当前的num，那么@i 加一，否则就等于1。

SET @i=1;
SELECT
	t.shop_id,
	t.item_id,
	t.num,
	TRUNCATE (num / c.s, 2) a,
	(
		@i := CASE
		WHEN @s_shop_id = t.shop_id
		AND @s_num > t.num THEN
			@i + 1
		ELSE
			1
		END
	) b,
	@s_shop_id := t.shop_id ,@s_num := t.num ,@s_shop_id ,@s_num
FROM
	test02 t
LEFT JOIN (
	SELECT
		sum(num) s,
		shop_id sc
	FROM
		test02
	GROUP BY
		shop_id
) AS c ON t.shop_id = c.sc
ORDER BY
	t.shop_id,
	t.num DESC

但是同样的sql，执行第一遍和第二遍的效果完全不一样。

执行第一遍结果：

执行第二遍结果

往后每次执行效果都与第二遍执行结果一致，但是第一次执行就不一样，我也不知道为什么，希望有大神来指导。

这样就求出啦a列和b列。

不好意思，刚才我感觉这个数据漏洞，例如B组数据，a和c值并列第一可以，如果并列第二呢？于是我把最后一条数据的shop_id值改成了B。数据如下：

按照逻辑，那么B组的最大值为item_id=b,num=9，那么B组的 a c,就并列第二

按照上边的sql执行结果如下：

按照逻辑，B组中 b最大，a和c并列，a和c 在b列值都应该为1，所以重新修改sql

SELECT
	t.shop_id,
	t.item_id,
	t.num,
	TRUNCATE (num / c.s, 2) a,
	(
		@i := CASE
		WHEN @s_shop_id = t.shop_id THEN
			CASE
		WHEN @s_num > t.num THEN
			@i + 1
		ELSE
			@i
		END
		ELSE
			1
		END
	) b,
	@s_shop_id := t.shop_id ,@s_num := t.num ,@s_shop_id ,@s_num
FROM
	test02 t
LEFT JOIN (
	SELECT
		sum(num) s,
		shop_id sc
	FROM
		test02
	GROUP BY
		shop_id
) AS c ON t.shop_id = c.sc
ORDER BY
	t.shop_id,
	t.num DESC

这样执行效果如图：

数据改回原先的，也没有问题，大功告成，只是第一遍执行效果，和之后的执行效果依旧不一样。不知道为什么。