sql分页排序后数据重复解决问题
select h.id,click_count,real_click,sort,show_start_time,show_end_time,h.status,h.index, b.id brandId,brand_name,category_tags_l1_name,category_tags_l2_name from join_brand_hot h left join join_brand b on h.brand_code = b.brand_code where 1 = 1 order by h.index desc, sort desc, click_count desc
再加一个Page进行分页
但发现第二页和第三页有两条数据重复了
解决办法是按照分数排序后,前三种相同的在按照其他方式排序保证分页数据不重复.
所以最后面在加一个id
select h.id,click_count,real_click,sort,show_start_time,show_end_time,h.status,h.index, b.id brandId,brand_name,category_tags_l1_name,category_tags_l2_name from join_brand_hot h left join join_brand b on h.brand_code = b.brand_code where 1 = 1 order by h.index desc, sort desc, click_count desc, h.id
SQL语句精选
SQL语句精选
修改字段描述
ALTER TABLE a MODIFY type tinyint(2) COMMENT ‘标准分类…’;
添加字段
ALTER TABLE a add COLUMN location_type tinyint(2) default NULL COMMENT ‘推荐标准类型…’;
扩大长度
ALTER TABLE a MODIFY COLUMN data_key varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT ‘数据key’;
创建表并索引 https://blog.csdn.net/ScarletMeCarzy/article/details/109091115
CREATE TABLE a ( dict_id bigint(19) NOT NULL AUTO_INCREMENT COMMENT ‘数据ID’, dict_group varchar(128) DEFAULT NULL, data_group_text varchar(128) NOT NULL COMMENT ‘数据组文本’, dict_type varchar(32) DEFAULT NULL COMMENT ‘数据类型1 文本 2 单选 3 多选’, data_key varchar(128) NOT NULL COMMENT ‘数据项’, data_text varchar(128) NOT NULL COMMENT ‘数据项’, data_value varchar(2048) DEFAULT NULL COMMENT ‘数据值’, gmt_create datetime DEFAULT NULL COMMENT ‘创建时间’, gmt_modified datetime DEFAULT NULL COMMENT ‘最后更新时间’, PRIMARY KEY (dict_id) USING BTREE, KEY DATA_NAME (data_text) USING BTREE, KEY DATA_TYPE (dict_type) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=658568 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT=‘数据字典’;
