SELECT id,srcnum,destnum,msisdn,servicecode,flag FROM 表一名 as T1,表二名 as T2,表三名 as T3 WHERE T3.servicecode='aa' and T1.id=T3.id 取出的T1.num-T2.num(num是各个表中的条数字段)
To lawyu(雨淋漓) :非常感谢,无论写得出来不!开小号是因为大号没分了。 周末urg.vicp.net 连不上,我没上班!tm_recv是用户上行的表srctermid是用户的号码,id是自动增长,length(desttermid)=11(length(desttermid)=11的记录是关于业务代码为QR的记录)的是要统计的记录; tm_send是向用户发送消息的表:desttermid是用户的号码,id是自动增长,length(srctermid)=11(length(srctermid)=11的记录是关于业务代码为QR的记录)的是要统计的记录; tp_user_od用户订阅的业务列表:msisdn是用户的号码,servicecode是业务代码,orderflag是用户的该业务是否处于订阅状态,要找servicecode='QR'的。一句话:要统计在上行表和下行表中每个用户的符合要求(关于业务QR的)的记录(tm_recv中length(desttermid)=11)总条数差,并且这个用户在tp_user_od中servicecode='QR'的orderflag='1'的记录。 我写了个这样的句子: select ts.desttermid as ph,count(tr.id) as cr,count(ts.id) as ct from tm_recv as tr,tm_send as ts,tp_user_od as tu where ts.desttermid=tr.srctermid and ts.desttermid=tu.msisdn and tu.orderflag='1' group by tr.srctermid,ts.desttermid 上面这个句子是错误的,希望对理解我的意图有帮助。
如用户的号码是222SELECT 表3.*, ((select count(*) from 表1 where srcnum='222')-(select count(*) from 表2 where destnum='222')) AS x FROM 表3 WHERE msisdn='222';
一条SQL解决不了,使用临时表吧 create temporary table sum_tbl select descnum as num,count(*) as desc_count from 表2 group by descnum;再取结果 select u.srcnum, count(*)-ifnull(s.desc_count,0) as dif_count from 表1 as u left join sum_tbl as s on u.srcnum=s.num left join 表3 as b on u.srcnum=b.msisdn where b.servicecode='aa' and b.flag=1 group by u.srcnum;
SELECT 表3.*, ((select count(*) from 表1 where srcnum=表3.msisdn)-(select count(*) from 表2 where destnum=表3.msisdn)) AS x FROM 表3 WHERE servicecode='aa'and flag=1;
很感谢大家,我早就用多条查询的办法解决了, 在php版有人帮我解决了: http://community.csdn.net/Expert/topic/3740/3740356.xml?temp=.1585504 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;
join 表一 as t1 on t2.id=t1.id
join 表三 as t3 on t1.id=t3.id
where servicecode='aa' and flag=1
就是要差,就是说某个用户上行(发送)多,但是没收到消息或者少,我们就手动给他消息,具体是这样的:我们一个电信增值业务,用户可以自由想其他订阅了这个业务的人发送消息,但别人呢不一定回他,所以为不让他发送很多消息都没人回答而失望,所以我们要把他找出来。请用mysqlcc连接urg.vicp.net 用户:sh 密码:123456。或者220.166.201.254(adsl)小号一定会接帖。
urg.vicp.net 用户:sh 密码:123456
这里面我怎么都没找到有下面字段的表儿啊sctele2和test里的表都不是啊
id,srcnum
id,destnum
id,msisdn
周末urg.vicp.net 连不上,我没上班!tm_recv是用户上行的表srctermid是用户的号码,id是自动增长,length(desttermid)=11(length(desttermid)=11的记录是关于业务代码为QR的记录)的是要统计的记录;
tm_send是向用户发送消息的表:desttermid是用户的号码,id是自动增长,length(srctermid)=11(length(srctermid)=11的记录是关于业务代码为QR的记录)的是要统计的记录;
tp_user_od用户订阅的业务列表:msisdn是用户的号码,servicecode是业务代码,orderflag是用户的该业务是否处于订阅状态,要找servicecode='QR'的。一句话:要统计在上行表和下行表中每个用户的符合要求(关于业务QR的)的记录(tm_recv中length(desttermid)=11)总条数差,并且这个用户在tp_user_od中servicecode='QR'的orderflag='1'的记录。
我写了个这样的句子:
select ts.desttermid as ph,count(tr.id) as cr,count(ts.id) as ct from tm_recv as
tr,tm_send as ts,tp_user_od as tu
where ts.desttermid=tr.srctermid and
ts.desttermid=tu.msisdn and tu.orderflag='1' group by tr.srctermid,ts.desttermid
上面这个句子是错误的,希望对理解我的意图有帮助。
FROM 表3
WHERE msisdn='222';
create temporary table sum_tbl
select descnum as num,count(*) as desc_count
from 表2
group by descnum;再取结果
select u.srcnum, count(*)-ifnull(s.desc_count,0) as dif_count
from 表1 as u
left join sum_tbl as s on u.srcnum=s.num
left join 表3 as b on u.srcnum=b.msisdn
where b.servicecode='aa' and b.flag=1
group by u.srcnum;
表二:向用户发送的信息:id,destnum(用户的号码)
表三:用户的业务定制表:id,msisdn(用户的号码),servicecode(业务名称),flag(是否在订阅状态0,1)“目的查询某个用户上行的条数与收到的条数的差”,就是表一与表二相应用户的记录数之差
我想我没有搞错你有“表一中有数据的号码表三一定有数据,但表二不一定有数据。”
那么:
表一和表三的连接应该使用内连接,这样才可以排除不符合条件的记录
而表一和表二的连接应该使用左连接,这样表一中有而表二中没有的记录在结果中被赋值为null。而你实际上是需要得到的就是这些值为null的记录数思路清楚了,算式写起来也就简单了。
FROM 表3
WHERE servicecode='aa'and flag=1;
在php版有人帮我解决了:
http://community.csdn.net/Expert/topic/3740/3740356.xml?temp=.1585504
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;
http://community.csdn.net/Expert/topic/3740/3740539.xml?temp=2.759951E-02
领分。