• 日期: 2028-02-18
  • 标签: 常规

使用 TiDB 数据库设计资产租赁表

以下是用 TiDB 数据库创建 asset_lease 表的 SQL 语句，用于存储资产租赁信息：

CREATE TABLE `asset_lease` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `unit_id` bigint(20) NOT NULL COMMENT '承租申请单位id',
  `asset_type_id` bigint(20) DEFAULT NULL COMMENT '资产类型id',
  `asset_lease_guide_price` decimal(22,2) DEFAULT NULL COMMENT '资产类型租赁指导价(元)',
  `measurement_unit` varchar(16) DEFAULT NULL COMMENT '价格的计量单位',
  `scheduled_price` decimal(22,2) DEFAULT NULL COMMENT '计划租赁价格(元)',
  `is_public` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否公开交易 0:否 1:是',
  `asset_list` json NOT NULL COMMENT '固定资产列表',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user_id` varchar(64) NOT NULL COMMENT '创建人用户id',
  `update_user_id` varchar(64) NOT NULL COMMENT '更新人用户id',
  `create_user_name` varchar(64) NOT NULL COMMENT '创建人用户名',
  `update_user_name` varchar(64) NOT NULL COMMENT '更新人用户名',
  `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标志 0:未删除 1:已删除',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=61555445 COMMENT='资产租赁';

asset_list 列是一个 JSON 数组，包含以下类型：

private List<AssetPageResp> assetList;

AssetPageResp 类定义如下：

public class AssetPageResp {

    /**
     * 主键
     */
    @ApiModelProperty(value = '主键')
    private Long id;

    /**
     * 资产编号
     */
    @ApiModelProperty(value = '资产编号')
    private String code;

    /**
     * 所属单位id
     */
    @ApiModelProperty(value = '所属单位id')
    private Long unitId;

    /**
     * 所属单位名称
     */
    @ApiModelProperty(value = '所属单位名称')
    private String unitName;

    /**
     * 固定资产类型id
     */
    @ApiModelProperty(value = '固定资产类型id')
    private Long assetTypeId;

    /**
     * 固定资产类型名称
     */
    @ApiModelProperty(value = '固定资产类型名称')
    private String assetTypeName;

    /**
     * 资产性质
     */
    @ApiModelProperty(value = '资产性质')
    private Byte nature;

    /**
     * 资产状态
     */
    @ApiModelProperty(value = '字典 assetState 资产状态 35:闲置 40:在用 45:出租 50:出借 55:报废 60:损毁 65:捐赠转出 70:盘出 75:其他')
    private Byte state;

    /**
     * 资产来源:10在用,20:已出租,30:闲置
     */
    @ApiModelProperty(value = '资产来源 assetSource 10:自购 20:自建 30:接受捐赠 40:盘盈 50:上级拨入 60:拼账转入')
    private Byte source;

    /**
     * 资产名称
     */
    @ApiModelProperty(value = '资产名称')
    private String name;

    /**
     * 购（构）建时间
     */
    @ApiModelProperty(value = '购（构）建时间')
    @JsonFormat(pattern = 'yyyy-MM-dd', timezone = 'GMT+8')
    private LocalDate purchasedDate;

    /**
     * 资产原值(元)
     */
    @ApiModelProperty(value = '资产原值(元)')
    private BigDecimal initialValue;

    /**
     * 资产数量
     */
    @ApiModelProperty(value = '资产数量')
    private Integer quantity;

    /**
     * 计量单位
     */
    @ApiModelProperty(value = '计量单位')
    private String measurementUnit;

    /**
     * 资产累计折旧值
     */
    @ApiModelProperty(value = '资产累计折旧值')
    private BigDecimal depreciationValue;

    /**
     * 资产残值
     */
    @ApiModelProperty(value = '资产残值')
    private BigDecimal residualValue;

    @ApiModelProperty('创建日期(登记日期)')
    @JsonFormat(pattern = 'yyyy-MM-dd', timezone = 'GMT+8')
    private LocalDate createdDate;


    /**
     * 是否允许拆分
     */
    @ApiModelProperty(value = '是否拆分')
    private Boolean splitFlag;

    /**
     * 权证类别 10:有证 20:无证  只有资产类别为房屋及建筑物的资产才会进行校验
     */
    @ApiModelProperty(value = '字典 assetWarrantType 权证类别 10:有证 20:无证 只有资产类别为房屋及建筑物的资产才会进行校验')
    private Byte warrantType;

    /**
     * 只有资产类别为房屋及建筑物的资产才会进行校验且经营模式为代管的时候 必传选项
     */
    @ApiModelProperty(value = '代管方单位id 只有资产类别为房屋及建筑物的资产才会进行校验且经营模式为代管的时候 必填选项')
    private Long agentUnitId;

    /**
     * 只有资产类别为房屋及建筑物的资产才会进行校验 经营模式
     */
    @ApiModelProperty(value = '经营模式 只有资产类别为房屋及建筑物的资产 必填选项 字典 businessPattern 10 自营 20 代管方')
    private AssetManagementTypeEnum managementType;

    /**
     * 父级资产id
     */
    @ApiModelProperty(value = '父级资产id')
    private Long parentId;

