死锁的主要原因是什么，可以预防吗？

Question

JHFB

Asked: 2012-05-23 09:10:53 +0800 CST2012-05-23 09:10:53 +0800 CST 2012-05-23 09:10:53 +0800 CST

我可以在没有函数的情况下将整数转换为 base-2 吗

772

这是一个挑战。我正在处理表格ReportServer.Schedule，并且有一个名为DaysOfWeek. 我在这里可能没有使用正确的术语，但这是一个整数，可以从中得出 SSRS 订阅设置为在一周中的哪几天运行。每一天都分配了一个数字，如下所示：

周日：1
星期一：2
周二：4
周三：8
星期四：16
周五：32
星期六：64

订阅设置运行的总天数就是此列中的数字。因此，从周一到周五运行的订阅在此列中的值为62。

我想要的最终结果是一个视图，它根据这个数字派生出每天的 T/F 标志，这样我每天都有一列。我目前正在探索的方法是将这个整数转换为 base-2，这样我就可以将它转换为 varchar 并解析出天数。在此示例中，结果将为 111110。

最后的转折——我没有能力在这个数据库中创建函数或存储过程，所以我强烈倾向于在一条SELECT语句中解决这个问题……

（如果迫在眉睫，我将移动原始数据并在单独的数据库中使用一个函数 - 并且已经在网上找到了一些。）

2 个回答

Voted

Thomas Stringer · Answer 1 · 2012-05-23T09:29:19+08:00

除非我完全误解了你的问题，否则下面的 T-SQL 应该可以做到（如果这不是你要找的，请告诉我）。它利用按位运算符提取天数的位掩码：

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case 
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case 
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case 
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case 
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case 
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case 
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case 
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable

buckley · Answer 2 · 2012-05-23T09:35:57+08:00

由于您无法创建函数存储过程，因此您可以加入内存查找表:)

