mysql - 有人可以向我解释这段代码吗？

Question

我看到了这段代码，我想问问你们是否可以向我解释一下这段代码是如何工作的，谢谢。

select SUM(IF(encodedRestDay & (1 << (DAYOFWEEK(selected_date)-1)) > 0, 1, NULL)) as numberOfRestDay from (
    select *, if ( encodedRestDay & (1 << (DAYOFWEEK(selected_date)-1)) > 0,
    'restDay', 'workDay'
    ) as dayStatus, dayName(selected_date) as `dayName` from (
        select *,  (sat << 6 | fri << 5 | thu << 4 | wed << 3 | tue << 2 | mon << 1 | sun) as encodedRestDay from (
            select adddate('2014-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as selected_date,
                sat, fri, thu, wed, tue, mon, sun
            from 
                    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
                    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
                    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
                    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4,
            restday
            where id = 6
            ) v

            where selected_date between '2014-02' and '2014-04'
        )a
    )b

Answer 1

此查询正在执行复杂的日期算法，以计算两个日期之间的“休息”天数。 (这大概是非工作日的天数。)数据结构足够灵活，可以处理不同“文化”的工作日。

这个子查询:

        select adddate('2014-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as selected_date,
            sat, fri, thu, wed, tue, mon, sun
        from 
                (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
                (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
                (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
                (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
                (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4,
        restday
        where id = 6

正在为接下来的 10,000 天创建从 2014-01-01 开始的日历。它对 restday 中符合 id = 6 的每一行执行此操作。没有表格的布局，很难理解这意味着什么，或者星期几列的真正含义。我推测它们会告诉您对于特定文化来说一天是工作日还是休息日。

下一级:

    select *,  (sat << 6 | fri << 5 | thu << 4 | wed << 3 | tue << 2 | mon << 1 | sun) as encodedRestDay
    from (

正在做一些位移。这样，1001011 代表星期六、星期三、星期一和星期日。

最外层的查询然后进行某种比较以获得“休息”天数(如果我理解正确的话)。

这是查询的要点。我不完全明白 restday 是做什么的；我怀疑它针对不同的文化或类次有不同的工作日模式。