【SQL 语句】SQL语句汇总(一)

摘要:本系列文章是想将所有自己写过的SQL语句进行整理和记录,一是为了总结和反思,二是为了便于查找和回顾。之前工作中也写过很多的SQL语句,但是没有进行总结和回顾,随着时间的推移,发现以前很熟悉的知识变的很陌生,归根到底是因为自己没有总结,更没有定期回顾的原因,才会遗忘已学习的知识。只有将自己思考过的知识一点一滴的记录积累,才能不断的提升自己的能力。本篇文章主要记录在一次工作中的筛选查询,其中用得最多的是UNION ALLHAVING的结合来做各种复杂的条件查询。

复杂的汇总比例

求相关数据的比例(类比汇总用户某积分总金额/用户总金额),主要用到不同的表累计不同的金额后通过 UNION ALL 汇总为一个表,然后用 HAVING 对相关的表进行条件筛选。注意,要保持字段列一致,可以用0来填充,即使外层最后 sum 也不影响值。

SELECT
    s.user_id_num ,
    sum(s.cash_xxx) ,
    sum(s.deal_xxx) ,
    ROUND(
        sum(s.cash_xxx) / sum(s.deal_xxx) ,
        4
    )
FROM
    (
        SELECT
            user_id_num ,
            SUM(deal_xxx) AS deal_xxx ,
            0 cash_xxx
        FROM
            (
                SELECT
                    SUM(
                        IFNULL(
                            npsq.quit_principal ,
                            fa.repay_principal
                        )
                    ) AS deal_xxx ,
                    npi.user_id_num AS user_id_num
                FROM
                    new_product_xxx AS npi
                JOIN finance_xxx fa ON npi.id = fa.finance_xxx_id
                LEFT JOIN new_product_sm_xxx npsq ON npi.id = npsq.invest_id
                WHERE
                    npi.state IN(2 , 3 , 4 , 6 , 8)
                AND npi.user_id_num IN(22616868)
                GROUP BY
                    user_id_num
                UNION ALL
                    SELECT
                        SUM(
                            IFNULL(
                                fuq.join_money ,
                                fa.repay_xxx
                            )
                        ) AS deal_xxx ,
                        fim.user_id_num AS user_id_num
                    FROM
                        finance_xxx_merge AS fim
                    INNER JOIN finance_xxx AS fp ON fp.id = fim.packa_xxx
                    JOIN finance_xxx fa ON fim.user_id_num = fa.user_id_num
                    AND fp.id = fa.packa_xxx
                    AND fp.state IN(25 , 30)
                    LEFT JOIN finance_xxx fuq ON fim.packa_xxx = fuq.packa_xxx
                    AND fuq.user_id_num = fim.user_id_num
                    WHERE
                        fuq.quit_xxx != 0
                    AND fuq.cashed_xxx != 1
                    AND fa.is_invalid = 1
                    AND fim.user_id_num IN(22616868)
                    GROUP BY
                        user_id_num
            ) AS u
        GROUP BY
            user_id_num
        UNION ALL
            SELECT
                toq.account_id AS user_id_num ,
                0 deal_xxx ,
                SUM(toq.amount + toq.interest) AS cash_xxx
            FROM
                t_order_xxx AS toq
            WHERE
                toq.type = 1
            AND toq.account_id IN(22616868)
            GROUP BY
                account_id
    ) AS s
GROUP BY
    user_id_num
HAVING
    ROUND(
        sum(s.cash_xxx) / sum(s.deal_xxx) ,
        4
    ) >= 0
AND ROUND(
    sum(s.cash_xxx) / sum(s.deal_xxx) ,
    4
) <= 100

总投资笔数

特点:第一种产品的总投资笔数 UNION ALL 第二种产品的总投资笔数后,使用HAVING对两个产品相加后总的投资笔数进行过滤。

SELECT
 s.user_id_num ,
 sum(s.count) AS count
FROM
 (
  SELECT
   COUNT(user_id_num) AS count ,
   npi.user_id_num
  FROM
   new_product_xxx AS npi
  WHERE
   npi.state IN(2 , 3 , x , x , x)
  AND npi.user_id_num IN(98607)
  GROUP BY
   npi.user_id_num
  UNION ALL
   SELECT
    COUNT(fim.user_id_num) AS count ,
    fim.user_id_num
   FROM
    finance_invest_xxx AS fim
   INNER JOIN finance_xxx AS fp ON fp.id = fim.package_id
   AND fp.state IN(25 , 30)
   WHERE
    fim.user_id_num IN(98607)
   GROUP BY
    fim.user_id_num
 ) AS s
GROUP BY
 s.user_id_num
HAVING
 sum(s.count) >= 0
AND sum(s.count) <= 100

