分别用WITH-SELECT语句和CASE语句对以下情况进行数据判断:4为二进制数为0111~1010时,属于数据正常;当数据小于0111或者大于1010时,数据均属于不正常。
时间: 2024-03-13 16:47:33 浏览: 23
使用WITH-SELECT语句进行数据判断:
```sql
WITH binary_data AS (
SELECT '0111' AS binary_num UNION ALL
SELECT '1010' AS binary_num UNION ALL
SELECT '0101' AS binary_num UNION ALL
SELECT '0010' AS binary_num UNION ALL
SELECT '1100' AS binary_num
)
SELECT binary_num,
CASE
WHEN binary_num BETWEEN '0111' AND '1010' THEN '数据正常'
ELSE '数据不正常'
END AS data_status
FROM binary_data;
```
使用CASE语句进行数据判断:
```sql
WITH binary_data AS (
SELECT '0111' AS binary_num UNION ALL
SELECT '1010' AS binary_num UNION ALL
SELECT '0101' AS binary_num UNION ALL
SELECT '0010' AS binary_num UNION ALL
SELECT '1100' AS binary_num
)
SELECT binary_num,
CASE
WHEN binary_num < '0111' THEN '数据不正常'
WHEN binary_num > '1010' THEN '数据不正常'
ELSE '数据正常'
END AS data_status
FROM binary_data;
```