select tr.srctermid as ph,count(tr.id) as cr from tm_recv as tr,tp_user_od as tu where tr.srctermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000' group by tr.srctermid union select ts.desttermid as ph,count(ts.id) as cr from tm_send as ts,tp_user_od as tu where ts.desttermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000' group by ts.desttermid
mysql> select version(); +----------------+ | version() | +----------------+ | 5.0.0-alpha-nt | +----------------+ 1 row in set (0.03 sec)
SELECT a.ph,(a.cr-b.cr) as cr FROM (select tr.srctermid as ph,count(tr.id) as cr from tm_recv as tr,tp_user_od as tu where tr.srctermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000' group by tr.srctermid) as a, (select ts.desttermid as ph,count(ts.id) as cr from tm_send as ts,tp_user_od as tu where ts.desttermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000' group by ts.desttermid) as b WHERE a.ph=b.ph;
union
select ts.desttermid as ph,count(ts.id) as cr from tm_send as ts,tp_user_od as tu where ts.desttermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000' group by ts.desttermid
就是要差,就是说某个用户上行(发送)多,但是没收到消息或者少,我们就手动给他消息,具体是这样的:我们一个电信增值业务,用户可以自由想其他订阅了这个业务的人发送消息,但别人呢不一定回他,所以为不让他发送很多消息都没人回答而失望,所以我们要把他找出来。请用mysqlcc连接urg.vicp.net 用户:sh 密码:123456。或者220.166.201.254(adsl)
+----------------+
| version() |
+----------------+
| 5.0.0-alpha-nt |
+----------------+
1 row in set (0.03 sec)
(select tr.srctermid as ph,count(tr.id) as cr
from tm_recv as tr,tp_user_od as tu
where tr.srctermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000'
group by tr.srctermid) as a,
(select ts.desttermid as ph,count(ts.id) as cr
from tm_send as ts,tp_user_od as tu
where ts.desttermid=tu.msisdn and tu.orderflag=1 and tu.serviceid='124000'
group by ts.desttermid) as b
WHERE a.ph=b.ph;
http://community.csdn.net/Expert/topic/3740/3740539.xml?temp=2.759951E-02
领分。