剩余待收本金

特点:先分别算出某个产品的待收本金(总金额-已收金额),再利用UNION ALL 取并集后,使用 HAVING 进行条件过滤。

SELECT
 sum(s.amount) ,
 sum(received_cash) ,
 sum(s.accept_money) ,
 s.user_id_num
FROM
 (
  SELECT
   IFNULL(sum(npi.xxx_amount) , 0) AS amount ,
   IFNULL(sum(npsq.xxx_cash) , 0) AS received_cash ,
   IFNULL(
    sum(npi.xxx_amount) - sum(npsq.xxx_cash) ,
    0
   ) AS accept_money ,
   npi.user_id_num
  FROM
   new_product_xxx AS npi
  LEFT JOIN new_product_smart_xxx AS npsq ON npsq.invest_id = npi.id
  WHERE
   npi.state IN(2 , 3 , x , x , x , x)
  AND npi.user_id_num IN(24345597)
  GROUP BY
   npi.user_id_num
  UNION ALL
   SELECT
    IFNULL(sum(fim.amount) , 0) AS amount ,
    IFNULL(sum(fuq.xxx_money) , 0) AS received_cash ,
    IFNULL(
     sum(fim.amount) - sum(fuq.xxx_money) ,
     0
    ) AS accept_money ,
    fim.user_id_num
   FROM
    finance_invest_xxx AS fim
   INNER JOIN finance_xxx AS fp ON fp.id = fim.package_id
   AND fp.state IN(25 , x)
   LEFT JOIN finance_urgent_xxx AS fuq ON fuq.package_id = fp.id
   WHERE
    fim.user_id_num IN(24345597)
   GROUP BY
    fim.user_id_num
 ) AS s
GROUP BY
 s.user_id_num
HAVING
 sum(s.accept_money) >= 0
AND sum(s.accept_money) <= 1000000000000

总金额比例筛选

特点:分别先求两个产品的相关金额,再用 UNION ALL进行连接后,使用HAVING进行条件过滤(金额先求和再算比例)。

SELECT
 s.user_id_num
FROM
 (
  SELECT
   IFNULL(sum(npi.deal_amount) , 0) AS deal_amount ,
   IFNULL(sum(npsq.received_cash) , 0) AS received_cash ,
   npi.user_id_num AS user_id_num
  FROM
   new_product_xxx AS npi
  LEFT JOIN new_product_smart_xxx npsq ON npi.id = npsq.invest_id
  WHERE
   npi.state IN(2 , 3 , x , x , x)
  AND npi.user_id_num IN(98607)
  GROUP BY
   npi.user_id_num
  UNION ALL
   SELECT
    IFNULL(sum(fim.amount) , 0) AS xxx_deal_amount ,
    IFNULL(sum(fuq.recieve_money) , 0) AS xxx_recieve_money ,
    fim.user_id_num AS xxx_user_id_num
   FROM
    finance_invest_xxx AS fim
   INNER JOIN finance_xxx AS fp ON fp.id = fim.package_id
   AND fp.state IN(25 , 30)
   LEFT JOIN finance_urgent_xxxx fuq ON fp.id = fuq.package_id
   AND fuq.user_id_num = fim.user_id_num
   WHERE
    fuq.quit_state != 0
   AND fim.user_id_num IN(98607)
   GROUP BY
    fim.user_id_num
 ) AS s
GROUP BY
 user_id_num
HAVING
 (
  sum(s.received_cash) / sum(s.deal_amount)
 ) >= 0
AND(
 sum(s.received_cash) / sum(s.deal_amount)
) <= 100

积分兑换比例(总积分/总金额)

特点:分别先求两个产品的相关总金额和总积分,再用 UNION ALL进行连接后,使用HAVING进行条件过滤(积分先求和再算比例)。

SELECT
 s.user_id_num ,
 sum(s.gift_amount) / sum(s.deal_amount) AS ex
FROM
 (
  SELECT
   IFNULL(npi.deal_amount , 0) AS deal_amount ,
   IFNULL(toq.amount , 0) AS gift_amount ,
   npi.user_id_num AS user_id_num
  FROM
   new_product_XXX AS npi
  LEFT JOIN new_XXX npsq ON npi.id = npsq.invest_id
  LEFT JOIN t_XXX AS toq ON toq.quit_id = npsq.id
  AND toq.type = 1
  AND toq.product_type = 0
  WHERE
   npi.state IN(2 , 3 , x , x , x )
  AND npi.user_id_num IN(98607)
  GROUP BY
   npi.user_id_num
  UNION ALL
   SELECT
    IFNULL(fim.amount , 0) AS XXX_deal_amount ,
    IFNULL(toq.amount , 0) AS XXX_gift_amount ,
    fim.user_id_num AS XXX_user_id_num
   FROM
    finance_invest_XXX AS fim
   INNER JOIN finance_XXX AS fp ON fp.id = fim.package_id
   AND fp.state IN(25 , 30)
   LEFT JOIN finance_urgent_XXX fuq ON fp.id = fuq.package_id
   AND fuq.user_id_num = fim.user_id_num
   LEFT JOIN t_XXX AS toq ON toq.quit_id = fuq.id
   AND toq.type = 1
   AND toq.product_type = 1
   WHERE
    fuq.quit_state != 0
   AND fim.user_id_num IN(98607)
   GROUP BY
    fim.user_id_num
 ) AS s
