【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
    cert_no ,
    real_xx ,
    mobile_xx ,
    YEAR(curdate()) -
IF(
    length(cert_no) = 18 ,
    substring(cert_no , 7 , 4) ,

IF(
    length(cert_no) = 15 ,
    concat('19' , substring(cert_no , 7 , 2)) ,
    NULL
)
) AS '年龄' ,
 CASE
IF(
    length(cert_no) = 18 ,
    cast(
        substring(cert_no , 17 , 1) AS UNSIGNED
    ) % 2 ,

IF(
    length(cert_no) = 15 ,
    cast(
        substring(cert_no , 15 , 1) AS UNSIGNED
    ) % 2 ,
    3
)
)
WHEN 1 THEN
    '男'
WHEN 0 THEN
    '女'
ELSE
    '未知'
END AS '性别' ,
 CASE LEFT(`cert_no` , 2)
WHEN 11 THEN
    '北京'
WHEN 12 THEN
    '天津'
WHEN 13 THEN
    '河北'
WHEN 14 THEN
    '山西'
WHEN 15 THEN
    '内蒙古'
WHEN 21 THEN
    '辽宁'
WHEN 22 THEN
    '吉林'
WHEN 23 THEN
    '黑龙江'
WHEN 31 THEN
    '上海'
WHEN 32 THEN
    '江苏'
WHEN 33 THEN
    '浙江'
WHEN 34 THEN
    '安徽'
WHEN 35 THEN
    '福建'
WHEN 36 THEN
    '江西'
WHEN 37 THEN
    '山东'
WHEN 41 THEN
    '河南'
WHEN 42 THEN
    '湖北'
WHEN 43 THEN
    '湖南'
WHEN 44 THEN
    '广东'
WHEN 45 THEN
    '广西'
WHEN 46 THEN
    '海南'
WHEN 50 THEN
    '重庆'
WHEN 51 THEN
    '四川'
WHEN 52 THEN
    '贵州'
WHEN 53 THEN
    '云南'
WHEN 54 THEN
    '西藏'
WHEN 61 THEN
    '陕西'
WHEN 62 THEN
    '甘肃'
WHEN 63 THEN
    '青海'
WHEN 64 THEN
    '宁夏'
WHEN 65 THEN
    '新疆'
WHEN '71' THEN
    '台湾'
WHEN '81' THEN
    '香港'
WHEN '82' THEN
    '澳门'
END AS '所属省份' ,
 cast(

    IF(
        length(cert_no) = 18 ,
        substring(cert_no , 7 , 8) ,

    IF(
        length(cert_no) = 15 ,
        concat('19' , substring(cert_no , 7 , 8)) ,
        NULL
    )
    ) AS date
) AS '出生日期' ,
 CASE
WHEN length(cert_no) = 18 THEN
    CASE
WHEN substr(cert_no , 11 , 2) = 01 THEN
    '鼠'
WHEN substr(cert_no , 11 , 2) = 02 THEN
    '牛'
WHEN substr(cert_no , 11 , 2) = 03 THEN
    '虎'
WHEN substr(cert_no , 11 , 2) = 04 THEN
    '兔'
WHEN substr(cert_no , 11 , 2) = 05 THEN
    '龙'
WHEN substr(cert_no , 11 , 2) = 06 THEN
    '蛇'
WHEN substr(cert_no , 11 , 2) = 07 THEN
    '马'
WHEN substr(cert_no , 11 , 2) = 08 THEN
    '羊'
WHEN substr(cert_no , 11 , 2) = 09 THEN
    '猴'
WHEN substr(cert_no , 11 , 2) = 10 THEN
    '鸡'
WHEN substr(cert_no , 11 , 2) = 11 THEN
    '狗'
WHEN substr(cert_no , 11 , 2) = 12 THEN
    '猪'
ELSE
    NULL
END
ELSE
    NULL
END AS '生肖' ,
 CASE
WHEN length(cert_no) = 18 THEN
    CASE
WHEN substr(cert_no , 12 , 3) >= 312
AND substr(cert_no , 12 , 3) <= 419 THEN
    '白羊座'
WHEN substr(cert_no , 12 , 3) >= 420
AND substr(cert_no , 12 , 3) <= 520 THEN
    '金牛座'
WHEN substr(cert_no , 12 , 3) >= 521
AND substr(cert_no , 12 , 3) <= 621 THEN
    '双子座'
WHEN substr(cert_no , 12 , 3) >= 622
AND substr(cert_no , 12 , 3) <= 722 THEN
    '巨蟹座'
WHEN substr(cert_no , 12 , 3) >= 723
AND substr(cert_no , 12 , 3) <= 822 THEN
    '狮子座'
WHEN substr(cert_no , 12 , 3) >= 823
AND substr(cert_no , 12 , 3) <= 922 THEN
    '处女座'
WHEN substr(cert_no , 12 , 3) >= 923
AND substr(cert_no , 12 , 3) <= 930 THEN
    '天枰座'
WHEN substr(cert_no , 11 , 4) >= 1001
AND substr(cert_no , 11 , 4) <= 1023 THEN
    '天枰座'
WHEN substr(cert_no , 11 , 4) >= 1024
AND substr(cert_no , 11 , 4) <= 1121 THEN
    '天蝎座'
WHEN substr(cert_no , 11 , 4) >= 1122
AND substr(cert_no , 11 , 4) <= 1221 THEN
    '射手座'
WHEN substr(cert_no , 11 , 4) >= 1222
AND substr(cert_no , 11 , 4) <= 1231 THEN
    '魔蝎座'
WHEN substr(cert_no , 12 , 3) >= 101
AND substr(cert_no , 12 , 3) <= 119 THEN
    '魔蝎座'
WHEN substr(cert_no , 12 , 3) >= 120
AND substr(cert_no , 12 , 3) <= 218 THEN
    '水瓶座'
WHEN substr(cert_no , 12 , 3) >= 219
AND substr(cert_no , 12 , 3) < 320 THEN
    '双鱼座'
ELSE
    NULL
END
ELSE
    NULL
END AS '星座'
FROM
    `user`
WHERE
    cert_no != '';

充提差

充提差筛选

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

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