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中>;<等符号的拼接方法![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/8403062ea544ec64b288045e6f9ee2d9.png)
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);
4.截断删除和del的差别
truncate:会清空表中所有的数据,速度快,不可回滚;实质是删除整张表包括数据再重新创建表;
delete:逐行删除数据,每步删除都是有日志记录的,可以回滚数据;实质是逐行删除表中的数据;