博客这东西更像个人的备忘录,最近发现不管博文长短,遇到问题总结一下是很开心的,而且会加深印象,这里记录一下最近遇到的问题以及如何解决的(其实挺简单的一个问题,由于以前没有写博文的习惯并且都是看别人博文,所以用过就忘了)
CREATE TABLE `cms_resource_set_resource` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',`channel_code` varchar(64) DEFAULT NULL COMMENT '渠道编码',
`res_set_code` varchar(64) DEFAULT NULL COMMENT '资源集编码',
`code` varchar(255) DEFAULT NULL COMMENT '文件名字、编码',
`res_code` varchar(64) NOT NULL COMMENT '资源集中的资源编码',
`play_type` int(11) DEFAULT NULL COMMENT '播放类型(0-必播,1-选播)',
`order_id` int(11) DEFAULT NULL COMMENT '资源集中的资源排序ID',
`status` varchar(255) DEFAULT '0' COMMENT '0 初始 1 待审 2 通过 3 拒绝 4 H5链接过期 5 撤回 6 版权到期',
`url_thumb` varchar(255) DEFAULT NULL COMMENT '图片缩略图',
`channel_category_id` varchar(64) DEFAULT NULL COMMENT '渠道厂商分类ID',
`channel_category_name` varchar(64) DEFAULT NULL COMMENT '渠道厂商分类名称',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `channel_code` (`channel_code`,`res_set_code`,`res_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='组图资源表';
CREATE TABLE `cms_resource_langs` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`channel_code` varchar(64) DEFAULT NULL COMMENT '渠道编码',
`res_set_code` varchar(64) DEFAULT NULL COMMENT '资源集编码',
`res_code` varchar(64) DEFAULT NULL COMMENT '资源编码',
`res_title_code` varchar(64) DEFAULT NULL COMMENT '图文编码',
`lang` varchar(64) DEFAULT 'zh-CN' COMMENT '多语言类型,参考 ISO639-1 默认zh-CN',
`title` varchar(256) DEFAULT NULL COMMENT '标题',
`content` varchar(1024) DEFAULT NULL COMMENT '内容,广告语',
`btn` varchar(2048) DEFAULT NULL COMMENT '客户端链接显示名称',
`source` varchar(256) DEFAULT NULL COMMENT '来源',
`h5` varchar(2048) DEFAULT NULL COMMENT '跳转h5链接',
`view_url` varchar(2048) DEFAULT NULL COMMENT '曝光URL,广告展示统计url',
`status` varchar(255) DEFAULT '0' COMMENT '图文状态0 初始 1 待审 2 通过 3 拒绝 4 H5链接过期 5 撤回',
`start_time` datetime DEFAULT NULL COMMENT 'h5链接有效开始时间',
`end_time` datetime DEFAULT NULL COMMENT 'h5链接有效结束时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `channel_code` (`channel_code`,`res_set_code`,`res_code`,`lang`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='资源图文表/组图图文表';
cms_resource_set_resource 存放单图和组图的信息,存储方式为渠道、组图ID、图ID组合在一起查询唯一数据
cms_resource_langs 存放图应的多语言信息,并不是每张图都有多语言信息,并且单图时组图ID字段存的是自身ID
两张表组合要实现如下效果,带数字4的是个组图,不带数字的是单图
最开始想着用一条SQL group by 方式查询(以图的多语言表中的res_title_code 分组),这种方式能满足,单图显示要求(每种语言相同图说相同图片,相同渠道显示为一张图,不同图说不同渠道相同语言显示为多张,不同图说不同渠道不同语言相同图片显示为多张),但是这个样查询会导致组图被分解,当组图中的只有几个图有图说的时候,查询因为是按照res_title_code 分组会导致没有在多语言信息表中存在的数据被划分到空组中,示例如下:
SELECT crsr.res_set_code,
crl.res_title_code,
'1' nums, -- 备注一下:这里需要改为 COUNT(DISTINCT crsr.res_code) ,如果还是用常量字符串'1',最后联合查询结果可能不是预期的数字型,可能返回类似java对象地址
GROUP_CONCAT(DISTINCT crsr.CODE) codes,
GROUP_CONCAT(DISTINCT crsr.channel_code) channel_code,
GROUP_CONCAT(DISTINCT crsr.res_code) res_code,
GROUP_CONCAT(DISTINCT crsr.url_thumb) url_thumb,
GROUP_CONCAT(DISTINCT crsr.channel_category_id) category_id,
GROUP_CONCAT(DISTINCT crsr.channel_category_name) channel_category_name,
'0' is_group
FROM
cms_resource_set_resource crsr
LEFT JOIN cms_resource_langs crl ON crsr.res_set_code = crl.res_set_code
AND crsr.res_code = crl.res_code AND crsr.channel_code=crl.channel_code
GROUP BY
crl.res_title_code
这样的结果是不符合需求的,用了一下午时间没想到好的解决办法,下班后回家又琢磨,吃过晚饭平复了心境后突然想起了 UNION 联合查询,然后就尝试了一把,结果符合预期,只不过多语言信息需要单图查询,因为组图只显示其中一张图的图说。
思路是,将单图、组图的查询结果进行合并,由于单图时res_set_code 与 res_code 是相同的,所以第一条SQL先查询单图,单图以 res_title_code 进行分组,SQL 如下
SELECT
crsr.res_set_code,
crl.res_title_code,
'1' nums,
GROUP_CONCAT(DISTINCT crsr.CODE) codes,
GROUP_CONCAT(DISTINCT crsr.channel_code) channel_code,
GROUP_CONCAT(DISTINCT crsr.res_code) res_code,
GROUP_CONCAT(DISTINCT crsr.url_thumb) url_thumb,
GROUP_CONCAT(DISTINCT crsr.channel_category_id) category_id,
GROUP_CONCAT(DISTINCT crsr.channel_category_name) channel_category_name,
'0' is_group
FROM
cms_resource_set_resource crsr
LEFT JOIN cms_resource_langs crl ON crsr.res_set_code = crl.res_set_code
AND crsr.res_code = crl.res_code AND crsr.channel_code=crl.channel_code
WHERE crsr.res_set_code = crsr.res_code
GROUP BY
crl.res_title_code
结果如下
oppo,vivo 由于图说一直,并且是单图,所以会被合并处理,而htc虽然也是相同图片,但是图说不一样,所以被划分为单独的一个
下面是组图查询,组图是以res_set_code 分组,并且res_set_code与 res_code 不同,根据这个条件,组图查询SQL如下
SELECT crsr.res_set_code,
crl.res_title_code,
COUNT(DISTINCT crsr.res_code) nums,
GROUP_CONCAT(DISTINCT crsr.CODE) codes,
GROUP_CONCAT(DISTINCT crsr.channel_code) channel_code,
GROUP_CONCAT(DISTINCT crsr.res_code) res_code,
GROUP_CONCAT(DISTINCT crsr.url_thumb) url_thumb,
GROUP_CONCAT(DISTINCT crsr.channel_category_id) category_id,
GROUP_CONCAT(DISTINCT crsr.channel_category_name) channel_category_name,
'1' is_group
FROM
cms_resource_set_resource crsr
LEFT JOIN cms_resource_langs crl ON crsr.res_set_code = crl.res_set_code
AND crsr.res_code = crl.res_code AND crsr.channel_code=crl.channel_code
WHERE IF(crsr.res_set_code = crsr.res_code,0,1)=1 // 这里判断 res_set_code 是否与 res_code 一致,如果一致说明是单图需要过滤掉,所以 条件是等于 1
GROUP BY
crsr.res_set_code
查询结果如下
最后组合在一起查询SQL 如下(这里需要注意一点,UNION 与 UNION ALL 的区别,UNION 会去重,当两个或多个SQL查询的结果一模一样的时候会被踢出掉,而UNION ALL 会保留重复数据,我这里不需要重复数据,所以用的UNION)
SELECT
crsr.res_set_code,
crl.res_title_code,
'1' nums,
GROUP_CONCAT(DISTINCT crsr.CODE) codes,
GROUP_CONCAT(DISTINCT crsr.channel_code) channel_code,
GROUP_CONCAT(DISTINCT crsr.res_code) res_code,
GROUP_CONCAT(DISTINCT crsr.url_thumb) url_thumb,
GROUP_CONCAT(DISTINCT crsr.channel_category_id) category_id,
GROUP_CONCAT(DISTINCT crsr.channel_category_name) channel_category_name,
'0' is_group
FROM
cms_resource_set_resource crsr
LEFT JOIN cms_resource_langs crl ON crsr.res_set_code = crl.res_set_code
AND crsr.res_code = crl.res_code AND crsr.channel_code=crl.channel_code
WHERE crsr.res_set_code = crsr.res_code
GROUP BY
crl.res_title_code
UNION
SELECT
crsr.res_set_code,
crl.res_title_code,
COUNT(DISTINCT crsr.res_code) nums,
GROUP_CONCAT(DISTINCT crsr.CODE) codes,
GROUP_CONCAT(DISTINCT crsr.channel_code) channel_code,
GROUP_CONCAT(DISTINCT crsr.res_code) res_code,
GROUP_CONCAT(DISTINCT crsr.url_thumb) url_thumb,
GROUP_CONCAT(DISTINCT crsr.channel_category_id) category_id,
GROUP_CONCAT(DISTINCT crsr.channel_category_name) channel_category_name,
'1' is_group
FROM
cms_resource_set_resource crsr
LEFT JOIN cms_resource_langs crl ON crsr.res_set_code = crl.res_set_code
AND crsr.res_code = crl.res_code AND crsr.channel_code=crl.channel_code
WHERE IF(crsr.res_set_code = crsr.res_code,0,1)=1
GROUP BY
crsr.res_set_code
结果如下(可以与第一张图对比)
这件事证明了一件事,人在急躁的时候很容忽略以前学过的东西,反而造成效率非常低下,当遇到事段时间内想不到好的解决办法的时候,先要让自己冷静下来,休息休息再回想以前学过的知识,有时候会发现当前棘手的问题,其实很好解决。
2017年11月24日10:19:38