# sql 联合 问题,

chuting1 2010-12-07 09:12:06

userinfo :
yhbm(编号) ,ysxz(用水性质)

sellinfo:
yhbm,ysxz,amount,je,date

select ysxz,count(*) from userinfo可以汇总得到各用水性质的用户数

select ysxz,sum(amount),sum(je),count(*) from sellinfo where date between a and b group by ysxz 可以得到某时间段内各用水性质 交易的汇总结果(户数,总量,金额)

ysxz,总户数,交易户数,交易总量,交易金额

...全文
48 点赞 收藏 8

8 条回复

fengyun142415 2010-12-07

chuting1 2010-12-07

chuting1 2010-12-07
select ysxz,count(*) cnt from userinfo group by ysxz少了一点

dawugui 2010-12-07

``````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``````

dawugui 2010-12-07

``````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 ``````

``````最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。

``````

http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

``````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``````

2.7w+

MS-SQL Server 应用实例

2010-12-07 09:12