作者:淘宝杂谈网z | 来源:互联网 | 2023-10-11 20:55
我对两张表进行不同的查询,数据如下
表一:
sql 1:
select xn,xqm,xh 学号,round(avg(jd),2) 平均绩点 from T_XS_CJZLB where SCBZ= 'n' and XH in (select xh from t_xs_xsjbxxb where bjmc='14机械制造与自动化2') and xn='2014-2015' and xqm='2' group by xh,xn,xqm order by xn,xqm,平均绩点
表二:
sql 2:
select
kh 学号,
round((count(lsh)/348),2)*100||'%' 就餐率
from T_YKT_XFJL
where nd='2015' and KH in (select xh from t_xs_xsjbxxb where bjmc='14机械制造与自动化2') and shlxmc='就餐' and yf in (3,4,5,6,7) group by kh order by 就餐率
求大神指教,怎么把两条sql语句合并,让两张表中的数据合为一张表
谢谢!!!!!!!
3 个解决方案
with t as(
select 学号,绩点,'' 餐率 from t1 -- 第一个查询
union all
select 学号,'' 绩点,餐率 from t2 -- 第二个查询
)
select 学号,max(绩点),max(餐率)
from t
group 学号
with t as(
select
xn,xqm,xh 学号,round(avg(jd),2) 平均绩点
from T_XS_CJZLB
where SCBZ= 'n' and XH in (select xh from t_xs_xsjbxxb where bjmc='14机械制造与自动化2') and xn='2014-2015' and xqm='2' group by xh,xn,xqm order by xn,xqm,平均绩点
union all
select
kh 学号,
round((count(lsh)/348),2)*100||'%' 就餐率
from T_YKT_XFJL
where nd='2015' and KH in (select xh from t_xs_xsjbxxb where bjmc='14机械制造与自动化2') and shlxmc='就餐' and yf in (3,4,5,6,7) group by kh order by 就餐率
)
select 学号,平均绩点,就餐率
from t
group 学号
是不是这样,但这样报错啊,显示‘缺失右括号’
麻烦,大神了