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,
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;