PostgreSQL 8.1 CASE 转字符串

Question

我有一个数据库，它使用一串 0 和 1 来表示一个人的工作时间表中的日期。例如，0111110 代表“否”星期日，“是”星期一至星期五，“否”星期六。我想使用 SQL 将其从数据库中取出，最后得到一个看起来像“星期一、星期二、星期三、星期四、星期五”的字符串。 这就是我所得到的……

   CASE 
        WHEN SUBSTR(regular_work_days, 1,1)='1' THEN 'Sunday'
        WHEN SUBSTR(regular_work_days, 2,1)='1' THEN 'Monday'
        WHEN SUBSTR(regular_work_days, 3,1)='1' THEN 'Tuesday'
        WHEN SUBSTR(regular_work_days, 4,1)='1' THEN 'Wednesday'
        WHEN SUBSTR(regular_work_days, 5,1)='1' THEN 'Thursday'
        WHEN SUBSTR(regular_work_days, 6,1)='1' THEN 'Friday'
        WHEN SUBSTR(regular_work_days, 7,1)='1' THEN 'Saturday'
        ELSE ' '
   END AS "Regular Work Days",

可以预见的是，我最终得到了第一个“1”，而没有其他日子。我尝试添加||但意识到我不知道如何让它发挥作用。如果逗号可以简化事情，那么就没有必要。 请帮忙？

Answer 1

打包的日期字符串只有 128 个可能的值（七个槽位，每个槽位有两个可能的值意味着 128 种可能性），因此只需生成一个涵盖所有选项的表并连接到该表即可获取未打包的字符串。您应该能够非常轻松地生成这样的表格：

packed    | unpacked
----------+-----------
'0000000' | ''
'0000001' | 'Saturday'
...
'1000001' | 'Sunday, Saturday'
...

然后您可以将打包日列加入到 packed 上的此表格中，并选择 unpacked 来获取人类友好的字符串。请记住，有限域上的函数是关联表，如果域很小，那么您可以很容易地将函数实现为表。

---

如果你必须以困难的方式做到这一点，那么这将在 8.1 中工作，但它相当可怕，并且应该让你相信（1）你不应该像那样存储你的日子，（b）你应该处理这种格式数据库之外。在现实生活中我不会做这样的事情，我只是将其包括在内，因为我想看看是否可以想出一些可以在受限的 8.1 环境中工作的东西。您可能也想尽快升级，8.1 已经过时很久了，不再受支持。

首先，您需要一个要连接的日期名称表：

create table days (num int not null, name varchar(9) not null);
insert into days (num, name) values (1, 'Sunday');
insert into days (num, name) values (2, 'Monday');
insert into days (num, name) values (3, 'Tuesday');
insert into days (num, name) values (4, 'Wednesday');
insert into days (num, name) values (5, 'Thursday');
insert into days (num, name) values (6, 'Friday');
insert into days (num, name) values (7, 'Saturday');

然后是一个自定义聚合，用于将用逗号分隔的字符串连接在一起：

create function comma_join(t1 text, t2 text) returns text as $
begin
    if t1 is null or t2 is null then
        return null;
    elseif t1 = '' or t2 = '' then
        return t1 || t2;
    end if;
    return t1 || ', ' || t2;
end;
$ language plpgsql;

create aggregate group_comma_join(
    sfunc = comma_join,
    basetype = text,
    stype = text,
    initcond = ''
);

最后，一个用于隐藏所有丑陋之处的解包函数：

create function unpack_days(days_string text) returns text as $
declare
    s text;
begin
    select group_comma_join(name)
    from (
        select name into s
        from days d join generate_series(1, 7) n(num) on d.num = n.num
        where substr(days_string, n.num, 1) = '1'
    ) dt;
    return s;
end
$ language plpgsql;

现在您可以这样说：

=> select unpack_days('0111110');
                 unpack_days                  
----------------------------------------------
 Monday, Tuesday, Wednesday, Thursday, Friday
(1 row)

=> select unpack_days('0000000');
 unpack_days 
-------------

(1 row)

Answer 2

这应该有效。也许有一种更简单的方法但是......

select
    array_to_string(array_agg(
        (regexp_split_to_array('sun,mon,tue,wed,thu,fri,sat', ','))[i]
    ), ', ')
from generate_series(1, 7) i
where (regexp_split_to_array('0111110', ''))[i] = '1'

这给出了：

         daylist         
-------------------------
 mon, tue, wed, thu, fri