首页 新闻 搜索 专区 学院

sql 多表查询的问题

0
悬赏园豆:20 [已解决问题] 解决于 2017-08-16 11:35

select * from(
select * from(
select mpa02 ,count(a) as '总采购笔数',sum(CONVERT(int,a)) as '<24小时发货',sum(CONVERT(int,b)) as '24-36小时之间发货',
sum(CONVERT(int,c)) as '36-48小时之间发货',sum(CONVERT(int,d)) as '>48小时发货' ,
((round(sum(CONVERT(float,a))/COUNT(a)*0.7,2))+(round(sum(CONVERT(float,b))/COUNT(a)*0.56,2))+ (round(sum(CONVERT(float,c))/COUNT(a)*0.42,2))+
(round(sum(CONVERT(float,d))/COUNT(a)*0,2)))*100 as '发货得分' from (
select mpa02,
(case when datediff(HH,mpa05,mpaa02) <='24' then '1' else '' end ) as 'a' ,
(case when datediff(HH,mpa05,mpaa02) >'24' and DATEDIFF(HH,mpa05,mpaa02)<=36 then '1' else '' end ) as 'b',
(case when datediff(HH,mpa05,mpaa02) >'36' and DATEDIFF(HH,mpa05,mpaa02)<=48 then '1' else '' end ) as 'c',
(case when datediff(HH,mpa05,mpaa02) >'48' then '0' else '' end ) as 'd'

from mpa_file left join mpaa_file on mpa01=mpaa01 left join stq_file on mpa01=stq03 left join maa_file on mpa02=maa12 where mpa02='ZLDZSW'
and mpaacti='Y' and mpa03>='3' and mpa01 like 'MP%'
and (mpa04>='2017-07-13' and mpa04<'2017-08-13'))aa group by mpa02 )a left join
(select mpa02,count(distinct(stq03)) as '发货异常笔数' from mpa_file left join stq_file on mpa01=stq03 where mpa02='ZLDZSW'
and (mpa04>='2017-07-08' and mpa04<'2017-08-13')
and mpaacti='Y' and mpa03>='3' and mpa01 like 'MP%' group by mpa02) b on a.mpa02=b.mpa02 )c left join
(select mpa02,COUNT (distinct(maa12)) as '下架暂缺' from mpa_file left join maa_file on mpa02=maa12 where maa12='ZLDZSW'
and (mpa04>='2017-07-08' and mpa04<'2017-08-13')
and mpaacti='Y' and mpa03>='3' and mpa01 like 'MP%' group by mpa02 ) c on a.mpa02=c.mpa02 )

问题补充:

 加了C表 就报错,请问下哪里有问题啊

男人要爽的主页 男人要爽 | 初学一级 | 园豆:15
提问于:2017-08-15 09:46
< >
分享
最佳答案
0

这语句太长了,不过看报的是)的错误,是不是括号多了或者少了?

收获园豆:20
顾晓北 | 大侠五级 |园豆:9957 | 2017-08-15 09:52
其他回答(3)
0

请问你加入c的哪一段代码?

金琥 | 园豆:2560 (老鸟四级) | 2017-08-15 10:10
0

你这SQL排版实在是太差,估计没人愿意去看,去维护。。

幻天芒 | 园豆:36594 (高人七级) | 2017-08-15 10:12
0

多了个右括号,把最后一个括号去掉

龙行天涯 | 园豆:1821 (小虾三级) | 2017-08-15 14:18
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册