SELECT bin 
from
(
select 0 as dec, '0000000' as bin 
UNION 
select 1 as dec, '0000001' as bin 
UNION 
select 2 as dec, '0000010' as bin 
UNION 
select 3 as dec, '0000011' as bin 
UNION 
select 4 as dec, '0000100' as bin 
UNION 
select 5 as dec, '0000101' as bin 
UNION 
select 6 as dec, '0000110' as bin 
UNION 
select 7 as dec, '0000111' as bin 
UNION 
select 8 as dec, '0001000' as bin 
UNION 
select 9 as dec, '0001001' as bin 
UNION 
select 10 as dec, '0001010' as bin 
UNION 
select 11 as dec, '0001011' as bin 
UNION 
select 12 as dec, '0001100' as bin 
UNION 
select 13 as dec, '0001101' as bin 
UNION 
select 14 as dec, '0001110' as bin 
UNION 
select 15 as dec, '0001111' as bin 
UNION 
select 16 as dec, '0010000' as bin 
UNION 
select 17 as dec, '0010001' as bin 
UNION 
select 18 as dec, '0010010' as bin 
UNION 
select 19 as dec, '0010011' as bin 
UNION 
select 20 as dec, '0010100' as bin 
UNION 
select 21 as dec, '0010101' as bin 
UNION 
select 22 as dec, '0010110' as bin 
UNION 
select 23 as dec, '0010111' as bin 
UNION 
select 24 as dec, '0011000' as bin 
UNION 
select 25 as dec, '0011001' as bin 
UNION 
select 26 as dec, '0011010' as bin 
UNION 
select 27 as dec, '0011011' as bin 
UNION 
select 28 as dec, '0011100' as bin 
UNION 
select 29 as dec, '0011101' as bin 
UNION 
select 30 as dec, '0011110' as bin 
UNION 
select 31 as dec, '0011111' as bin 
UNION 
select 32 as dec, '0100000' as bin 
UNION 
select 33 as dec, '0100001' as bin 
UNION 
select 34 as dec, '0100010' as bin 
UNION 
select 35 as dec, '0100011' as bin 
UNION 
select 36 as dec, '0100100' as bin 
UNION 
select 37 as dec, '0100101' as bin 
UNION 
select 38 as dec, '0100110' as bin 
UNION 
select 39 as dec, '0100111' as bin 
UNION 
select 40 as dec, '0101000' as bin 
UNION 
select 41 as dec, '0101001' as bin 
UNION 
select 42 as dec, '0101010' as bin 
UNION 
select 43 as dec, '0101011' as bin 
UNION 
select 44 as dec, '0101100' as bin 
UNION 
select 45 as dec, '0101101' as bin 
UNION 
select 46 as dec, '0101110' as bin 
UNION 
select 47 as dec, '0101111' as bin 
UNION 
select 48 as dec, '0110000' as bin 
UNION 
select 49 as dec, '0110001' as bin 
UNION 
select 50 as dec, '0110010' as bin 
UNION 
select 51 as dec, '0110011' as bin 
UNION 
select 52 as dec, '0110100' as bin 
UNION 
select 53 as dec, '0110101' as bin 
UNION 
select 54 as dec, '0110110' as bin 
UNION 
select 55 as dec, '0110111' as bin 
UNION 
select 56 as dec, '0111000' as bin 
UNION 
select 57 as dec, '0111001' as bin 
UNION 
select 58 as dec, '0111010' as bin 
UNION 
select 59 as dec, '0111011' as bin 
UNION 
select 60 as dec, '0111100' as bin 
UNION 
select 61 as dec, '0111101' as bin 
UNION 
select 62 as dec, '0111110' as bin 
UNION 
select 63 as dec, '0111111' as bin 
UNION 
select 64 as dec, '1000000' as bin 
UNION 
select 65 as dec, '1000001' as bin 
UNION 
select 66 as dec, '1000010' as bin 
UNION 
select 67 as dec, '1000011' as bin 
UNION 
select 68 as dec, '1000100' as bin 
UNION 
select 69 as dec, '1000101' as bin 
UNION 
select 70 as dec, '1000110' as bin 
UNION 
select 71 as dec, '1000111' as bin 
UNION 
select 72 as dec, '1001000' as bin 
UNION 
select 73 as dec, '1001001' as bin 
UNION 
select 74 as dec, '1001010' as bin 
UNION 
select 75 as dec, '1001011' as bin 
UNION 
select 76 as dec, '1001100' as bin 
UNION 
select 77 as dec, '1001101' as bin 
UNION 
select 78 as dec, '1001110' as bin 
UNION 
select 79 as dec, '1001111' as bin 
UNION 
select 80 as dec, '1010000' as bin 
UNION 
select 81 as dec, '1010001' as bin 
UNION 
select 82 as dec, '1010010' as bin 
UNION 
select 83 as dec, '1010011' as bin 
UNION 
select 84 as dec, '1010100' as bin 
UNION 
select 85 as dec, '1010101' as bin 
UNION 
select 86 as dec, '1010110' as bin 
UNION 
select 87 as dec, '1010111' as bin 
UNION 
select 88 as dec, '1011000' as bin 
UNION 
select 89 as dec, '1011001' as bin 
UNION 
select 90 as dec, '1011010' as bin 
UNION 
select 91 as dec, '1011011' as bin 
UNION 
select 92 as dec, '1011100' as bin 
UNION 
select 93 as dec, '1011101' as bin 
UNION 
select 94 as dec, '1011110' as bin 
UNION 
select 95 as dec, '1011111' as bin 
UNION 
select 96 as dec, '1100000' as bin 
UNION 
select 97 as dec, '1100001' as bin 
UNION 
select 98 as dec, '1100010' as bin 
UNION 
select 99 as dec, '1100011' as bin 
UNION 
select 100 as dec, '1100100' as bin 
UNION 
select 101 as dec, '1100101' as bin 
UNION 
select 102 as dec, '1100110' as bin 
UNION 
select 103 as dec, '1100111' as bin 
UNION 
select 104 as dec, '1101000' as bin 
UNION 
select 105 as dec, '1101001' as bin 
UNION 
select 106 as dec, '1101010' as bin 
UNION 
select 107 as dec, '1101011' as bin 
UNION 
select 108 as dec, '1101100' as bin 
UNION 
select 109 as dec, '1101101' as bin 
UNION 
select 110 as dec, '1101110' as bin 
UNION 
select 111 as dec, '1101111' as bin 
UNION 
select 112 as dec, '1110000' as bin 
UNION 
select 113 as dec, '1110001' as bin 
UNION 
select 114 as dec, '1110010' as bin 
UNION 
select 115 as dec, '1110011' as bin 
UNION 
select 116 as dec, '1110100' as bin 
UNION 
select 117 as dec, '1110101' as bin 
UNION 
select 118 as dec, '1110110' as bin 
UNION 
select 119 as dec, '1110111' as bin 
UNION 
select 120 as dec, '1111000' as bin 
UNION 
select 121 as dec, '1111001' as bin 
UNION 
select 122 as dec, '1111010' as bin 
UNION 
select 123 as dec, '1111011' as bin 
UNION 
select 124 as dec, '1111100' as bin 
UNION 
select 125 as dec, '1111101' as bin 
UNION 
select 126 as dec, '1111110' as bin 
UNION 
select 127 as dec, '1111111' as bin 
) AS dummy
WHERE dec = 62

我可以在没有函数的情况下将整数转换为 base-2 吗

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

我可以在没有函数的情况下将整数转换为 base-2 吗

2 个回答

相关问题