我有一张员工信息表emp,表里有个字断是员工当休年假的yearh,另有一张表假期表holiday,表里有个假期类型,天数字断,其中有个类型也是年假,我现在想用一SQL语句得到emp表中的年假,holiday表中所有休的年假总天数,以及剩下的天数。 emp表中的name字断和holiday中的name关联,我是这样写的,有错误:
select emp.yearh,(select sum(temp.day) from temp group by temp.name)
from (select holiday.name,holiday.day from holiday where holiday.type='年假') temp,emp
where temp.name=emp.name
select emp.yearh,(select sum(temp.day) from temp group by temp.name)
from (select holiday.name,holiday.day from holiday where holiday.type='年假') temp,emp
where temp.name=emp.name
select e.yearh,sum(t.day) from temp t inner join emp e on
e.name = t.name group by t.name where
group by temp.name where h.type='年假';
e.name = t.name group by t.name where h.type='年假'; 这里的temp是
(select holiday.name,holiday.day from holiday where holiday.type='年假') temp 吗?怎么我运行出错啊,我用的oracle
e.name = h.name group by h.name where h.type='年假'; 刚才看粗心了,把hoilday写成temp了,
不过你楼帖里写temp,temp是什么表?
最好把你的表结构发上来.
lz发表结构、数据、结果吧
这样大家不大好理解你的意思。。
holiday(type varchar,name varchar,day number)
我要得到年假天数据yearh,已休了多少天sum(day),剩下多少天算了,不求了
假如我有如下数据据:
emp
name yearh
a 10
b 13
c 15
holiday
name day type
a 3 病假
a 1 年假
b 2 年假
b 7 年假
a 3 年假
c 15 年假
我要得到:
name yearh(总共年假) sum(day)已休年假 剩下年假
a 10 4 6
b 13 8 5
c 15 15 0
create table emp(
name varchar(20),
yearh int
)create table holiday(
name varchar(20),
day int,
type varchar(20)
)insert into emp
select 'a',10 union all
select 'b',13 union all
select 'c',15insert into holiday
select 'a',3,'病假' union all
select 'a',1,'年假' union all
select 'b',2,'年假' union all
select 'b',7,'年假' union all
select 'a',3,'年假' union all
select 'c',15,'年假'select e.name,e.yearh,sumday=sum(h.[day]) from emp e left join holiday h on e.name=h.name
where h.type='年假'
group by e.name,e.yearhdrop table emp,holiday
name yearh sumday
-------------------- ----------- -----------
a 10 4
b 13 9
c 15 15(所影响的行数为 3 行)**/
记得oracle的关键字处理是用\\的
(h.[day])----》(h.\day\)应该就行了
create table emp(
name varchar(20),
yearh int
)create table holiday(
name varchar(20),
day int,
type varchar(20)
)insert into emp
select 'a',10 union all
select 'b',13 union all
select 'c',15insert into holiday
select 'a',3,'病假' union all
select 'a',1,'年假' union all
select 'b',2,'年假' union all
select 'b',7,'年假' union all
select 'a',3,'年假' union all
select 'c',15,'年假'select e.name,e.yearh,sumday=sum(h.[day]),otherday=(e.yearh-sum(h.[day])) from emp e
left join holiday h on e.name=h.name
where h.type='年假'
group by e.name,e.yearhdrop table emp,holiday/**
name yearh sumday otherday
-------------------- ----------- ----------- -----------
a 10 4 6
b 13 9 4
c 15 15 0(所影响的行数为 3 行)
**/
帖子标题:《JSP页面多表查询的问题(在线等...)!~ 》
帖子地址:http://topic.csdn.net/u/20080619/18/38607f5c-abf7-4bce-b8fc-5405d1fb9af9.html?seed=1984791784
[code={SQL}]
mysql> select a.*,sum(b.day) as sumday ,(a.yearh-sum(b.day)) as leftday
-> from emp a left join holiday b on a.name = b.name
-> where b.type='年假'
-> group by a.name,a.yearh;
+------+-------+--------+---------+
| name | yearh | sumday | leftday |
+------+-------+--------+---------+
| a | 10 | 4 | 6 |
| b | 13 | 19 | -6 |
| c | 15 | 15 | 0 |
+------+-------+--------+---------+
3 rows in set (0.00 sec)
[/code]