MYSQL-常用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;