/************************************/
然后在表C中选择c.msgid=b.msgid and c.service=b.service的数据,如果有就在循环里加1,没有不加
/************************************/
以上作何解释?如果上面条件符合的记录有多条,是不是也在循环中加多次1?
如果是:select
count(1)
from
A
inner join
B
on
A.mobile = B.mobile and A.service = B.service
inner join
C
on
B.msgid = C.msgid and B.service = C.service
where
A.online = 1如果不是:
select
count(distinct cast(A.id as varchar(10))+'||'+cast(B.id as varchar(10)))
from
A
inner join
B
on
A.mobile = B.mobile and A.service = B.service
inner join
C
on
B.msgid = C.msgid and B.service = C.service
where
A.online = 1
然后在表C中选择c.msgid=b.msgid and c.service=b.service的数据,如果有就在循环里加1,没有不加
/************************************/
以上作何解释?如果上面条件符合的记录有多条,是不是也在循环中加多次1?
如果是:select
count(1)
from
A
inner join
B
on
A.mobile = B.mobile and A.service = B.service
inner join
C
on
B.msgid = C.msgid and B.service = C.service
where
A.online = 1如果不是:
select
count(distinct cast(A.id as varchar(10))+'||'+cast(B.id as varchar(10)))
from
A
inner join
B
on
A.mobile = B.mobile and A.service = B.service
inner join
C
on
B.msgid = C.msgid and B.service = C.service
where
A.online = 1
--最后一条记录的mobile,msgid两个字段
select msgid,mobile from 表B b,(
--在表A中选择Online=1的数据
select distinct mobile,service from 表A where Online=1
)a where
--a.mobile=b.mobile and a.service=b.serviceid
a.mobile=b.mobile and a.service=b.serviceid
--最后一条记录
and not exists(
select * from B where id>b.id and msgid=a.msgid
and a.mobile=mobile and a.service=serviceid)
)b where c.msgid=b.msgid and c.mobile=b.mobile
表B字段id,msgid,mobile,service
表C字段id,msgid,mobile,error
select b.mobile,b.service,max(b.msgid) from B b,(
select a.mobile,a.service from A a,where a.online=1
)a where a.mobile=b.mobile and a.service=b.service
group by b.mobile,b.service )d
where d.msgid=c.msgid and d.service=c.service
不知道這樣行不行
select * from C c,(
select b.mobile,b.service,max(b.msgid) from B b,(
select a.mobile,a.service from A a,where a.online=1
)a where a.mobile=b.mobile and a.service=b.service
group by b.mobile,b.service )d
where d.msgid=c.msgid and d.mobile=c.mobile
where a.Online=1 and a.mobile=b.mobile and
a.service=b.serviceid
order by b.msgid,b.mobile desc) ee
where c.msgid=ee.msgid and c.mobile=ee.mobile不知道是不是要上面的结果,上面返回符合要求的结果数,没有则为0,楼主可以试试看
A.mobile
from
A
inner join
A.
on
A.mobile = B.mobile and A.service = B.service
inner join
C
on
B.msgid = C.msgid and B.mobile= C.mobile
where
A.online = 1