56,677
社区成员
发帖
与我相关
我的任务
分享
假如 111 111 1 (二进制表示 右 低位-> 左边 高位)
从右向左
第1位 星期一
第2位 星期二
3 星期三
4 星期四
5 星期五
6 星期六
7 星期七
eg.
111 111 1 (二进制) = 128 (十进制) 就是 星期 一,星期二 星期三,星期四,星期五,星期六,星期日
week字段存储的是一个smallint的数字值
(case week
"when 1 then '星期一' " & _
"when 2 then '星期二' " & _
"when 3 then '星期一,星期二' " & _
"when 4 then '星期三' " & _
"when 5 then '星期一,星期三' " & _
"when 6 then '星期二,星期三' " & _
"when 7 then '星期一,星期二,星期三' " & _
"when 8 then '星期四' " & _
"when 9 then '星期一,星期四' " & _
"when 10 then '星期二,星期四' " & _
"when 11 then '星期一,星期二,星期四' " & _
"when 12 then '星期三,星期四' " & _
"when 13 then '星期一,星期三,星期四' " & _
"when 14 then '星期二,星期三,星期四' " & _
"when 15 then '星期一,星期二,星期三,星期四' " & _
"when 16 then '星期五' " & _
"when 17 then '星期一,星期五' " & _
"when 18 then '星期二,星期五' " & _
"when 19 then '星期一,星期二,星期五' " & _
"when 20 then '星期三,星期五' " & _
"when 21 then '星期一,星期三,星期五' " & _
"when 22 then '星期二,星期三,星期五' " & _
"when 23 then '星期一,星期二,星期三,星期五' " & _
"when 24 then '星期四,星期五' " & _
"when 25 then '星期一,星期四,星期五' " & _
"when 26 then '星期二,星期四,星期五' " & _
......
end) as week
SELECT GROUP_CONCAT(name) AS week
FROM
(
SELECT
1 AS w, 'Mon' AS name
UNION
SELECT
2 AS w, 'Tue' AS name
UNION
SELECT
4 AS w, 'Wed' AS name
UNION
SELECT
8 AS w, 'Thu' AS name
UNION
SELECT
16 AS w, 'Fri' AS name
UNION
SELECT
32 AS w, 'Sat' AS name
UNION
SELECT
64 AS w, 'Sun' AS name
) X
WHERE 127 & X.w != 0
SELECT GROUP_CONCAT(name) AS week
FROM
(SELECT 127 AS num) A,
(
SELECT
1 AS w, 'Mon' AS name
UNION
SELECT
2 AS w, 'Tue' AS name
UNION
SELECT
4 AS w, 'Wed' AS name
UNION
SELECT
8 AS w, 'Thu' AS name
UNION
SELECT
16 AS w, 'Fri' AS name
UNION
SELECT
32 AS w, 'Sat' AS name
UNION
SELECT
64 AS w, 'Sun' AS name
) X
WHERE A.num & X.w != 0
GROUP BY num
mysql> select MAKE_SET(1,'星期一','星期二','星期三','星期四','星期五','星期六','星期日');
+----------------------------------------------------------------------------+
| MAKE_SET(1,'星期一','星期二','星期三','星期四','星期五','星期六','星期日') |
+----------------------------------------------------------------------------+
| 星期一 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select MAKE_SET(2,'星期一','星期二','星期三','星期四','星期五','星期六','星期日');
+----------------------------------------------------------------------------+
| MAKE_SET(2,'星期一','星期二','星期三','星期四','星期五','星期六','星期日') |
+----------------------------------------------------------------------------+
| 星期二 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select MAKE_SET(3,'星期一','星期二','星期三','星期四','星期五','星期六','星期日');
+----------------------------------------------------------------------------+
| MAKE_SET(3,'星期一','星期二','星期三','星期四','星期五','星期六','星期日') |
+----------------------------------------------------------------------------+
| 星期一,星期二 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select MAKE_SET(127,'星期一','星期二','星期三','星期四','星期五','星期六','星期日');
+------------------------------------------------------------------------------+
| MAKE_SET(127,'星期一','星期二','星期三','星期四','星期五','星期六','星期日') |
+------------------------------------------------------------------------------+
| 星期一,星期二,星期三,星期四,星期五,星期六,星期日 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>