ywy表 存放N个业务员的名字
id,nameyj表 存放业务员的业绩(每一条记录表示他做的一个单子)
id,name,price两个表用name关联现在想要返回一个表包含每个业务员的营业额,和每个业务员的做单数量应该如何查询?
也就是最后得到一个这样的表:
name,sumPrice,sumSlipCount
id,nameyj表 存放业务员的业绩(每一条记录表示他做的一个单子)
id,name,price两个表用name关联现在想要返回一个表包含每个业务员的营业额,和每个业务员的做单数量应该如何查询?
也就是最后得到一个这样的表:
name,sumPrice,sumSlipCount
解决方案 »
- 在线杂志问题
- sql高手进!在线等!跪求帮助
- 请教RegisterStartupScript 导致Jqeury报错的问题
- 关于Flash传值乱码的问题
- 如何让IE地址的域名某个字母变大写
- 在SQL SERVER2000中有事件查看器可以看到SQL语句、存储过程等的运行状况而2005中的在哪呢,怎么找不到?
- 在线等待啊,文本框中提交的内容有html代码怎么办
- 刚学ASP。NET 请前辈指点一个与数据库有关的问题
- 跪求128B 字体库
- 用datagrid做录入的时候如何实现:当切换录入的单元格的时候触发一个事件??
- 关于viewstate我又迷糊了.期待绝顶高手出现
- 关于.net后台(.cs)调用前台(.aspx)页面中JS并返回值的问题
你的查询结果与第一个表无关啊。。
select 姓名,sumPrice,sumSlipCount from (select distinct name as 姓名 from ywy) as tb1 left join (select name as n0,sum(price) as sumPrice,count(*) as sumSlipCount from yj group by n0) as tb2 on name=n0我的更长
select type_1,t_sl,t_je,t_sl_jsfw,t_je_jsfw,t_sl_jskf,t_je_jskf,t_sl_jszr,t_je_jszr,t_sl_jszx,t_je_jszx
from (select buy_mfdq0 as type_1,count(*) as t_sl,sum(co_htcjzje) as t_je from tc where 1=1 group by buy_mfdq0) as tj1,
(select type_2,t_sl_jsfw,t_je_jsfw from (select distinct buy_mfdq0 as type_2 from tc) as tc_2 left join (select buy_mfdq0 as type_2_1,count(*) as t_sl_jsfw,sum(co_htcjzje) as t_je_jsfw from tc where co_htlb0='技术服务' group by buy_mfdq0) as tc_2_1 on type_2=type_2_1) as tj2,
(select type_3,t_sl_jskf,t_je_jskf from (select distinct buy_mfdq0 as type_3 from tc) as tc_3 left join (select buy_mfdq0 as type_3_1,count(*) as t_sl_jskf,sum(co_htcjzje) as t_je_jskf from tc where co_htlb0='技术开发' group by buy_mfdq0) as tc_3_1 on type_3=type_3_1) as tj3,
(select type_4,t_sl_jszr,t_je_jszr from (select distinct buy_mfdq0 as type_4 from tc) as tc_4 left join (select buy_mfdq0 as type_4_1,count(*) as t_sl_jszr,sum(co_htcjzje) as t_je_jszr from tc where co_htlb0='技术转让' group by buy_mfdq0) as tc_4_1 on type_4=type_4_1) as tj4,
(select type_5,t_sl_jszx,t_je_jszx from (select distinct buy_mfdq0 as type_5 from tc) as tc_5 left join (select buy_mfdq0 as type_5_1,count(*) as t_sl_jszx,sum(co_htcjzje) as t_je_jszx from tc where co_htlb0='技术咨询' group by buy_mfdq0) as tc_5_1 on type_5=type_5_1) as tj5
where type_1=type_2 and type_1=type_3 and type_1=type_4 and type_1=type_5
group by type_1,t_sl,t_je,t_sl_jsfw,t_je_jsfw,t_sl_jskf,t_je_jskf,t_sl_jszr,t_je_jszr,t_sl_jszx,t_je_jszx
哈哈~
from ywy a
left join
(select name,sum(price) sumprice,count(*) sumSlipCount from yj group by name) b
on a.name=b.name