reverse group concat in sqlite

初(扯)衷(淡)

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

开源的就是好的

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

正题

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


第一种方法:逐步填充

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

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

1
2
3
4
5
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,**

1
2
3
4
5
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**

1
2
3
4
5
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);

最后,匹配没有逗号的(唯一)

1
2
3
4
5
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);

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

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

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

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


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

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

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

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