27,582
社区成员




select isnull(m.ysxz,n.ysxz) ysxz , m.交易总量 ,m.交易金额,m.交易户数, n.总户数 from
(select ysxz,sum(amount) 交易总量,sum(je) 交易金额, count(*) 交易户数 from sellinfo where date between a and b group by ysxz) m
full join
(select ysxz,count(*) 总户数 from userinfo) n
on m.ysxz = n.ysxz
select a.ysxz,
a.cnt 总户数,
b.cnt 交易户数,
b.amount 交易总量,
b.je 交易金额
from (select ysxz,count(*) cnt from userinfo) a
left join
(select ysxz,sum(amount),sum(je),count(*)cnt from sellinfo where date between a and b group by ysxz)b
on a.ysxz=b.ysxz
select ysxz,
sum(amount) 交易总量,
sum(case type when 1 then 1 else 0 end) 交易户数
sum(je) 交易金额,
count(*) 总户数
from sellinfo
where date between a and b
group by ysxz
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
select a.ysxz,总用户 ,户数=isnull(户数,0),总量=isnull(总量,0),金额=isnull(金额,0)
from (
select ysxz,count(*) as 总用户 from userinfo
) a
left join
(
select ysxz,sum(amount) as 户数,sum(je) as 总量,count(*) as 金额 from sellinfo where date between a and b group by ysxz
)b
on a.ysxz=b.ysxz