SQL日常记录

最新推荐文章于 2022-07-13 14:17:23 发布

路过蜻蜓912

最新推荐文章于 2022-07-13 14:17:23 发布

阅读量167

点赞数

文章标签：日常sql

本文链接：https://blog.csdn.net/qq_42520477/article/details/99347408

版权

SELECT * FROM(
SELECT
u.user_code,
IFNULL(u.nick_name,‘0’) as nick_name,
IFNULL(u.user_img,‘0’) as user_img,
IFNULL(u.user_phone,‘0’) as user_phone,
r.user_id,
IFNULL(sum(if(r.rebate_status=1,r.rebate_money,0)),0) AS nosettled_rebate,
IFNULL(sum(if(r.rebate_status=2,r.rebate_money,0)),0) AS settled_rebate,
IFNULL(sum(if(r.rebate_status=3,r.rebate_money,0)),0) AS reclaim_rebate,
IFNULL(sum(if(r.rebate_status !=3,r.rebate_money,0)),0) AS rebate_all
FROM
vip_user_rebate r
LEFT JOIN vip_user_info u on u.id=r.user_id
where 1=1

AND u.user_code LIKE CONCAT(’%’,trim(#{userCode}),’%’)

AND u.user_phone LIKE CONCAT(’%’,trim(#{userPhone}),’%’)

GROUP BY r.user_id
ORDER BY r.user_id
)a ORDER BY

settled_rebate DESC

settled_rebate ASC

nosettled_rebate DESC

nosettled_rebate ASC

reclaim_rebate DESC

reclaim_rebate ASC

rebate_all DESC

rebate_all ASC

rebate_all DESC

==================================================

AND r.rebate_status =#{rebateStatus,jdbcType=VARCHAR}

<![CDATA[ AND r.rebate_time >= #{startDate,jdbcType=TIMESTAMP} ]]>

<![CDATA[ AND r.rebate_time <= #{endDate,jdbcType=TIMESTAMP} ]]>

<?xml version="1.0" encoding="UTF-8" ?>

id, user_id, fans_id, invite_time

select

from vip_user_fans
where id = #{id,jdbcType=VARCHAR}

delete from vip_user_fans
where id = #{id,jdbcType=VARCHAR}

insert into vip_user_fans (id, user_id, fans_id,
invite_time)
values (#{id,jdbcType=VARCHAR}, #{userId,jdbcType=VARCHAR}, #{fansId,jdbcType=VARCHAR},
#{inviteTime,jdbcType=TIMESTAMP})

insert into vip_user_fans

id,
user_id,
fans_id,
invite_time,

#{id,jdbcType=VARCHAR},
#{userId,jdbcType=VARCHAR},
#{fansId,jdbcType=VARCHAR},
#{inviteTime,jdbcType=TIMESTAMP},

update vip_user_fans

user_id = #{userId,jdbcType=VARCHAR},

fans_id = #{fansId,jdbcType=VARCHAR},

invite_time = #{inviteTime,jdbcType=TIMESTAMP},

where id = #{id,jdbcType=VARCHAR}

update vip_user_fans
set user_id = #{userId,jdbcType=VARCHAR},
fans_id = #{fansId,jdbcType=VARCHAR},
invite_time = #{inviteTime,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=VARCHAR}

<resultMap id="VipUserTaskVo" type="com.sunsoft.sys.vo.VipUserTaskVo"
	extends="BaseResultMap">
	<result column="user_code" property="userCode" jdbcType="VARCHAR" />
	<result column="nick_name" property="nickName" jdbcType="VARCHAR" />
	<result column="user_phone" property="userPhone" jdbcType="VARCHAR" />
</resultMap>

SELECT
t.id,
t.user_id,
t.task_type,
t.rebate_status,
t.task_detail,
t.task_gold,
t.tesk_finish_time,
t.task_code,
u.user_code,
u.nick_name,
u.user_phone
FROM
vip_user_task t,
vip_user_info u
WHERE
t.user_id=u.id

AND u.user_code LIKE CONCAT(’%’,trim(#{userCode}),’%’)

AND t.task_type LIKE CONCAT(’%’,trim(#{taskType}),’%’)

AND u.user_phone LIKE CONCAT(’%’,trim(#{userPhone}),’%’)

<![CDATA[ AND t.tesk_finish_time >= #{startDate,jdbcType=TIMESTAMP} ]]>

<![CDATA[ AND t.tesk_finish_time <= #{endDate,jdbcType=TIMESTAMP} ]]>

ORDER BY tesk_finish_time DESC

update vip_user_info
set
user_gold = user_gold+#{packetMoney,jdbcType=INTEGER}
where id = #{userId,jdbcType=VARCHAR}

<resultMap id="VipUserFansVoList" type="com.sunsoft.facade.share.vo.VipUserFansVo"
	extends="BaseResultMap">
	<result column="user_type" property="userType" jdbcType="INTEGER" />
	<result column="nick_name" property="nickName" jdbcType="VARCHAR" />
	<result column="user_img" property="userImg" jdbcType="VARCHAR" />
</resultMap>

select
f.user_id,
u.nick_name,
u.user_img
from vip_user_fans f,
vip_user_info u
where fans_id = #{userId,jdbcType=VARCHAR}
and f.user_id=u.id

<resultMap id="ResultMapSupplierAnnex" type="com.sunsoft.sys.vo.SupplierInfoVo"
	extends="BaseResultMap">

	<collection property="supplierAnnexList" ofType="com.sunsoft.sys.entity.SupplierAnnex"
		column="id"
		select="com.sunsoft.service.admin.dao.SupplierAnnexDao.selectBySupplierId" />
</resultMap>



<select id="selectSupplier" resultMap="ResultMapSupplierAnnex"
	parameterType="java.lang.String">
	select
	*
	from supplier_info
	where
	id = #{id,jdbcType=VARCHAR}
	and status="1"
</select>