首页 计算机学科 blog sql

# sql-blog

场景：

今天写了一个 针对每个用户查询对应的多个标签，和它的订单数量还有总价

表如下：

user 用户表：

image-20240414151839124

user_tags 用户与标签关联表：

image-20240414151905335

tag_managment 标签表：

image-20240414152025256

order 订单表：

image-20240414152122075

首先我们得考虑如何完成这个业务的查询先想 sql 怎么写：

SELECT
    t.id,
    t.head,
    t.nickname,
    t.open_id,
    t.wx_union_id,
    t.sex,
    t.phone,
    t.state,
    t.address,
    t.create_time,
    t.delete_time,
    t.is_del,
    CAST(COUNT(o.id) / 2 AS UNSIGNED) AS order_count,
	 SUM(o.total_price / 2) AS order_price
FROM
    tab_user t
    LEFT JOIN tab_user_tags t1 ON t1.user_id = t.id
    LEFT JOIN tab_tag_managent t2 ON t2.id = t1.tags_id AND t2.is_del = 0
    LEFT JOIN tab_order o ON o.create_ther = t.id
WHERE
    t.is_del = 0
GROUP BY
    t.id
LIMIT 5;

首先通过左连接对应三张表的关系，然后使用 sql 函数计算出每个用户的所有订单数量和总和，但是我们还需要几个模糊查询完整 sql 如下：

SELECT
    t.id,
    t.head,
    t.nickname,
    t.open_id,
    t.wx_union_id,
    t.sex,
    t.phone,
    t.state,
    t.address,
    t.create_time,
    t.delete_time,
    t.is_del,
    CAST(COUNT(o.id) / 2 AS UNSIGNED) AS order_count,
	 SUM(o.total_price / 2) AS order_price
FROM
    tab_user t
    LEFT JOIN tab_user_tags t1 ON t1.user_id = t.id
    LEFT JOIN tab_tag_managent t2 ON t2.id = t1.tags_id AND t2.is_del = 0
    LEFT JOIN tab_order o ON o.create_ther = t.id
WHERE
    t.is_del = 0
    AND t.nickname LIKE CONCAT( '%', '123', '%' ) 
		AND t.wx_union_id LIKE CONCAT( '%', '123', '%' ) 
		AND t.phone LIKE CONCAT( '%', '123', '%' ) 
		AND t.sex LIKE CONCAT( '%', '男', '%' ) 
		AND t2.tag_name LIKE CONCAT( '%', '123', '%' ) 
		AND t.create_time BETWEEN '2024-04-13 10:11:02' 
		AND '2024-04-15 10:11:08' 
		AND t.id LIKE CONCAT( '%', 1, '%' )
GROUP BY
    t.id
LIMIT 5;

下面编号 java 代码：

这里 除了用户的 Mapper 就省略了：

Mapper

@Mapper
public interface UserMapper extends MPJBaseMapper<User>
{
    Page<User> getAll(Page<User> page,@Param("user") User user);
}

xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--namespace 绑定一个对应的 Dao (Mapper) 接口 -->
<mapper namespace="org.dromara.dkxmodules.mapper.UserMapper">
    <resultMap id="newUserDto" type="org.dromara.dkxmodules.entity.User">
        <result column="id" property="id" />
        <result column="head" property="head" />
        <result column="nickname" property="nickname" />
        <result column="open_id" property="openId" />
        <result column="wx_union_id" property="wxUnionId" />
        <result column="sex" property="sex" />
        <result column="phone" property="phone" />
        <result column="state" property="state" />
        <result column="address" property="address" />
        <result column="create_time" property="createTime" />
        <result column="delete_time" property="deleteTime" />
        <result column="is_del" property="isDel" />
        <result column="order_count" property="orderCount" />
        <result column="order_price" property="orderPrice" />
        <collection property="tags" ofType="java.lang.String"
                    column="id" foreignColumn="id" select="getTags">
        </collection>
    </resultMap>
    <!--SQL 语句头标签 -->
    <!--select: 查询语句，id: 对应接口的方法名，parameterType: 参数类型，resultType: 返回值类型 -->
    <select id="getAll" parameterType="org.dromara.dkxmodules.entity.User" resultMap="newUserDto">
        SELECT
        t.id,
        t.head,
        t.nickname,
        t.open_id,
        t.wx_union_id,
        t.sex,
        t.phone,
        t.state,
        t.address,
        t.create_time,
        t.delete_time,
        t.is_del,
        CAST(COUNT(o.id) / 2 AS UNSIGNED) AS order_count,
        SUM(o.total_price / 2) AS order_price
        FROM
        tab_user t
        LEFT JOIN tab_user_tags t1 ON t1.user_id = t.id
        LEFT JOIN tab_tag_managent t2 ON t2.id = t1.tags_id AND t2.is_del = 0
        LEFT JOIN tab_order o ON o.create_ther = t.id
        <where>
            t.is_del = 0
            <if test="user.nickname != null and user.nickname != ''">
                AND t.nickname LIKE CONCAT('%', #{user.nickname}, '%')
            </if>
            <if test="user.wxUnionId != null and user.wxUnionId != ''">
                AND t.wx_union_id LIKE CONCAT('%', #{user.wxUnionId}, '%')
            </if>
            <if test="user.phone != null and user.phone != ''">
                AND t.phone LIKE CONCAT('%', #{user.phone}, '%')
            </if>
            <if test="user.sex != null and user.sex != ''">
                AND t.sex LIKE CONCAT('%', #{user.sex}, '%')
            </if>
            <if test="user.tagsName != null and user.tagsName != ''">
                AND t2.tag_name LIKE CONCAT('%', #{user.tagsName}, '%')
            </if>
            <if test="user.startDate != null and user.startDate != '' and user.endDate != null and user.endDate != ''">
                AND t.create_time BETWEEN #{user.startDate} AND #{user.endDate}
            </if>
            <if test="user.id != null and user.id != ''">
                AND t.id LIKE CONCAT('%', #{user.id}, '%')
            </if>
        </where>
        GROUP BY
        t.id;
    </select>
    <select id="getTags" resultType="java.lang.String">
        SELECT
            tag_name
        FROM
            tab_tag_managent ttm
                LEFT JOIN tab_user_tags tut ON tut.tags_id = ttm.id
                LEFT JOIN tab_user tu ON tu.id = tut.user_id
        WHERE
            tu.id = #{id}
    </select>
</mapper>
由于我们还需要用户的所有标签信息所以下面写了一个 getTags 的 select 这个会返回到实体类的集合字段中，其中 resultMap 中的 collection 里面要 column="id" foreignColumn="id"，这里会把查询的用户 id 传入 并找到 select 对应的查询然后进行查询返回结果到 tags 字段中返回类型是 String 类型的集合
实体类
/**
 * 用户表
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "tab_user")
public class User
{
    /**
     * 用户 id
     */
    @ExcelIgnore
    @TableId(type = IdType.AUTO)
    private BigInteger id;
    /**
     * 用户头像
     */
    @ExcelIgnore
    private String head;
    /**
     * 用户昵称
     */
    @ExcelProperty("用户昵称")
    private String nickname;
    /**
     * 微信登录标识 id
     */
    @ExcelProperty("微信登录标识")
    private String openId;
    /**
     * 微信号码
     */
    @ExcelProperty("微信号码")
    private String wxUnionId;
    /**
     * 用户性别
     */
    @ExcelProperty("用户性别")
    @ExcelDictFormat(readConverterExp = "0=男,1=女")
    private String sex;
    /**
     * 用户手机号
     */
    @ExcelProperty("用户手机号")
    private String phone;
    /**
     * 用户状态 (0: 启用，1: 未启用)
     */
    @ExcelProperty("用户状态")
    private String state;
    /**
     * 用户地址
     */
    @ExcelProperty("用户地址")
    private String address;
    /**
     * 创建时间
     */
    @ExcelProperty("创建时间")
    private String createTime;
    /**
     * 删除时间
     */
    @ExcelProperty("删除时间")
    private String deleteTime;
    /**
     * 逻辑删除
     */
    @ExcelIgnore
    @TableLogic
    private Integer isDel;
    // 这里排除的字段是用来查询或者接收数据的
    @ExcelIgnore
    @TableField(exist = false)
    private String tagsName;
    @ExcelIgnore
    @TableField(exist = false)
    private List<String> tags;
    @ExcelIgnore
    @TableField(exist = false)
    private Integer orderCount;
    @ExcelIgnore
    @TableField(exist = false)
    private Integer orderPrice;
    @ExcelIgnore
    @TableField(exist = false)
    private String startDate;
    @ExcelIgnore
    @TableField(exist = false)
    private String endDate;
}
serviceImpl
/**
     * 查询全部数据并分页，模糊查询
     * @return
     */
@Override
public PageResult<User> getAll(PageParams params, User user)
{
   Page<User> page = new Page<>(params.getPageNo(), params.getPageSize());
   Page<User> all = userMapper.getAll(page, user);
   List<User> records = all.getRecords();
   long total = all.getTotal();
   return new PageResult<>(records, total, params.getPageNo(), params.getPageSize());
}
controller
/**
     * 查询全部数据并分页，模糊查询
     * @param pageParams
     * @return
     */
@PostMapping("/getAll")
public PageResult<User> getAll(PageParams pageParams,@RequestBody User user)
{
   return service.getAll(pageParams, user);
}
测试结果：
image-20240414154035783
如果要想返回标签里面是一个对象的话，如下：
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--namespace 绑定一个对应的 Dao (Mapper) 接口 -->
<mapper namespace="org.dromara.dkxmodules.mapper.UserMapper">
    <resultMap id="newUserDto" type="org.dromara.dkxmodules.entity.UserEntity">
        <result column="id" property="id" />
        <result column="head" property="head" />
        <result column="nickname" property="nickname" />
        <result column="open_id" property="openId" />
        <result column="wx_union_id" property="wxUnionId" />
        <result column="sex" property="sex" />
        <result column="phone" property="phone" />
        <result column="state" property="state" />
        <result column="address" property="address" />
        <result column="create_time" property="createTime" />
        <result column="delete_time" property="deleteTime" />
        <result column="is_del" property="isDel" />
        <result column="order_count" property="orderCount" />
        <result column="order_price" property="orderPrice" />
        <collection property="tags" ofType="org.dromara.dkxmodules.entity.vo.UserTagVo"
                    column="id" foreignColumn="id" select="getTags">
        </collection>
    </resultMap>
    <!--SQL 语句头标签 -->
    <!--select: 查询语句，id: 对应接口的方法名，parameterType: 参数类型，resultType: 返回值类型 -->
    <select id="getAll" parameterType="org.dromara.dkxmodules.entity.UserEntity" resultMap="newUserDto">
        SELECT
        t.id,
        t.head,
        t.nickname,
        t.open_id,
        t.wx_union_id,
        t.sex,
        t.phone,
        t.state,
        t.address,
        t.create_time,
        t.delete_time,
        t.is_del,
        CAST(COUNT(o.order_id) / 2 AS UNSIGNED) AS order_count,
        SUM(o.actual_payment / 2) AS order_price
        FROM
        tab_user t
        LEFT JOIN tab_user_tags t1 ON t1.user_id = t.id
        LEFT JOIN tab_tag_managent t2 ON t2.id = t1.tags_id AND t2.is_del = 0
        LEFT JOIN tab_order o ON o.user_id = t.id
        <where>
            t.is_del = 0
            <if test="user.nickname != null and user.nickname != ''">
                AND t.nickname LIKE CONCAT('%', #{user.nickname}, '%')
            </if>
            <if test="user.wxUnionId != null and user.wxUnionId != ''">
                AND t.wx_union_id LIKE CONCAT('%', #{user.wxUnionId}, '%')
            </if>
            <if test="user.phone != null and user.phone != ''">
                AND t.phone LIKE CONCAT('%', #{user.phone}, '%')
            </if>
            <if test="user.sex != null and user.sex != ''">
                AND t.sex LIKE CONCAT('%', #{user.sex}, '%')
            </if>
            <if test="user.tagsName != null and user.tagsName != ''">
                AND t2.tag_name LIKE CONCAT('%', #{user.tagsName}, '%')
            </if>
            <if test="user.tagId != null and user.tagId != ''">
                AND t2.id LIKE CONCAT('%', #{user.tagId}, '%')
            </if>
            <if test="user.startDate != null and user.startDate != '' and user.endDate != null and user.endDate != ''">
                AND t.create_time BETWEEN #{user.startDate} AND #{user.endDate}
            </if>
            <if test="user.id != null and user.id != ''">
                AND t.id LIKE CONCAT('%', #{user.id}, '%')
            </if>
        </where>
        GROUP BY
        t.id,
        t.head,
        t.nickname,
        t.open_id,
        t.wx_union_id,
        t.sex,
        t.phone,
        t.state,
        t.address,
        t.create_time,
        t.delete_time,
        t.is_del
    </select>
    <select id="getTags" resultType="org.dromara.dkxmodules.entity.vo.UserTagVo">
        SELECT
            tag_name AS tagName, tag_color AS tagColor
        FROM
            tab_tag_managent ttm
                LEFT JOIN tab_user_tags tut ON tut.tags_id = ttm.id
                LEFT JOIN tab_user tu ON tu.id = tut.user_id
        WHERE
            tu.id = #{id}
    </select>
</mapper>
把实体类的泛型更改：
@ExcelIgnore
@TableField(exist = false)
private List<UserTagVo> tags;
数据格式：
{
    "items": [
        {
            "id": "1789958514896728066",
            "head": "http://192.168.2.35:9000/ruoyi/2024/05/13/c5988ff5342f4bb4bdee0cb7d3f47bde.jpeg",
            "nickname": "Misjudgment",
            "birthday": null,
            "openId": "o_Pd964vdtzklLVH08zzEpWBsUFE",
            "wxUnionId": "wex324",
            "sex": "0",
            "phone": "19831988276",
            "state": "0",
            "address": "河北省邢台市沙河市十里亭",
            "createTime": "2024-05-13 17:59:00",
            "deleteTime": null,
            "isDel": 0,
            "tagsName": null,
            "tags": [
                {
                    "tagName": "回头客",
                    "tagColor": "2"
                }
            ],
            "orderCount": 0,
            "orderPrice": null,
            "startDate": null,
            "endDate": null,
            "tagId": null,
            "pageSize": 2147483647,
            "pageNo": 0
        }
    ],
    "counts": 1,
    "page": 0,
    "pageSize": 10
}