union的一个问题,50分求救

rottenapple 2003-09-29 12:20:39

Select A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,'11' As ProjectName,getdate() as EndDate,1 as Status,A.EmployeeID as EmployeeID
From S_EMPLOYEE A
where A.employeeid not in (select distinct employeeid from s_emp_assignment)
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1) order by EmployeeName
union
Select distinct A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,C.ProjectName as ProjectName,B.EndDate as EndDate,B.WorkStatus as Status,A.EmployeeID as EmployeeID From S_EMPLOYEE A,S_EMP_ASSIGNMENT B,S_PROJECTSET C
Where A.EmployeeID=B.EmployeeID AND B.ProjectID=C.ProjectID AND B.StartDate<getdate() AND B.EndDate>getdate()
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1) order by EmployeeName
查询语句如上,问题很奇怪
目前是上面查询的结果为0,下面有结果,但是抱错,说union有错误
如果上面查询有结果,就可以运行.为什么会这样?
...全文
86 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
sdhdy 2003-09-29
  • 打赏
  • 举报
回复
Select A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,'11' As ProjectName,getdate() as EndDate,1 as Status,A.EmployeeID as EmployeeID
From S_EMPLOYEE A
where A.employeeid not in (select distinct employeeid from s_emp_assignment)
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
union

Select distinct A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,C.ProjectName as ProjectName,B.EndDate as EndDate,B.WorkStatus as Status,A.EmployeeID as EmployeeID From S_EMPLOYEE A,S_EMP_ASSIGNMENT B,S_PROJECTSET C
Where A.EmployeeID=B.EmployeeID AND B.ProjectID=C.ProjectID AND B.StartDate<getdate() AND B.EndDate>getdate()
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
order by EmployeeName
aierong 2003-09-29
  • 打赏
  • 举报
回复
分句里面不能排序

把它们合并做一个表后再排序
txlicenhe 2003-09-29
  • 打赏
  • 举报
回复
Select * from (
Select A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,'11' As ProjectName,getdate() as EndDate,1 as Status,A.EmployeeID as EmployeeID
From S_EMPLOYEE A
where A.employeeid not in (select distinct employeeid from s_emp_assignment)
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1) union
Select distinct A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,C.ProjectName as ProjectName,B.EndDate as EndDate,B.WorkStatus as Status,A.EmployeeID as EmployeeID From S_EMPLOYEE A,S_EMP_ASSIGNMENT B,S_PROJECTSET C
Where A.EmployeeID=B.EmployeeID AND B.ProjectID=C.ProjectID AND B.StartDate<getdate() AND B.EndDate>getdate()
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
) tmp
order by EmployeeName
yujohny 2003-09-29
  • 打赏
  • 举报
回复
只能在最后用Order by

Select A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,'11' As ProjectName,getdate() as EndDate,1 as Status,A.EmployeeID as EmployeeID
From S_EMPLOYEE A
where A.employeeid not in (select distinct employeeid from s_emp_assignment)
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
union
Select distinct A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,C.ProjectName as ProjectName,B.EndDate as EndDate,B.WorkStatus as Status,A.EmployeeID as EmployeeID From S_EMPLOYEE A,S_EMP_ASSIGNMENT B,S_PROJECTSET C
Where A.EmployeeID=B.EmployeeID AND B.ProjectID=C.ProjectID AND B.StartDate<getdate() AND B.EndDate>getdate()
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
order by EmployeeName
gmlxf 2003-09-29
  • 打赏
  • 举报
回复
Select A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,'11' As ProjectName,getdate() as EndDate,1 as Status,A.EmployeeID as EmployeeID
From S_EMPLOYEE A
where A.employeeid not in (select distinct employeeid from s_emp_assignment)
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
union

Select distinct A.FirstName+' '+isNUll(A.MiddleName, ' ')+A.LastName As EmployeeName,C.ProjectName as ProjectName,B.EndDate as EndDate,B.WorkStatus as Status,A.EmployeeID as EmployeeID From S_EMPLOYEE A,S_EMP_ASSIGNMENT B,S_PROJECTSET C
Where A.EmployeeID=B.EmployeeID AND B.ProjectID=C.ProjectID AND B.StartDate<getdate() AND B.EndDate>getdate()
and A.EmployeeID IN (Select EmployeeID From S_EMP_SKILLS Where (SkillID=1 and Proficiency>=0 and Months>=0) group by EmployeeID having count(EmployeeID)=1)
order by EmployeeName

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