SQL从一个条目中选择两列

Question

如何在条目末尾使用正则表达式从一列查询两列？

表图像：

IMAGE_NAME         IMAGE_ID
---------------------------
image01_01.png     0
image01_02.png     1
image02_01.png     2
image02_02.png     3

select
IMAGE_NAME,
IMAGE_ID,
<img src="https://my_page?im_id='||IMAGE_ID||'" class="test" height="50" width="200">' IMAGE
from 
IMAGES
where 
IMAGE_NAME like '%01.png'

显示所有以01.png结尾的图像名称。此外，我需要第二列，显示所有以02.png结尾的图像名称。

如何实现这个查询得到两列？

编辑：我想在Apex中的交互式报告中显示两个图像列。

编辑#2：当显示带有img标记的图像时，它变得更加复杂。

预期的列结果应该是一个html图像对象，可以在Apex:中显示

https://my_page?im_id=0

Suffix_01       | Suffix_02       | Link to image_01       | Link to image_02
----------------+-----------------+------------------------+-----------------
image02_01.png  | image02_02.png  |https://my_page?im_id=2 | https://my_page?im_id=3
image01_01.png  | image01_02.png  |https://my_page?im_id=0 |https://my_page?im_id=1

Answer 1

假设您希望一个表在一行中具有相同前缀的图像名称。您可以使用条件聚合：

SELECT 
t.suffix01, 
t.suffix02, 
'https://my_page?im_id=' || i1.image_id "Link to image_01", 
'https://my_page?im_id=' || i2.image_id "Link to image_02"
FROM
(SELECT 
MAX(CASE WHEN Image_name like '%01.png' THEN Image_name END) suffix01,
MAX(CASE WHEN Image_name like '%02.png' THEN Image_name END) suffix02
FROM 
Images
GROUP BY 
SUBSTR(image_name, 1, LENGTH(image_name) - 6)) t 
JOIN 
images i1 ON i1.image_name = t.suffix01
JOIN  
images i2 ON i2.image_name = t.suffix02

演示

结果：

suffix01        | suffix02        | Link to image_01        | Link to image_02
----------------+-----------------+-------------------------+------------------
image02_01.png  | image02_02.png  | https://my_page?im_id=2 | https://my_page?im_id=3
image01_01.png  | image01_02.png  | https://my_page?im_id=0 |https://my_page?im_id=1