查找用户数(1)
SELECT
SUM(CASE WHEN sm>=1 AND sm<=100 THEN 1 ELSE 0 END) AS sm_1_100,
SUM(CASE WHEN sm>=101 AND sm<=200 THEN 1 ELSE 0 END) AS sm_101_200,
SUM(CASE WHEN sm>=200 THEN 1 ELSE 0 END) AS sm_200_M
FROM(
SELECT
userid,SUM(money)/100 as sm
FROM
order
GROUP BY userid
) A;
查找用户数(2)
SELECT
COUNT(DISTINCT CASE WHEN sm>=1 AND sm<=100 THEN userid ELSE null END) AS sm_1_100,
COUNT(DISTINCT CASE WHEN sm>=101 AND sm<=200 THEN userid ELSE null END) AS sm_101_200,
COUNT(DISTINCT CASE WHEN sm>=200 AND sm<=99 THEN userid ELSE null END) AS sm_200_M,
FROM(
SELECT
userid,SUM(money)/100 as sm
FROM
order
GROUP BY userid
) A;
相比较上面(1)速度要快很多