📄 inbetween.out
字号:
ij> -- type mismatchesselect i from t where i in (i, i, e);ERROR 42818: Comparisons between 'INTEGER' and 'DATE' are not supported.ij> select i from t where i in (i, i, t);ERROR 42818: Comparisons between 'INTEGER' and 'TIME' are not supported.ij> select i from t where i in (i, i, p);ERROR 42818: Comparisons between 'INTEGER' and 'TIMESTAMP' are not supported.ij> select i from t where e in (e, p, e);ERROR 42818: Comparisons between 'DATE' and 'TIMESTAMP' are not supported.ij> select i from t where 1 in (p, 2, 1);ERROR 42818: Comparisons between 'INTEGER' and 'TIMESTAMP' are not supported.ij> -- positive tests-- type comparisonsselect i from t where i in (s, r, i, d, 40e1);I -----------0 -1 ij> select s from t where s in (s, r, i, d, 40e1);S ------100 -100 ij> select r from t where r in (s, r, i, d, 40e1);R -------------300.0 -300.0 ij> select d from t where d in (s, r, i, d, 40e1);D ----------------------200.0 -200.0 ij> select i from t where 40e1 in (s, r, i, d, 40e1);I -----------NULL 0 -1 ij> select c from t where c in (c, v, 'goodbye');C ----------hello goodbye ij> select v from t where v in (c, v, 'goodbye');V --------------------------------------------------everyone is here everyone is there ij> select i from t where 'goodbye' in (c, v, 'goodbye');I -----------NULL 0 -1 ij> select i from t where 'xxxxxxFILTERED-TIMESTAMPxxxxxin (p, 'xxxxxxFILTERED-TIMESTAMPxxxxx);I -----------NULL 0 -1 ij> select p from t where p in (p, 'xxxxxxFILTERED-TIMESTAMPxxxxx);P --------------------------xxxxxxFILTERED-TIMESTAMPxxxxxxxxxxxFILTERED-TIMESTAMPxxxxxij> -- not (test clone() once its implemented)select i from t where i not in (i, i);I -----------ij> select i from t where s not in (s, s);I -----------ij> select i from t where c not in (c, c);I -----------ij> select i from t where v not in (v, v);I -----------ij> select i from t where d not in (d, d);I -----------ij> select i from t where r not in (r, r);I -----------ij> select i from t where e not in (e, e);I -----------ij> select i from t where t not in (t, t);I -----------ij> select i from t where p not in (p, p);I -----------ij> -- more notsselect i from t where i not in (0, 9, 8, 2, 7);I ------------1 ij> select i from t where i not in (0, 9, 8, 2, 7);I ------------1 ij> select i from t where not i not in (0, 9, 8, 2, 7);I -----------0 ij> -- 1 element listselect s from t where s in (100);S ------100 ij> -- left side of expressionselect s from t where (s in (100));S ------100 ij> select s from t where (s in (100));S ------100 ij> -- complex expressionsselect i from t where i in (1, 3, 5, 6, (select i from ss where i = 2) - 2);I -----------0 ij> select * from test where i in (sqrt(d),{fn abs (i)}, -6);I |D ----------------------------------2 |4.0 3 |10.0 4 |12.0 5 |25.0 10 |100.0 -6 |36.0 ij> select * from test where sqrt(d) in (i, 4);I |D ----------------------------------2 |4.0 5 |25.0 10 |100.0 ij> select * from test where (i+d) in (6, 30);I |D ----------------------------------2 |4.0 5 |25.0 -6 |36.0 ij> select * from test where sqrt(d) in (i);I |D ----------------------------------2 |4.0 5 |25.0 10 |100.0 ij> select * from test where {fn abs (i)} in (i);I |D ----------------------------------2 |4.0 3 |10.0 4 |12.0 5 |25.0 10 |100.0 ij> select * from test where {fn abs (i)} not in (i);I |D -----------------------------------6 |36.0 ij> select * from test where (i+d) not in (6, 30);I |D ----------------------------------3 |10.0 4 |12.0 10 |100.0 ij> select * from test where sqrt(d) not in (5, 10, 2);I |D ----------------------------------3 |10.0 4 |12.0 -6 |36.0 ij> -- subquery inselect i from t where (select i from ss where i = 2) in (1, 2);I -----------NULL 0 -1 ij> -- derived tableselect * from (select * from t where i in (1, 3, 5, 6, (select i from ss where i = 2) - 2)) a;I |S |C |V |D |R |E |T |P --------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |100 |hello |everyone is here |200.0 |300.0 |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxxij> update ss set i = 5 where i in (2, 3, 40e1);1 row inserted/updated/deletedij> select * from ss;I -----------1 1 5 ij> -- delete - where clausedelete from ss where i not in (5, 3);2 rows inserted/updated/deletedij> select * from ss;I -----------5 ij> -- in/between create table u (c1 integer);0 rows inserted/updated/deletedij> insert into u values null;1 row inserted/updated/deletedij> insert into u values 1;1 row inserted/updated/deletedij> insert into u values null;1 row inserted/updated/deletedij> insert into u values 2;1 row inserted/updated/deletedij> select * from u where c1 between 2 and 3;C1 -----------2 ij> select * from u where c1 in (2, 3, 0, 1);C1 -----------1 2 ij> -- add some more rows before testing static in list xforminsert into t values (20, 200, 'maybe', 'noone is here', 800.0e0, 1000.0e0, '1892-01-01', '07:30:30', 'xxxxxxFILTERED-TIMESTAMPxxxxx);1 row inserted/updated/deletedij> insert into t values (-50, -200, 'never', 'noone is there', -800.0e0, -10300.0e0, '2992-01-02', '19:30:59', 'xxxxxxFILTERED-TIMESTAMPxxxxx);1 row inserted/updated/deletedij> -- test the static in list xform for the various typesselect i from t;I -----------NULL 0 -1 20 -50 ij> select i from t where i in (80, 20, -60, -1);I ------------1 20 ij> select s from t;S ------NULL 100 -100 200 -200 ij> select s from t where s in (100, -200, -400);S ------100 -200 ij> select c from t;C ----------NULL hello goodbye maybe never ij> select c from t where c in ('a', 'goodbye', '');C ----------goodbye ij> select v from t;V --------------------------------------------------NULL everyone is here everyone is there noone is here noone is there ij> select v from t where v in ('noone is there', 'everyone is here', '');⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -