SQL l连表查询 求大神帮忙看看

[紫九]

select y_num_e,m_num_e,deptname from
(
select sum(re_c.rdlc_num) y_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid
inner join department de on de.dept_id=h.dept_id
inner join s_hse_ticket s on s.reghumsid=h.human_sid
where to_char(re_c.create_date,'yyyy')=to_char(sysdate,'yyyy') and s.erifystate='完成' group by re_c.rdlc_num,de.deptname
union all
select sum(re_c.rdlc_num) m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid
inner join department de on de.dept_id=h.dept_id
inner join s_hse_ticket s on s.reghumsid=h.human_sid
where to_char(re_c.create_date,'mm')=to_char(sysdate,'mm') and s.erifystate='完成' group by re_c.rdlc_num,de.deptname
)
如上 报错m_num_e标识符无效 单查询只有字段y_num_e，deptname 原来这样做没出现过这样的问题
请各位大神看看是不是哪写错了

[Yole]

union all 用法有问题啊 所查的两个表union all之后是两列数据，你上面select 后面有三个字段，所以是不对滴！

[what是个穷孩纸]

换个名字试试 是不是列重复了

[紫九]

引用 1 楼 u010192842 的回复:

union all 用法有问题啊 所查的两个表union all之后是两列数据，你上面select 后面有三个字段，所以是不对滴！

但如过我想让两个表中的字段都出来改则么办？我运行过 只出现第一个表的字段

[Yole]

select y_num_e,m_num_e,deptname from
(
select sum(re_c.rdlc_num) y_num_e,'' as m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid
inner join department de on de.dept_id=h.dept_id 
inner join s_hse_ticket s on s.reghumsid=h.human_sid
where to_char(re_c.create_date,'yyyy')=to_char(sysdate,'yyyy') and s.erifystate='完成' 
group by re_c.rdlc_num,de.deptname
union all
select '' as y_num_e ,sum(re_c.rdlc_num) m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid
inner join department de on de.dept_id=h.dept_id 
inner join s_hse_ticket s on s.reghumsid=h.human_sid
where to_char(re_c.create_date,'mm')=to_char(sysdate,'mm') and s.erifystate='完成' 
group by re_c.rdlc_num,de.deptname
)

[紫九]

引用 2 楼 Hsuifengershi 的回复:

换个名字试试 是不是列重复了

那个列重复了？ 我这两张表的不同只有where 后面的条件

[Nick_Ngai]

select sum(re_c.rdlc_num) m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid 你这里应该改成： select sum(re_c.rdlc_num) y_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid 使用union all时，两个查询的列名要一样

[紫九]

引用 4 楼 u010192842 的回复:

select y_num_e,m_num_e,deptname from
(
select sum(re_c.rdlc_num) y_num_e,'' as m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid
inner join department de on de.dept_id=h.dept_id 
inner join s_hse_ticket s on s.reghumsid=h.human_sid
where to_char(re_c.create_date,'yyyy')=to_char(sysdate,'yyyy') and s.erifystate='完成' 
group by re_c.rdlc_num,de.deptname
union all
select '' as y_num_e ,sum(re_c.rdlc_num) m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid
inner join department de on de.dept_id=h.dept_id 
inner join s_hse_ticket s on s.reghumsid=h.human_sid
where to_char(re_c.create_date,'mm')=to_char(sysdate,'mm') and s.erifystate='完成' 
group by re_c.rdlc_num,de.deptname
)

报错 而且你把''赋值给y_num_e有什么意义？

[紫九]

引用 6 楼 anboat 的回复:

select sum(re_c.rdlc_num) m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid 你这里应该改成： select sum(re_c.rdlc_num) y_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid 使用union all时，两个查询的列名要一样

改成这样还有意义吗 一个是按年查 一个是按月查 你把m_num_e改成y_num_e我后面的where条件还有意义吗

[紫九]

这两个SQL语句的联合问题出现在第二个SQL的字段查询不出

[Yole]

赋个空值是为了对应上面你要查3个字段，而下面拼起来就2个字段.... 要么你select * from 一下！

[md5e]

select sum(re_c.rdlc_num) as y_num_e,0 as m_num_e, de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid inner join department de on de.dept_id=h.dept_id inner join s_hse_ticket s on s.reghumsid=h.human_sid where datepart(yyyy,re_c.create_date)=datepart(yyyy,sysdate) and s.erifystate='完成' group by re_c.rdlc_num,de.deptname union all select 0 as y_num_e,sum(re_c.rdlc_num) as m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid inner join department de on de.dept_id=h.dept_id inner join s_hse_ticket s on s.reghumsid=h.human_sid where datepart(mm,re_c.create_date)=datepart(mm,sysdate) and s.erifystate='完成' group by re_c.rdlc_num,de.deptname 先建视图,好像union all不能用form(),语法通不过验证 然后 select y_num_e,m_num_e,deptname from 视图

[紫九]

select y_num_e,m_num_e,deptname from ( select sum(re_c.rdlc_num) y_num_e,null m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid inner join department de on de.dept_id=h.dept_id inner join s_hse_ticket s on s.reghumsid=h.human_sid where to_char(re_c.create_date,'yyyy')=to_char(sysdate,'yyyy') and s.erifystate='完成' group by re_c.rdlc_num,de.deptname union all select null y_num_e,sum(re_c.rdlc_num) m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid inner join department de on de.dept_id=h.dept_id inner join s_hse_ticket s on s.reghumsid=h.human_sid where to_char(re_c.create_date,'mm')=to_char(sysdate,'mm') and s.erifystate='完成' group by re_c.rdlc_num,de.deptname ) 你是说是这样吧 我现在没数据 这样会不会影响结果？

[紫九]

我这只是两个 总共是六个SQL语句的union all 不同的地方只有where后面的判断条件 要拼接出 年-完成，月-完成，日-完成，申请中，年-作废，月-作废的字段 汇总都是按部门分的，但是表都是一样的 我只有这种思路 不知道这样对不对 你有别的方法吗？

[Yole]

你是要按部门拼接到一起么？最后的展现形式是啥样的？ 给个图或是数据呗！

[紫九]

图片 csdn上图太蛋疼了

[紫九]

完成的是次数字段re_c.rdlc_num 但是有三种状态 select * from REPORTVIEWER_count select * from REPORTVIEWER select * from department select * from rec_right select * from hum_list select * from user_list select * from s_hse_ticket 数据来自这几张表

[紫九]

select sum(re_c.rdlc_num) y_num_e,null m_num_e,de.deptname from REPORTVIEWER_count re_c left join hum_list h on re_c.user_id=h.human_sid inner join department de on de.dept_id=h.dept_id inner join s_hse_ticket s on s.reghumsid=h.human_sid 这做的都是连表查询 不同只有where 后面的判断条件