前两天产品有个需求，相同的商品因为价格不同而分开展示，但是明细还是算一条明细，具体区分展示出商品的价格和数量信息，其他重复的商品信息要置空。
需求并不难，用程序代码循环处理就可以了。但是后面涉及到打印报表，只能用纯sql语句生成。开始自己用了union 写的复杂了一些，后来百度了下文章看到一种实现思路，在此记录一下。下面的内容是根据其思路分析自己修改实现的一个demo，也简单扩展了一点实现功能。
直接上代码好了：
-- ---------------------------- -- Table structure for act -- ---------------------------- DROP TABLE IF EXISTS `act`; CREATE TABLE `act` ( `id` ) NOT NULL AUTO_INCREMENT COMMENT 'id', `name` ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'name', `type` ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'type', `peple` ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'peple', `age` ) NULL DEFAULT NULL COMMENT 'age', `sex` ) NULL DEFAULT NULL COMMENT 'sex', PRIMARY KEY (`id`) USING BTREE ) ENGINE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ---------------------------- -- Records of act -- ---------------------------- , , ); , , ); , , ); , , ); , , ); , , ); , , ); , , ); , , );
生成表数据：
下面案列是以type相同与否作为判断条件分组，peple就是type的小写，方便对照观察。后面的Bk结尾的别名字段也是方便对照观察加上的。
实现sql:
SELECT
id,
CASE
WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.name = a.name
GROUP BY type
) THEN name
WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.name = a.name
GROUP BY type
) THEN name
ELSE ''
END AS 'name' ,
CASE WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN type
WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN type
ELSE ''
END AS type,
CASE WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.age = a.age
GROUP BY type
) THEN age
WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.age = a.age
GROUP BY type
) THEN age
ELSE ''
END AS age,
CASE WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.sex = a.sex
GROUP BY type
) THEN sex
WHEN id in ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.sex = a.sex
GROUP BY type
) THEN sex
ELSE ''
END AS sex,
peple,name as nameBk,age as ageBk,sex as sexBk
FROM (select * from act order by type,id asc) as b
执行结果：
如果干脆要把任意字段置空（不管是不是重复的）也可以：
SELECT
id,
CASE
WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN name
WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN name
ELSE ''
END AS name,
CASE WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN type
WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN type
ELSE ''
END AS type,
CASE WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN age
WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN age
ELSE ''
END AS age,
CASE WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN sex
WHEN id = ( SELECT MIN(id)
FROM (select * from act) as a
WHERE b.type = a.type
GROUP BY type
) THEN sex
ELSE ''
END AS sex,
peple,name as nameBk,age as ageBk,sex as sexBk
FROM (select * from act order by type,id asc) as b
执行结果：
上面案例都是以type作为判断分组条件，如果是有多个字段，直接在后面接着增加就可以了。