Mysql重复数据查询置为空
2023-09-14 09:07:51 时间
前两天产品有个需求,相同的商品因为价格不同而分开展示,但是明细还是算一条明细,具体区分展示出商品的价格和数量信息,其他重复的商品信息要置空。
需求并不难,用程序代码循环处理就可以了。但是后面涉及到打印报表,只能用纯sql语句生成。开始自己用了union 写的复杂了一些,后来百度了下文章看到一种实现思路,在此记录一下。下面的内容是根据其思路分析自己修改实现的一个demo,也简单扩展了一点实现功能。
直接上代码好了:
-- ---------------------------- -- Table structure for act -- ---------------------------- DROP TABLE IF EXISTS `act`; CREATE TABLE `act` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id', `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'name', `type` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'type', `peple` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'peple', `age` int(3) NULL DEFAULT NULL COMMENT 'age', `sex` int(1) NULL DEFAULT NULL COMMENT 'sex', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ---------------------------- -- Records of act -- ---------------------------- INSERT INTO `act` VALUES (1, '张三', 'A', 'a', 10, 1); INSERT INTO `act` VALUES (2, '张三', 'A', 'a', 10, 0); INSERT INTO `act` VALUES (3, '王五', 'B', 'b', 10, 1); INSERT INTO `act` VALUES (4, '赵六', 'B', 'b', 20, 0); INSERT INTO `act` VALUES (5, '刘七', 'C', 'c', 30, 1); INSERT INTO `act` VALUES (6, '阿三', 'D', 'd', 10, 0); INSERT INTO `act` VALUES (7, '阿四', 'D', 'd', 20, 0); INSERT INTO `act` VALUES (8, '阿五', 'A', 'a', 30, 1); INSERT INTO `act` VALUES (9, '张三', 'A', 'a', 30, 1);
生成表数据:
下面案列是以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 HAVING COUNT(*) > 1 ) THEN name WHEN id in ( SELECT MIN(id) FROM (select * from act) as a WHERE b.name = a.name GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) >= 1 ) THEN type WHEN id in ( SELECT MIN(id) FROM (select * from act) as a WHERE b.type = a.type GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) >= 1 ) THEN age WHEN id in ( SELECT MIN(id) FROM (select * from act) as a WHERE b.age = a.age GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) >= 1 ) THEN sex WHEN id in ( SELECT MIN(id) FROM (select * from act) as a WHERE b.sex = a.sex GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) > 1 ) THEN name WHEN id = ( SELECT MIN(id) FROM (select * from act) as a WHERE b.type = a.type GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) >= 1 ) THEN type WHEN id = ( SELECT MIN(id) FROM (select * from act) as a WHERE b.type = a.type GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) >= 1 ) THEN age WHEN id = ( SELECT MIN(id) FROM (select * from act) as a WHERE b.type = a.type GROUP BY type HAVING COUNT(*) = 1 ) 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 HAVING COUNT(*) >= 1 ) THEN sex WHEN id = ( SELECT MIN(id) FROM (select * from act) as a WHERE b.type = a.type GROUP BY type HAVING COUNT(*) = 1 ) 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作为判断分组条件,如果是有多个字段,直接在后面接着增加就可以了。
相关文章
- 【MYSQL】如何下载mysql驱动jar包
- MySQL中使用日期索引优化性能(mysql日期索引)
- MySQL数据库实时同步:实现数据零距离同步(mysql数据库实时同步)
- 数据MySQL数据库快速插入数据指南(mysql数据库插入)
- MySQL中的索引:提升查询性能的利器(mysql中索引是什么)
- 类型精通MySQL中的二进制数据类型(mysql二进制数据)
- 如何存储MySQL中二进制数据存储方式研究(mysql二进制数据)
- MySQL数据监控工具推荐(mysql数据监控工具)
- MySQL如何修改字段类型(mysql 修改字段类型)
- MySQL联表查询实现数据分析(mysql中两表联合查询)
- MYSQL中ID的类型解析(mysql 中id的类型)
- 使用C语言操作MySQL查询出最佳结果(c mysql 查询结果)
- MySQL联表查询简介(mysql两表联立查询)
- MySQL实现两行数据相除操作(mysql两行数据相除)
- bc跨服架构搭建Mysql数据库的实践(bc跨服mysql搭建)
- 数据360强力清除Mysql数据,实现一键迅速删除(360强力删除mysql)
- MySQL删除语法快速清除无用数据(mysql中删除语法)
- MySQL插入数据操作详解(mysql。insert)
- MySQL和XML 如何高效存储数据(mysql xml 存储)
- MySQL数据库不在服务器上,该怎么办(mysql不在服务器上)
- MySQL时间精度避免使用时分秒(mysql不要时分秒)
- MySQL数据 如何获取上周日期(mysql 上周日期)
- 华为云将MySQL数据上云,更安全更高效(mysql上云华为云)
- 解决MySQL连接问题(mysql不给连接)