sql查询中遇到的一些小小注意点

最新推荐文章于 2024-08-23 10:55:37 发布

甲乙Rocket

最新推荐文章于 2024-08-23 10:55:37 发布

阅读量98

点赞数 1

分类专栏：日常开发文章标签： sql mybatis 数据库

本文链接：https://blog.csdn.net/weixin_46643875/article/details/128449868

版权

日常开发专栏收录该内容

30 篇文章 1 订阅

订阅专栏

1.sql子查询

// 最外层查询是查子查询中查询出来的结果
SELECT
        serverId,
        sum(revenue) as revenue,
        sum(orderCount) as orderCount,
        sum(refundCount) as refundCount,
        sum(guideRevenue) as guideRevenue,
        sum(cardCount) as cardCount,
        sum(activityCount) as activityCount,
        sum(unBindCount) as unBindCount,
        sum(bindCount) as bindCount,
        storeName,
        guideName,
        guideId,
        shopId
        FROM(
        // 子查询
        SELECT
        DISTINCT sgcd.server_id AS serverId,
        sgcd.revenue AS revenue,
        sgcd.pay_order_count AS orderCount,
        sgcd.refund_order_count AS refundCount,
        sgcd.guide_income AS guideRevenue,
        sgcd.coupon_count AS cardCount,
        sgcd.activity_count AS activityCount,
        sgcd.unbind_count AS unBindCount,
        sgcd.new_count AS bindCount,
        store.store_name AS storeName,
        emp.employee_name AS guideName,
        sgcd.guide_id AS guideId ,
        sgcd.shop_id as shopId
        from ser_guide_core_data sgcd
        left join usr_employee emp on sgcd.guide_id = emp.server_id
        left join server_stores store on sgcd.shop_id = store.server_id
        <where>
            sgcd.sys_id = #{sysId}
            <if test="startTime!=null">
                and sgcd.create_date <![CDATA[ >= ]]> STR_TO_DATE(#{startTime}, '%Y-%m-%d %H:%i:%s' )
            </if>
            <if test="endTime!=null">
                and sgcd.create_date <![CDATA[ <= ]]>  STR_TO_DATE(#{endTime}, '%Y-%m-%d %H:%i:%s')
            </if>
            <if test="guideName!=null and guideName!=''">
                and emp.employee_name like concat('%',#{guideName},'%')
            </if>
            <if test="storeName!=null and guideName!=''">
                and store.store_name like concat('%',#{storeName},'%')
            </if>
            <if test="storeOrGuideName!=null and storeOrGuideName!=''">
                and (store.store_name like concat('%',#{storeOrGuideName},'%')
                or emp.employee_name like concat('%',#{storeOrGuideName},'%'))
            </if>
            <if test="shopIds!=null and shopIds.size()!=0">
                and sgcd.shop_id in
                <foreach collection="shopIds" separator="," open="(" close=")" item="shopId">
                    #{shopId}
                </foreach>
            </if>
            <if test="empId!=null">
                and sgcd.guide_id=#{empId}
            </if>
        </where>
        )a
        // 这里一定要有一个临时表定义
        GROUP BY shopId,guideId
        order by storeName ASC,revenue DESC,guideId desc

2.mybatis xml中>;<等符号的拼接方法

3.SQL数据表两个字段关联同一张数据表

数据表temp结构

-- Table "temp" DDL
 
CREATE TABLE `temp` (
  `page_id` bigint(20) DEFAULT NULL,
  `section_id` bigint(20) DEFAULT NULL,
  `visit_category_id` bigint(20) DEFAULT NULL,
  `cart_category_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据表category结构

-- Table "category" DDL
 
CREATE TABLE `category` (
  `category_id` bigint(20) DEFAULT NULL,
  `category_name` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

业务需求
关联temp表和category表，取出visit_category_id和cart_category_id对应的category_name

解决方案
使用两个表命名即可

select a.page_id,
a.section_id,
a.visit_category_id,
b.category_name,
a.cart_category_id,
c.category_name 
from temp a 
// 关联一
left outer join category b on (a.visit_category_id=b.category_id) 
// 关联二
left outer join category c on (a.cart_category_id=c.category_id);