数据库查询条件与对应值有交集的判断
2019-01-04 本文已影响0人
forever_youyou
假设现有配置：
const conf = [
{value: 1 << 0, label: '普通住宅'}, // value=1
{value: 1 << 1, label: '别墅'}, // value=2
{value: 1 << 2, label: '酒店式公寓'}, // value=4
{value: 1 << 3, label: '商业'}, // value=8
{value: 1 << 4, label: '办公'}, // value=16
{value: 1 << 5, label: '车库/车位'}, // value=32
{value: 1 << 6, label: '工业'}, // value=64
{value: 1 << 7, label: '配套商品房'}, // value=128
{value: 1 << 8, label: '动迁安置房'}, // value=256
{value: 1 << 9, label: '经济适用房'}, // value=512
{value: 1 << 10, label: '限价房'}, // value=1024
{value: 1 << 11, label: '其他'}, // value=2048
];
现有模拟数据：
-- type 字段的值为该记录对应的所有type的value(conf中对应value)按位'或'的结果
-- 如李四: [普通住宅 1, 办公 16, 车库/车位 32], type值为: 1|16|32 = 49
INSERT INTO `t_demo`(`name`, `type`, `sType`) VALUES
('张三', 7, '\'普通住宅\', \'别墅\', \'酒店式公寓\''),
('李四', 49, '\'普通住宅\', \'办公\', \'车库/车位\''),
('暗巫', 52, '\'酒店式公寓\', \'办公\', \'车库/车位\''),
('王五', 1332, '\'酒店式公寓\', \'办公\', \'车库/车位\', \'动迁安置房\', \'限价房\''),
('好留', 1072, '\'办公\', \'车库/车位\', \'限价房\''),
('好六', 4095, '\'普通住宅\', \'别墅\', \'酒店式公寓\', \'商业\', \'办公\', \'车库/车位\', \'工业\', \'配套商品房\', \'动迁安置房\', \'经济适用房\', \'限价房\', \'其他\''),
('赵柳', 1661, '\'普通住宅\', \'酒店式公寓\', \'商业\', \'办公\', \'车库/车位\', \'工业\', \'经济适用房\', \'限价房\'');
要获取其中类型与查询类型有交集的记录如下即可：
-- 查 [普通住宅1] -> 1
SELECT * FROM t_demo WHERE type & 1;
-- 查 [普通住宅1, 别墅2] -> 1|2 = 3
SELECT * FROM t_demo WHERE type & 3;
-- 查 [普通住宅1, 别墅2, 办公16] -> 1|2|16 = 19
SELECT * FROM t_demo WHERE type & 19;
-- 查 [办公16] -> 16
SELECT * FROM t_demo WHERE type & 16;
-- 查 [限价房1024] -> 1024
SELECT * FROM t_demo WHERE type & 1024;
附相关demo：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Bit</title>
<style>
#vm {width: 800px;margin: 0 auto;}
.block + .block {margin-top: 30px;border-top: 3px solid #666;}
table {border-top: 1px solid #666;border-left: 1px solid #666;}
table td {border-bottom: 1px solid #666;border-right: 1px solid #666;}
.result {color: #F00;}
</style>
</head>
<body>
<div id="vm">
<div class="block">
<h3>简单使用</h3>
<template v-for="item in conf">
<label><input type="checkbox" :value="item.value" v-model="checkedTypes1"> {{item.label}} </label>
</template>
<br>
<p>
当前类型组合: {{ checkedTypes1 }}
<br>
组合值：{{ oneToMulti(multiToOne(checkedTypes1)) }}
<br>
组合值：<span class="result">{{ multiToOne(checkedTypes1) }}</span>
</p>
</div>
<div class="block">
<h3>两个组合是否有交集</h3>
<table>
<tr>
<td style="border-right: 1px solid #666;">
<template v-for="item in conf">
<label>
<input type="checkbox" :value="item.value" v-model="checkedTypes2"> {{item.label}}
</label>
</template>
<br>
<p>
当前类型组合: {{ checkedTypes2 }}
<br>
组合值：<span class="result">{{ multiToOne(checkedTypes2) }}</span>
</p>
</td>
<td>
<template v-for="item in conf">
<label>
<input type="checkbox" :value="item.value" v-model="checkedTypes3"> {{item.label}}
</label>
</template>
<br>
<p>
当前类型组合: {{ checkedTypes3 }}
<br>
组合值：<span class="result">{{ multiToOne(checkedTypes3) }}</span>
</p>
</td>
</tr>
<tr>
<td colspan="2" align="center">
两个组合是否有交集: <span class="result">{{ haveOverlap(multiToOne(checkedTypes2), multiToOne(checkedTypes3)) }}</span>
<br>
交集为：<span class="result">{{ getIntersection(multiToOne(checkedTypes2), multiToOne(checkedTypes3)) }}</span>
<br>
交集为：<span class="result">{{ oneToMulti(getIntersection(multiToOne(checkedTypes2), multiToOne(checkedTypes3))) }}</span>
</td>
</tr>
</table>
</div>
<div class="block">
<h3>配置</h3>
<pre>
conf: [
{value: 1 << 0, label: '普通住宅'},
{value: 1 << 1, label: '别墅'},
{value: 1 << 2, label: '酒店式公寓'},
{value: 1 << 3, label: '商业'},
{value: 1 << 4, label: '办公'},
{value: 1 << 5, label: '车库/车位'},
{value: 1 << 6, label: '工业'},
{value: 1 << 7, label: '配套商品房'},
{value: 1 << 8, label: '动迁安置房'},
{value: 1 << 9, label: '经济适用房'},
{value: 1 << 10, label: '限价房'},
{value: 1 << 11, label: '其他'},
]
</pre>
</div>
</div>
<script src="https://cdn.bootcss.com/vue/2.5.20/vue.min.js"></script>
<script>
new Vue({
el: '#vm',
data: {
conf: [
{value: 1 << 0, label: '普通住宅'},
{value: 1 << 1, label: '别墅'},
{value: 1 << 2, label: '酒店式公寓'},
{value: 1 << 3, label: '商业'},
{value: 1 << 4, label: '办公'},
{value: 1 << 5, label: '车库/车位'},
{value: 1 << 6, label: '工业'},
{value: 1 << 7, label: '配套商品房'},
{value: 1 << 8, label: '动迁安置房'},
{value: 1 << 9, label: '经济适用房'},
{value: 1 << 10, label: '限价房'},
{value: 1 << 11, label: '其他'},
],
checkedTypes1: [],
checkedTypes2: [],
checkedTypes3: [],
},
methods: {
/**
* 两个组合是否有交集
*/
haveOverlap: function (a, b) {
return !!this.getIntersection(a, b);
},
getIntersection: function (a, b) {
return a & b;
},
/**
* 指定组合的数组转为单个数字
* @param arr
* @returns {number}
*/
multiToOne(arr) {
let one = 0;
for (let i = 0, len = arr.length; i < len; i++) {
one |= arr[i];
}
return one;
},
oneToMulti(one) {
let arr = this.conf;
let ret = [];
for (let i = 0, len = arr.length; i < len; i++) {
if (one & arr[i].value) {
ret.push(arr[i].label)
}
}
return ret;
}
}
});
</script>
</body>
</html>
