select count(*) cnt
from
(
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, n1.smc as bt from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,ecoupons n where m.ssdx=n.iyhqbs )
and (m1.ssdx=n1.iyhqbs)
UNION
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, a1.dgbt as bt from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,dgsp a where m.ssdx=a.dgbs )
and (m1.ssdx=a1.dgbs)
union
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, b1.tgbt as bt from cxlyb m1,tgsp b1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,tgsp b where m.ssdx=b.tgbs )
and (m1.ssdx=b1.tgbs)
) t
from
(
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, n1.smc as bt from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,ecoupons n where m.ssdx=n.iyhqbs )
and (m1.ssdx=n1.iyhqbs)
UNION
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, a1.dgbt as bt from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,dgsp a where m.ssdx=a.dgbs )
and (m1.ssdx=a1.dgbs)
union
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, b1.tgbt as bt from cxlyb m1,tgsp b1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,tgsp b where m.ssdx=b.tgbs )
and (m1.ssdx=b1.tgbs)
) t
select count(*) as 总数 from (select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, n1.smc as bt from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,ecoupons n where m.ssdx=n.iyhqbs )
and (m1.ssdx=n1.iyhqbs)
UNION
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, a1.dgbt as bt from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,dgsp a where m.ssdx=a.dgbs )
and (m1.ssdx=a1.dgbs)
union
select distinct m1.lybs,m1.ssdx,m1.yhbs,m1.yhm,convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl, b1.tgbt as bt from cxlyb m1,tgsp b1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,tgsp b where m.ssdx=b.tgbs )
and (m1.ssdx=b1.tgbs)) t
select count(*)
from
(
select distinct m1.lybs,
m1.ssdx,
m1.yhbs,
m1.yhm,
convert(varchar(50),
m1.lyrq,121) as lyrq,
m1.ssfl,
n1.smc as bt ]
from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,ecoupons n where m.ssdx=n.iyhqbs )
and (m1.ssdx=n1.iyhqbs)
UNION
select distinct m1.lybs,
m1.ssdx,
m1.yhbs,
m1.yhm,
convert(varchar(50),
m1.lyrq,121) as lyrq,
m1.ssfl,
a1.dgbt as bt
from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,dgsp a where m.ssdx=a.dgbs )
and (m1.ssdx=a1.dgbs)
union
select distinct m1.lybs,
m1.ssdx,
m1.yhbs,
m1.yhm,
convert(varchar(50),m1.lyrq,121) as lyrq,
m1.ssfl,
b1.tgbt as bt
from cxlyb m1,tgsp b1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,tgsp b where m.ssdx=b.tgbs )
and (m1.ssdx=b1.tgbs)
) as TB
where m1.lybs in(select distinct m.lybs from cxlyb m,ecoupons n where m.ssdx=n.iyhqbs )
and (m1.ssdx=n1.iyhqbs)) + (select count(*) from cxlyb m1,dgsp a1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,dgsp a where m.ssdx=a.dgbs )
and (m1.ssdx=a1.dgbs)) + (select count(*) from cxlyb m1,tgsp b1,ecoupons n1
where m1.lybs in(select distinct m.lybs from cxlyb m,tgsp b where m.ssdx=b.tgbs )
and (m1.ssdx=b1.tgbs))) as varchar) + '条记录'