MYSQL-常用SQL

最新推荐文章于 2024-12-02 22:10:13 发布

原创最新推荐文章于 2024-12-02 22:10:13 发布 · 610 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了两种实用的SQL查询技巧：一是实现基于特定条件的用户排名，二是按天统计包含多个汇总指标的报表。通过复杂的子查询和聚合函数，展示了如何在SQL中灵活运用这些技术来满足业务需求。

1、排名：

select P.* ,

    (SELECT COUNT(*)+1 FROM
    (
        select uid,sum(money)/100 as sum_money ,max(create_date) as last_time
        FROM
        order
        WHERE status='P'
        AND create_date BETWEEN '2012-01-01' AND '2012-02-01'
        group by uid
    )Q WHERE Q.sum_money>P.sum_money OR (Q.sum_money = P.sum_money AND Q.last_time<P.last_time )
) AS arrange_num
FROM (
    select uid,sum(money)/100 as sum_money ,max(create_date) as last_time
    FROM
    order
    WHERE status='P'
    AND create_date BETWEEN '2012-01-01' AND '2012-02-01'
    group by uid
) P

ORDER BP arrange_num

按天统计的报表

SELECT A.*,

IFNULL(B. c_c2,0) AS count_cc,

IFNULL(B. sum_day_money,0) AS sum_day_money,

FROM (
    SELECT
        IFNULL(insert_date ,"合计") as insert_date,
        count(*) AS u_cout
      count(DISTINCT uu) AS u_discont
    FROM
    tb1
    group by insert_date
    WITH ROLLUP
    ) A
    LEFT JOIN (
        SELECT
                COUNT(DISTINCT field2) c_c2,
                IFNULL(DATE_FORMAT(insert_time,"%Y-%m-%d"),"合计") insert_date,
                SUM(money) AS sum_day_money
        FROM
        tb2
        WHERE cddd = 'S'
        GROUP BY insert_date
        WITH ROLLUP
    ) B
     ON A..insert_date = B.insert_date;