SQL实战

根据查询结果来更新

-- 失效用户excel表导入到表中，根据失效用户账号去更新正式的表
SELECT * from sys_user su INNER JOIN tst_sys_user tsu on su.USER_NAME = tsu.从账号 WHERE su.STATUS <> 'EXPR'
UPDATE sys_user su
INNER JOIN ( SELECT su.USER_NAME un FROM sys_user su INNER JOIN tst_sys_user tsu ON su.USER_NAME = tsu.从账号 ) su2 ON su.USER_NAME = su2.un 
SET su.STATUS = 'EXPR',
su.attribute12 = "注销-此账号从此不可恢复"

统计最新月份已确认的报表的数量

    SELECT
            mfpa.period,
            COUNT( DISTINCT mfpa.type ) count
        FROM
            moa_fin_publish_archive mfpa
        WHERE
            mfpa.period = (
                SELECT period
                FROM moa_fin_publish_archive
                WHERE confirm_status = 1
                ORDER BY period DESC
                LIMIT 1 )
                AND mfpa.confirm_status = 1

获取已确认的最新报表的信息

        SELECT type, period, creation_date ,draft_num, confirm_status, send_message_status 
        FROM
        (SELECT type, period, creation_date ,draft_num, confirm_status, send_message_status 
        FROM moa_fin_publish_archive
        WHERE confirm_status = 1
        <if test="period != null">AND period = #{period}</if>)    a
        WHERE  (
        SELECT count(1)
        FROM moa_fin_publish_archive b
        WHERE a.type = b.type
        AND a.creation_date < b.creation_date
        AND b.confirm_status = 1
        <if test="period != null">AND b.period = a.period</if>) < 1
        AND a.type != 'FIN_G'
        ORDER BY period desc

之所以在后面不用这个sql是因为我查出来的是一堆冗余字段。没有搞懂真实需求。需求只是统计一下报表的数量够不够，而我把它的具体的信息全查出来了。

找出质量小于50（商品表），销量大于20（订单表）的物品

 SELECT g.id,g.name,g.weight,tr.total from goods g
 inner join 
 (SELECT t.goods_id,SUM(t.count) total from trans t  group by t.goods_id HAVING SUM(t.count)>20) tr
 on tr.id = g.id
 and g.weight < 50
 order by g.id

去掉最大最小后的平均值

SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary
FROM salaries where to_date = '9999-01-01';

自己做饶了路，总想去掉最大最小是找到最大最小的id然后用not in。为什么不直接求和减去最大最小就好了呢？