1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
| CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
-- =====================
-- création table "test"
-- =====================
create table test (
id smallint not null primary key,
val smallint not null,
lib char(10) not null
);
insert into test (id,val,lib) values (1, 6,'six');
insert into test (id,val,lib) values (2, 9,'neuf');
insert into test (id,val,lib) values (3, 8,'huit');
insert into test (id,val,lib) values (4,16,'seize');
insert into test (id,val,lib) values (5,11,'onze');
insert into test (id,val,lib) values (6,13,'treize');
select * from test;
ID VAL LIB
======= ======= ==========
1 6 six
2 9 neuf
3 8 huit
4 16 seize
5 11 onze
6 13 treize
commit;
-- ===========
-- Requête N°1
-- ===========
with tab as (select id,
val,
lib,
mod(val,2) as pair,
position('i' in lib) as lettre,
case when (mod(val,2) + position('i' in lib)) = 0 then 1 else 2 end as flag
from test
order by flag, pair, lettre)
select * from tab where flag = (select first 1 flag from tab order by flag);
ID VAL LIB PAIR LETTRE FLAG
======= ======= ========== ======= ============ ============
1 6 six 0 2 2
3 8 huit 0 3 2
4 16 seize 0 3 2
2 9 neuf 1 0 2
5 11 onze 1 0 2
6 13 treize 1 4 2
-- ===========
-- Requête N°2
-- ===========
with tab as (select id,
val,
lib,
mod(val,2) as pair,
position('z' in lib) as lettre,
case when (mod(val,2) + position('z' in lib)) = 0 then 1 else 2 end as flag
from test
order by flag, pair, lettre)
select * from tab where flag = (select first 1 flag from tab order by flag);
ID VAL LIB PAIR LETTRE FLAG
======= ======= ========== ======= ============ ============
3 8 huit 0 0 1
1 6 six 0 0 1
exit;
Appuyez sur une touche pour continuer... |
Partager