关于SQl语句的问题求大神解答

紫九 2013-12-10 10:16:19

select Applicant_name,HEAD_name,SIGN_name from
(
select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant
union all
select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD
union all
select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign
)

运行报错："SIGN_name"标识符无效
select * from
(
select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant
union all
select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD
union all
select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign
)
运行无错误但是我需要Applicant_name，HEAD_name，SIGN_name 三个字段运行结果只有Applicant_name一个字段
请教各大神应该怎么改

...全文

192 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

紫九 2013-12-10

打赏
举报

谢谢了刚搞完就看到了一样的东西

紫九 2013-12-10

打赏
举报

差不多搞定了 select b.first_name as Applicant_name, c.first_name as HEAD_name, d.first_name as Sign_name from s_zhgl_Send_meal a inner join hum_list b on b.human_id=a.Applicant inner join hum_list c on c.human_id=a.HEAD inner join hum_list d on d.human_id=a.Sign 谢谢各位提供的思路

Yole 2013-12-10

打赏
举报

引用 12 楼 u012526879 的回复:

[quote=引用 10 楼 u010192842 的回复:]


select b.Applicant_name, c.HEAD_name, d.SIGN_name 

from  s_zhgl_Send_meal a left join 

hum_list b on b.human_id=a.Applicant left join 

hum_list c on c.human_id=a.HEAD  left join 

hum_list d on c.human_id=a.Sign

不该用union all 的，之前没指细看！

select b.Applicant_name, c.HEAD_name, d.SIGN_name from s_zhgl_Send_meal a left join hum_list b on b.human_id=a.Applicant left join hum_list c on c.human_id=a.HEAD left join hum_list d on d.human_id=a.Sign 这个还是不行 ORA-00904："D"."SIGN_name"标识符无效 b.Applicant_name, c.HEAD_name, d.SIGN_name 都是无效的标识符好无奈啊[/quote] b.xxx as Applicant_name, c.xxx as HEAD_name,d.xxx as SIGN_name xxx 是你hum_list表中名字那个字段

紫九 2013-12-10

打赏
举报

引用 10 楼 u010192842 的回复:


select b.Applicant_name, c.HEAD_name, d.SIGN_name 

from  s_zhgl_Send_meal a left join 

hum_list b on b.human_id=a.Applicant left join 

hum_list c on c.human_id=a.HEAD  left join 

hum_list d on c.human_id=a.Sign

不该用union all 的，之前没指细看！

紫九 2013-12-10

打赏
举报

引用 9 楼 liuchaolin 的回复:

首先要了解union all的用途 union all==> hum_list.first_name hum_list.first_name hum_list.first_name 并不是 hum_list.first_name,hum_list.first_name,hum_list.first_name

那应该怎么改？我是初学者

Yole 2013-12-10

打赏
举报


select b.Applicant_name, c.HEAD_name, d.SIGN_name 

from  s_zhgl_Send_meal a left join 

hum_list b on b.human_id=a.Applicant left join 

hum_list c on c.human_id=a.HEAD  left join 

hum_list d on c.human_id=a.Sign

不该用union all 的，之前没指细看！

md5e 2013-12-10

打赏
举报

首先要了解union all的用途 union all==> hum_list.first_name hum_list.first_name hum_list.first_name 并不是 hum_list.first_name,hum_list.first_name,hum_list.first_name

紫九 2013-12-10

打赏
举报

不行和 select * from ( select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant union all select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD union all select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign ) 运行结果差不多有三个字段但是数据都在Applicant_name下有点想法了我再看看

Yole 2013-12-10

打赏
举报


select a.Applicant_name,'' as HEAD_name,'' as SIGN_name from 
(
select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant
union all
select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD
union all
select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign
) a

紫九 2013-12-10

打赏
举报

select hum_list.first_name Applicant_name,hum_list.first_name HEAD_name,hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant or hum_list.human_id=s_zhgl_Send_meal.HEAD or hum_list.human_id=s_zhgl_Send_meal.Sign 用or 得出来的是重复的数据但是有三个字段用and 得出来的无数据但是也有三个字段数据库里面是三个字段下个有一个数据 Applicant，HEAD ，Sign 三个字段对应的汉字都是从hum_list表中的first_name（显示的是汉字）字段中取出的human_id（对应的ID）

紫九 2013-12-10

打赏
举报

貌似不行直接运行不通过了晕

蝶恋花雨 2013-12-10

打赏
举报

用with as

with ResultData as 
{
select hum_list.first_name Applicant_name,hum_list.first_name HEAD_name,hum_list.first_name SIGN_name
 from hum_list,s_zhgl_Send_meal  where hum_list.human_id=s_zhgl_Send_meal.Applicant and
 hum_list.human_id=s_zhgl_Send_meal.HEAD and hum_list.human_id=s_zhgl_Send_meal.Sign
//and 你试试行不行不行改成or试试 然后看看需不需要加group by 
}
select Applicant_name,HEAD_name,SIGN_name from ResultData //看看需不需要加group by

紫九 2013-12-10

打赏
举报

ORA-00904："A"."SIGN_NAME" 标识符无效

紫九 2013-12-10

打赏
举报

额还是报错

Yole 2013-12-10

打赏
举报

引用楼主 u012526879 的回复:

select Applicant_name,HEAD_name,SIGN_name from ( select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant union all select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD union all select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign ) 运行报错："SIGN_name"标识符无效 select * from ( select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant union all select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD union all select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign ) 运行无错误但是我需要Applicant_name，HEAD_name，SIGN_name 三个字段运行结果只有Applicant_name一个字段请教各大神应该怎么改


select a.Applicant_name,a.HEAD_name,a.SIGN_name from 
(
select hum_list.first_name Applicant_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Applicant
union all
select hum_list.first_name HEAD_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.HEAD
union all
select hum_list.first_name SIGN_name from hum_list,s_zhgl_Send_meal where hum_list.human_id=s_zhgl_Send_meal.Sign
) a

语法有错误，后面加个别名就好了。