reverse group concat in sqlite

初（扯）衷（淡）

最近在研究关于文档整理的相关知识，基本需求就是随时随地记录和查看文档。恩，这个博客也是其中一部分。印象笔记已经很接近这一点了，参考移动城堡的这篇攻略（心理活动：这货看上去闷闷的，没想到这么能折腾），可惜移动端不能离线，太费流量，弃之。然后发现了这个Omni-Notes，俗话说，

开源的就是好的

果断拿来用，可惜一堆Bug，不过也可以理解，老外用的好好的到我们手里就瘫掉多半是天朝特色。不出所料，这货用的是一个sqlite文件做数据存储，只不过附件是用路径引用的方式，这样的话备份就只涉及到这二者了。

正题

我的第一个想法是把http://plant.hzau.edu.cn上的学校植物数据弄到omni-note上，爬取数据，当然首选scrapy。过程略去。最终得到两张表，**PLANT_DATA是图片的blob和对应的file name，IMAGES**是每个植物的详细信息以及对应网页的short url，其中附件名一栏是逗号分隔的file name。现在的任务是:
如何把file name对应到short url
换句话说就是
把逗号分隔的file name拆开
用脚本当然很容易实现，但我就是要用sql来搞（对这种全大写的语言中毒极深）。下面开搞

---

第一种方法：逐步填充

目前可用的大概就like和instr函数，但instr有个问题就是没法区分部分匹配。所以就like啦

首先，匹配**,xxx.jpg,这种形式并填充

UPDATE IMAGES SET 
LI = (SELECT PLANT_DATA.M FROM PLANT_DATA 
WHERE PLANT_DATA.E LIKE "%," || IMAGES.PATH || ",%")
WHERE EXISTS(SELECT * FROM PLANT_DATA 
WHERE PLANT_DATA.E LIKE "%," || IMAGES.PATH || ",%");

然后，填充xxx.jpg,**

UPDATE IMAGES SET 
LI = (SELECT PLANT_DATA.M FROM PLANT_DATA 
WHERE PLANT_DATA.E LIKE IMAGES.PATH || ",%")
WHERE EXISTS(SELECT * FROM PLANT_DATA 
WHERE (LI IS NULL) AND PLANT_DATA.E LIKE IMAGES.PATH || ",%");

然后，**,xxx.jpg**

UPDATE IMAGES SET 
LI = (SELECT PLANT_DATA.M FROM PLANT_DATA 
WHERE PLANT_DATA.E LIKE "%," || IMAGES.PATH)
WHERE EXISTS(SELECT * FROM PLANT_DATA 
WHERE (LI IS NULL) AND PLANT_DATA.E LIKE "%," || IMAGES.PATH);

最后，匹配没有逗号的（唯一）

UPDATE IMAGES SET 
LI = (SELECT PLANT_DATA.M FROM PLANT_DATA 
WHERE PLANT_DATA.E=IMAGES.PATH)
WHERE EXISTS(SELECT * FROM PLANT_DATA 
WHERE (LI IS NULL) AND PLANT_DATA.E=IMAGES.PATH);

第二种方法：先替换再匹配

在每个字符串前后加上逗号，这样就不用逐步匹配啦
先

UPDATE PLANT_DATA SET 
E = "," || E || "," WHERE E <> "";

然后用上面的第一个就好，不用担心部分匹配的问题。

---

当然还有其他更好的办法就不啰嗦了

最后可以在**IMAGES**里用这个验证一下

SELECT GROUP_CONCAT(PATH), LI FROM IMAGES GROUP BY LI;

SELECT E, M FROM PLANT_DATA WHERE E <>"";