select tb.amount,count(*) users from (select o.user_id, count(o.oid) as amount from t_orders o where booking_time between (time1, between2) group by o.user_id) tb group by tb.amount order by tb.amount;
select amount,count(*) as users from (select o.user_id, count(o.oid) as amount from t_orders o where booking_time between (time1, between2) group by o.user_id) t group by amount order by amount;
select amount,count(*) as users from (select o.user_id, count(o.oid) as amount from t_orders o where booking_time between (time1, between2) group by o.user_id) t group by amount order by amount;
本帖最后由 ACMAIN_CHM 于 2010-04-15 11:13:49 编辑
LS的都是N个星的,高手呀,谢谢大家了。
LS的兄弟们还在吗?帮我看下这条SQLselect orders , count(*) as users from ( select t_users.id, count(t_orders.t_users_id) as orders from t_users left join t_orders on t_users.id = t_orders.t_users_id /* where t_orders.booking_time >= 20100401000000 and t_orders.booking_time < 20100415000000 */ group by t_users.id ) t group by orders order by orders 我的目的是想把没有订单的用户也统计出来,所有就和用户表(t_users)外左联结了下。 这样在不启用where条件的时候结果是对的,即把订单数量为0的用户数也统计出来了。 但当启用where条件限制时,订单数量为0的用户数就没统计。为什么呢?怎么改进?
上面t_orders.t_users_id 写错了 应该是t_orders.user_id
不早说还有 需要 users where改为and,因为左联时,右表的条件在连接时用,连接后用,你可以想想为什么了select orders , count(*) as users from ( select t_users.id, count(t_orders.t_users_id) as orders from t_users left join t_orders on t_users.id = t_orders.t_users_id
and t_orders.booking_time >= 20100401000000 and t_orders.booking_time < 20100415000000
group by t_users.id ) t group by orders order by orders
select tb.amount,count(*) users from
(select o.user_id, count(o.oid) as amount from t_orders o where booking_time between (time1, between2)
group by o.user_id) tb
group by tb.amount
order by tb.amount;
(select o.user_id, count(o.oid) as amount from t_orders o where booking_time between (time1, between2)
group by o.user_id) t
group by amount
order by amount;
(select o.user_id, count(o.oid) as amount from t_orders o where booking_time between (time1, between2)
group by o.user_id) t
group by amount
order by amount;
from (
select
t_users.id, count(t_orders.t_users_id) as orders
from
t_users left join t_orders on t_users.id = t_orders.t_users_id
/*
where
t_orders.booking_time >= 20100401000000 and
t_orders.booking_time < 20100415000000
*/
group by
t_users.id
) t
group by orders
order by orders
我的目的是想把没有订单的用户也统计出来,所有就和用户表(t_users)外左联结了下。
这样在不启用where条件的时候结果是对的,即把订单数量为0的用户数也统计出来了。
但当启用where条件限制时,订单数量为0的用户数就没统计。为什么呢?怎么改进?
应该是t_orders.user_id
where改为and,因为左联时,右表的条件在连接时用,连接后用,你可以想想为什么了select orders , count(*) as users
from (
select
t_users.id, count(t_orders.t_users_id) as orders
from
t_users left join t_orders on t_users.id = t_orders.t_users_id
and
t_orders.booking_time >= 20100401000000 and
t_orders.booking_time < 20100415000000
group by
t_users.id
) t
group by orders
order by orders