GROUP BY
 user_id_num
HAVING
 (
  sum(s.gift_amount) / sum(s.deal_amount)
 ) >= 0
AND(
 sum(s.gift_amount) / sum(s.deal_amount)
) <= 100

充提差

充提差筛选

特点:一个子查询新表(新充值表)作为主表,左连接一个子查询新表(新提现表),再求两个子查询新表字段的差。

SELECT
 IFNULL(recharge_amt , 0) - IFNULL(cash_amt , 0) AS rc_amt ,
 r_user_id_num
FROM
 (
  SELECT
   IFNULL(sum(money) , 0) AS recharge_amt ,
   r.user_id_num AS r_user_id_num
  FROM
  xxx AS r
  WHERE
   r.user_id_num IN(98607)
  AND r.last_state = 12
  GROUP BY
   r.user_id_num
 ) AS rson
LEFT JOIN(
 SELECT
  IFNULL(sum(appxx_money) , 0) AS cash_amt ,
  c.user_id_num
 FROM
 xxx AS c
 WHERE
  user_id_num IN(98607)
 AND last_state = 10
 GROUP BY
  c.user_id_num
) AS cson ON cson.user_id_num = rson.r_user_id_num
WHERE
 IFNULL(recharge_amt , 0) - IFNULL(cash_amt , 0) >= 0
AND IFNULL(recharge_amt , 0) - IFNULL(cash_amt , 0) <= 100000

用户充提差

特点:两个表先各自求和后进行求差。

SELECT
 (
  IFNULL(recharge_amt - cash_amt , 0)
 ) AS rc_amt
FROM
 (
  SELECT
   IFNULL(sum(money) , 0) AS recharge_amt
  FROM
  xxx AS r
  WHERE
   r.user_id_num = (98607)
  AND r.last_state = 12
 ) AS recharge ,
 (
  SELECT
   IFNULL(sum(appxx_money) , 0) AS cash_amt
  FROM
  xxx AS c
  WHERE
   user_id_num = (98607)
  AND last_state = 10
 ) AS cash

最近一次兑换时间筛选

特点:使用MAX()聚合函数来获取最近的一次时间,最后再用HAVING作为条件过滤。

SELECT
 s.user_id_num
FROM
 (
  SELECT
   accoxxx_id AS user_id_num ,
   MAX(create_time) AS create_time
  FROM
   xxx
  WHERE
   product_type = 0
  AND type = 2  AND accoxxx_id IN(373638)
  GROUP BY
   user_id_num
  HAVING
   (
    create_time IS NOT NULL
    AND create_time >= "2015-04-16"
   )
  AND(
   create_time IS NOT NULL
   AND create_time <= "2020-04-09"
  )
  ORDER BY
   create_time DESC
 ) AS s

UNIONUNION ALL

问:UNIONUNION ALL有什么区别?

  • UNION:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序。
  • UNION ALL:对两个结果集进行并集操作,包括重复行,不进行排序。(因为多了重复行,所以数据条数多于UNION的数据)

WhereHaving

问:WhereHaving有什么区别?

  • 聚合函数是比较WhereHaving的关键。Where 后不能跟聚合函数,因为 Where执行顺序大于聚合函数。在Having中可以使用聚合函数。
  • Where是一个约束声明,使用Where来约束数据库的数据,Where是在结果返回之前起作用的。

  • Having是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作。

  • Where子句的作用是在对查询结果进行分组前,将不符合Where条件的行去掉,即在分组之前过滤数据,使用Where条件显示特定的行。

  • Having子句的作用是筛选满足条件的组,即在分组之后过滤数据,使用Having条件显示特定的组,也可以使用多个分组标准进行分组。

Where、聚合函数、HavingFrom后面的执行顺序:

where>聚合函数(sum,min,max,avg,count)>having

相关推荐

Leave a Reply

Your email address will not be published. Required fields are marked *

微信扫一扫,分享到朋友圈

【SQL 语句】SQL语句汇总(一)
返回顶部

显示

忘记密码?

显示

显示

获取验证码

Close