博客这东西更像个人的备忘录，最近发现不管博文长短，遇到问题总结一下是很开心的，而且会加深印象，这里记录一下最近遇到的问题以及如何解决的(其实挺简单的一个问题，由于以前没有写博文的习惯并且都是看别人博文，所以用过就忘了)
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
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
