iT邦幫忙

0

SQL with as

sql
ksg 2018-10-05 16:19:391446 瀏覽

请问SQL语法,资料库Postgresql
WITH
aggregate AS(SELECT * FROM table1 where x=1),
inserts AS (
请问这里有办法用IF来判断条件吗?
比如IF EXISTS(select * from aggregate where x=1)
then
do something;
END IF;
)

1 個回答

2
暐翰
iT邦大師 2 級 ‧ 2018-10-05 16:47:44
最佳解答

可以,使用function配合case when

舉例:

檢查是不是2的倍數的Script

CREATE TABLE Table1
    ("x" int)
;
    
INSERT INTO Table1
    ("x")
VALUES
    (1),
    (2),
    (3),
    (4)
;

CREATE FUNCTION isMultiplesOf2(integer) RETURNS boolean
    AS 'select MOD($1,2)=1 ;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
    
WITH aggregate AS(
  SELECT * FROM table1 where x in (1,4)
)
select 
  case when isMultiplesOf2(x) then 'true'
  else 'false' 
  end isMultiplesOf2
from aggregate;    

結果

ismultiplesof2
true
false

線上測試連結DB Fiddle - SQL Database Playground

ksg iT邦新手 5 級 ‧ 2018-10-05 18:23:52 檢舉

了解,感谢大大回答/images/emoticon/emoticon02.gif

暐翰 iT邦大師 2 級 ‧ 2018-10-05 18:32:22 檢舉

/images/emoticon/emoticon12.gif

ksg iT邦新手 5 級 ‧ 2018-10-08 11:27:40 檢舉

WITH aggregate AS(
SELECT * FROM Table1
),
check_table AS(
SELECT
CASE WHEN EXISTS (SELECT * FROM table1 WHERE x in (1,4))
THEN
some_insert_function()
END
RETURNING 1
),
delete_table AS(
DELETE FROM Table1 where 1=1
RETURNING 1
)
请问有办法实现以上的方式吗?
目前想再第二个部分做判断然后Insert,然后再到第三个部分做删除的动作. 感谢
/images/emoticon/emoticon06.gif

我要發表回答

立即登入回答