我要评论数据字典
1.需求
2.表机构设计
| id | varchar | 32 | 主键 |
|---|---|---|---|
| name | varchar | 32 | 枚举名称 |
| value | varchar | 32 | 枚举值 |
| type | varchar | 32 | 枚举类型 |
| grade | int | 1 | 等级（1：一级，2：二级，3：三级依次类推） |
| sort | int | 11 | 排序 |
| pid | varchar | 32 | 枚举上级id |
| del | int | 1 | 0正常1删除 |
2.1 建表语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for enum
-- ----------------------------
DROP TABLE IF EXISTS `enum`;
CREATE TABLE `enum` (
`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '枚举名称',
`value` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '枚举值',
`type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '枚举类型',
`grade` int(1) NULL DEFAULT 1 COMMENT '等级',
`sort` int(11) NULL DEFAULT NULL COMMENT '排序',
`pid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '枚举上级id',
`del` int(1) NULL DEFAULT 0 COMMENT '0正常1删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
3.字段说明
4.查询
1.下拉选查询方式
获取一级栏目
SELECT
`value` AS id,
`name` AS NAME
FROM
`enum` where `type` = 'TELECOM_TRADE_TYPE'
AND grade = 1
AND del = 0
order by sort
查询子级栏目
SELECT
`value` as id,
`name` as name
FROM
`enum`
where `type` = 'TELECOM_TRADE_TYPE'
AND pid = #{pid}
and del = 0
order by sort
2.树状结构查询
SELECT
a.VALUE
AS id,
a.NAME AS NAME,
b.
VALUE
AS id1,
b.NAME AS name1
FROM
`enum` a
LEFT JOIN `enum` b ON b.pid = a.`value`
AND b.`del` = 0
WHERE
a.type = #{type} AND a.`del` = 0 and a.`grade`=1 ORDER BY a.`sort`,b.`sort`
发表评论