使用 CASE 和条件更新表 (postgresql)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17521084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating a table using CASE and conditions (postgresql)
提问by
i have the tables ::
我有桌子::
CREATE TABLE emp1
(
eid integer NOT NULL,
ename character varying(20),
sid integer,
ssid integer,
CONSTRAINT pk_eid PRIMARY KEY (eid)
);
CREATE TABLE leave_type
(
eid integer,
lid integer,
lnum integer,
emp_bal integer,
sno serial NOT NULL,
CONSTRAINT pk_sno PRIMARY KEY (sno),
CONSTRAINT fk_eid FOREIGN KEY (eid)
REFERENCES emp1 (eid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);//emp_bal-->employee balance leaves which is considered as 8
CREATE TABLE result
(
eid integer,
lid integer,
sd date,
ed date,
sida boolean,
ssida boolean,
rsno serial NOT NULL,
CONSTRAINT pk_rsno PRIMARY KEY (rsno)
);
INSERTED DATA IS
插入的数据是
emp1
-----
eid | ename | sid | ssid
-----+-------+-----+------
1 | a | 2 | 8
3 | c | 4 | 9
2 | b | 3 | 8
4 | d | 2 | 8
5 | e | 2 | 8
6 | f | 4 | 9
(6 rows)
(6 行)
leave_type
----------
eid | lid | lnum | emp_bal | sno
-----+-----+------+---------+-----
1 | 0 | 1 | 8 | 1
3 | 0 | 1 | 8 | 2
5 | 0 | 1 | 8 | 3
1 | 1 | 1 | 8 | 4
1 | 2 | 2 | 8 | 5
(5 rows)
result
-------
eid | lid | sd | ed | sida | ssida | rsno
-----+-----+------------+------------+------+-------+------
1 | 0 | 2013-01-01 | 2013-01-01 | t | f | 1
3 | 0 | 2013-01-09 | 2013-01-09 | t | f | 2
5 | 0 | 2013-01-11 | 2013-01-11 | t | f | 3
1 | 1 | 2013-02-14 | 2013-02-14 | t | f | 4
1 | 2 | 2013-03-15 | 2013-03-16 | f | t | 5
(5 rows)
Query :
询问 ：
I want a approval table to be updated
我想要更新审批表
CREATE TABLE approval
(
eid integer,
lid integer,
asid integer,
bal integer
);
as the output
作为输出
eid | lid | sid |bal
-----+-----+---+--
1 | 0 | 2 | 7
3 | 0 | 4 | 7
5 | 0 | 2 | 7
1 | 1 | 2 | 6
1 | 2 | 8 | 4
(5 rows)
conditions::i tried this query for getting the sid into approval table as stated below::
条件 ::我尝试了这个查询来将 sid 放入批准表中，如下所述：
CASE WHEN r.sida='t'
THEN (update approval set a.asid=e.sid where a.eid=e.eid from emp1 e,approval a)
WHEN r.ssida='t'
THEN (update approval set a.asid=e.ssid where a.eid=e.eid from emp1 e,approval a)
ELSE 0
END
i want even the balance column should be updated basing on sid verification i.e..,
我希望甚至应该根据 sid 验证更新余额列，即
CASE if r.sida='t' then bal=emp1.emp_bal-1 and emp_bal in emp1 should be updated to latest value of bal from approval else if r.ssida='t' then bal=emp_bal-2 and emp_bal in emp1 should be updated to latest value of bal from approval
CASE 如果 r.sida='t' 那么 bal=emp1.emp_bal-1 和 emp1 中的 emp_bal 应该更新为最新的 bal 批准值，否则如果 r.ssida='t' 那么 bal=emp_bal-2 和 emp1 中的 emp_bal应更新为批准后的 bal 的最新值
Is there a way to solve this?
有没有办法解决这个问题？
finally i want to see all the employee leaves which are approved and who approved it and there balance leaves left!!
最后，我想查看所有批准的员工休假和批准的休假，还有剩余的休假！！
Full details are in the [SQL FIDDLE] (http://sqlfiddle.com/#!12/3e6a7/18)
完整的细节在 [SQL FIDDLE] ( http://sqlfiddle.com/#!12/3e6a7/18)
回答by Parth Malhan
You are doing wrong.
你做错了。
Use Case Statement witin a Update Statement instead of using Update Statement Within Case Statement.
在更新语句中使用 Case 语句，而不是在 Case 语句中使用更新语句。
Like
喜欢
Update **Table**
Set **Col1**=
Case when **Col10=1** then 5
else case when **Col10=2** THEN 6
**ELSE 10** END
**ELSE 15** END
