try:select b.Dept_Name,a.Emp_Name,a.Emp_Addr,case when a.emp_age <=30 then '青年'when a.emp_age>30 and a.emp_age<=50 then '老年'when a.emp_age>50 then '老年' end from emp a left join dept b on a.dept_id=b.dept_id order by b.dept_name desc, a.emp_id
调试欢乐多
E.Emp_Name,
E.Emp_Addr,
(case when E.Emp_Age<=30 then '青年'
when E.Emp_Age<=50 then '中年'
else then '老年' end
) as Emp_Age
from Dept表 D join Emp表 Eon D.Dept_ID=E.Dept_IDorder by D.Dept_Name desc,E.Emp_ID
case
when a.emp_age <=30
then '青年'
when a.emp_age>30 and a.emp_age<=50
then '老年'
when a.emp_age>50
then '老年'
end
from emp a , dept b
where a.dept_id=b.dept_id
order by b.dept_name desc, a.emp_id asc
case
when a.emp_age <=30
then '青年'
when a.emp_age>30 and a.emp_age<=50
then '老年'
when a.emp_age>50
then '老年'
end
from emp a , dept b
where a.dept_id=b.dept_id
group by dept_id
order by b.dept_name desc, a.emp_id asc
Server: Msg 8120, Level 16, State 1, Line 1
列 'a.Dept_Name' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
Server: Msg 8120, Level 16, State 1, Line 1
列 'b.Emp_Name' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
Emp_ID Dept_ID Emp_Name Emp_Age Emp_Sex Emp_Phone Emp_Addr 1 1 shliu 20 女 51181688 aaaaaaa
2 2 wswang 40 男 51181688 aaaaaaa
3 1 yling 60 女 51181688 aaaaaaa
dept:
Dept_ID Dept_Name
------- --------------------------------------------------
1 a
2 bselect e.dept_name,f.emp_name,f.emp_addr,e.age
from (
select b.dept_id,b.dept_name,c.age,c.emp_id
from
(select * from dept
group by dept_id,dept_name)as b left join
(
select Emp_ID,Dept_ID,
case
when a.emp_age <=30
then '青年'
when a.emp_age>30 and a.emp_age<=50
then '中年'
when a.emp_age>50
then '老年'
end as age
from emp as a)as c
on b.dept_id=c.dept_id) as e
left join emp as f
on e.dept_id=f.dept_id and e.emp_id=f.emp_id
order by e.dept_name desc, f.emp_id asc結果:
dept_name emp_name emp_addr age
-------------------------------------------------- -------- ----------------------------
b wswang aaaaaaa 中年
a shliu aaaaaaa 青年
a yling aaaaaaa 老年
dept_name emp_name emp_addr age
b wswang aaaaaaa 中年
a shliu aaaaaaa 青年
a yling aaaaaaa 老年
case
when a.emp_age <=30
then '青年'
when a.emp_age>30 and a.emp_age<=50
then '老年'
when a.emp_age>50
then '老年'
end as Emp_Age
from emp a , dept b
where a.dept_id=b.dept_id
group by Dept_Name,Emp_Name,Emp_Addr,Emp_Age
order by b.dept_name desc, a.emp_id asc
case
when a.emp_age <=30
then '青年'
when a.emp_age>30 and a.emp_age<=50
then '老年'
when a.emp_age>50
then '老年'
end as Emp_Age
from emp a , dept b
where a.dept_id=b.dept_id
group by b.Dept_Name,a.Emp_Name ,a.Emp_Addr ,a.emp_age
order by b.dept_name desc, a.emp_id asc