    @ApiModelProperty(value = '建筑面积(平方米)')
    private BigDecimal buildingArea;

}

模糊查询 assetList 中的 name 和 code

以下 SQL 语句可以根据 assetList 中的 name 和 code 进行模糊查询，并返回带有数据所在行的 id 的 List<AssetPageResp>：

SELECT 
  a.id,
  a.code,
  a.unit_id AS unitId,
  a.unit_name AS unitName,
  a.asset_type_id AS assetTypeId,
  a.asset_type_name AS assetTypeName,
  a.nature,
  a.state,
  a.source,
  a.name,
  a.purchased_date AS purchasedDate,
  a.initial_value AS initialValue,
  a.quantity,
  a.measurement_unit AS measurementUnit,
  a.depreciation_value AS depreciationValue,
  a.residual_value AS residualValue,
  a.created_date AS createdDate,
  a.split_flag AS splitFlag,
  a.warrant_type AS warrantType,
  a.agent_unit_id AS agentUnitId,
  a.management_type AS managementType,
  a.parent_id AS parentId,
  a.building_area AS buildingArea
FROM (
  SELECT 
    al.id,
    al.code,
    al.unit_id,
    al.unit_name,
    al.asset_type_id,
    al.asset_type_name,
    al.nature,
    al.state,
    al.source,
    al.name,
    al.purchased_date,
    al.initial_value,
    al.quantity,
    al.measurement_unit,
    al.depreciation_value,
    al.residual_value,
    al.created_date,
    al.split_flag,
    al.warrant_type,
    al.agent_unit_id,
    al.management_type,
    al.parent_id,
    al.building_area,
    ROW_NUMBER() OVER (ORDER BY al.id) AS rn
  FROM asset_lease al
  WHERE JSON_CONTAINS(`asset_list`, 
    JSON_OBJECT('name', 'your_name', 'code', 'your_code'),
    '$[*]'
  ) = 1
) a
WHERE a.rn BETWEEN ? AND ?;

请将 your_name 和 your_code 替换为实际的模糊查询条件。同时，将 ? 替换为实际的分页参数，例如起始行号和结束行号。

例如，如果要查询 name 包含 '电脑' 且 code 以 'A' 开头的资产，并获取第 10 到 20 行的数据，则 SQL 语句可以写成：

SELECT 
  a.id,
  a.code,
  a.unit_id AS unitId,
  a.unit_name AS unitName,
  a.asset_type_id AS assetTypeId,
  a.asset_type_name AS assetTypeName,
  a.nature,
  a.state,
  a.source,
  a.name,
  a.purchased_date AS purchasedDate,
  a.initial_value AS initialValue,
  a.quantity,
  a.measurement_unit AS measurementUnit,
  a.depreciation_value AS depreciationValue,
  a.residual_value AS residualValue,
  a.created_date AS createdDate,
  a.split_flag AS splitFlag,
  a.warrant_type AS warrantType,
  a.agent_unit_id AS agentUnitId,
  a.management_type AS managementType,
  a.parent_id AS parentId,
  a.building_area AS buildingArea
FROM (
  SELECT 
    al.id,
    al.code,
    al.unit_id,
    al.unit_name,
    al.asset_type_id,
    al.asset_type_name,
    al.nature,
    al.state,
    al.source,
    al.name,
    al.purchased_date,
    al.initial_value,
    al.quantity,
    al.measurement_unit,
    al.depreciation_value,
    al.residual_value,
    al.created_date,
    al.split_flag,
    al.warrant_type,
    al.agent_unit_id,
    al.management_type,
    al.parent_id,
    al.building_area,
    ROW_NUMBER() OVER (ORDER BY al.id) AS rn
  FROM asset_lease al
  WHERE JSON_CONTAINS(`asset_list`, 
    JSON_OBJECT('name', '%电脑%', 'code', 'A%'),
    '$[*]'
  ) = 1
) a
WHERE a.rn BETWEEN 10 AND 20;

该 SQL 语句会查询 name 中包含 '电脑' 且 code 以 'A' 开头的资产，并返回第 10 到 20 行的数据。

希望以上内容对您有所帮助。

注意:

  • 实际的查询条件和分页参数需要根据您的实际需求进行调整。
  • 使用 JSON_CONTAINS 函数可以实现对 JSON 数组中数据的模糊查询。
  • 使用 ROW_NUMBER() 函数可以实现分页功能。
  • 使用 BETWEEN 语句可以指定要查询的行号范围。

其他查询示例:

  • 查询 name 包含 '手机' 的资产： 
    JSON_CONTAINS(`asset_list`, JSON_OBJECT('name', '%手机%'), '$[*]') = 1
  • 查询 code 以 'B' 开头的资产： 
    JSON_CONTAINS(`asset_list`, JSON_OBJECT('code', 'B%'), '$[*]') = 1
  • 查询 name 为 '电脑' 且 code 为 'A123' 的资产： 
    JSON_CONTAINS(`asset_list`, JSON_OBJECT('name', '电脑', 'code', 'A123'), '$[*]') = 1
TiDB 数据库资产租赁表设计及模糊查询

原文地址: https://www.cveoy.top/t/topic/qkY0 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录