优化存储过程查询

Question

Firebird 2.5 db:中有两个表

SL_SPORS
-----------------
ID_PERS  ID_SUMSP
10     2
10     3
11     2

SL_BRUTS
----------------------------------------
ID_PERS  S_SPORS_1  S_SPORS_2  S_SPORS_3 ...
10     0            50         0
11     0             0         0

我需要从第一个表中获取ID_PERS和ID_SUMSP，其中S_SPORS_[ID_SUMSP]在第二个表中为0，ID_SUMSP1到7，在SL_BRUTS中，一个人只有一条记录

我有一个存储过程：

CREATE OR ALTER PROCEDURE SP_VALIDSUM()
RETURNS (RES VARCHAR(500)) AS
....
FOR SELECT A.ID_PERS,S.ID_SUMSP FROM SL_SPORS INTO :ID_PERS,:ID_SUMSP DO
BEGIN
SUMA=0;
EXECUTE STATEMENT 'SELECT FIRST 1 S_SPORS_'||:ID_SUMSP||' FROM SL_BRUTS WHERE ID_PERS='||:ID_PERS INTO :SUMA;
IF (SUMA=0) THEN
BEGIN
RES='SUM 0 FOR ID_PERS='||:ID_PERS||' AND ID_SUMSP='||:ID_SUMSP;" 
SUSPEND;
END
END

它是有效的，但我想知道是否有更好的解决方案，例如，也许我可以使用LIST(..(在单个查询中从第一个表加载所有ID_SUMSP。或者我可以在单个查询上检查表2中的所有1-7字段

Answer 1

您必须更改第二个表的结构(Schema(-阅读Martin Gruber的"Essential SQL"；或关于"；数据库规范化"；。SL_BRUTS表应重新成型为具有三列：ID_PERS和ID_SUMSP以及S_SPORS

• https://en.wikipedia.org/wiki/Database_normalization

进行一次性数据转换，然后对规范化表进行查询。您在SL_BRUTS中创建的结构永远不会允许您进行任何有效的查询，而是最琐碎的查询。

检查以下最后两个查询中的查询执行计划：

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
, rdb$character_set_name
from rdb$database;

VERSION|RDB$CHARACTER_SET_NAME：------|：---------------------------------------------------------------------------------------------------------------------------3.0.5 | UTF8

create table SL_SPORS (
ID_PERS  integer,
ID_SPORS integer,
Primary key (ID_PERS, ID_SPORS)
)

✓

insert into SL_SPORS 
select 10, 2 from rdb$database union all
select 10, 3 from rdb$database union all
select 11, 2 from rdb$database union all
select 20, 3 from rdb$database 

4行影响

select * from sl_spors

ID_PERS|ID_SPORS------：|-------：10|210|311|220|3

<blockquote\

-- CAN this sl_spors_NNN data be NULL ? or not ? what is semantics, what is meaning of it???
create table SL_BROKEN (
ID_PERS  integer primary key,
s_SPORS_1 integer NOT NULL,  
s_SPORS_2 integer NOT NULL,
s_SPORS_3 integer NOT NULL
)

>

✓

create table SL_RAWS (
ID_PERS  integer,
ID_SPORS integer,
S_SPOR integer NOT NULL,
Primary key (ID_PERS, ID_SPORS),
constraint impossible_over_SL_BROKEN 
FOREIGN KEY(ID_PERS, ID_SPORS)
REFERENCES SL_SPORS(ID_PERS, ID_SPORS)
)

✓

-- should throw error over non-existing PERSON - but would it ???
insert into SL_BROKEN
values (-100, 20, 30, 40)

1行影响

-- should throw error over non-existing PERSON - and it would!
insert into SL_RAWS
values (-100, 20, 30)

违反FOREIGN KEY约束"；IMPOSSIBLE_OVER_SL_BROKEN"；在桌子上"；SL_ RAWS"；外键引用目标不存在问题键值为("ID_PERS"=-100，"ID_SPORS"=20(

insert into  SL_BROKEN 
select 10, 0, 50, 0 from rdb$database union all
select 11, 0,  0, 0 from rdb$database

2行影响

select * from SL_BROKEN 

ID_PERS|S_SPORS_1|S_SPORS_2|S_SPORS _3------：|--------：|-------：|---------：-100 | 20 | 30 | 4010|0|50|011|0|0|0

delete from SL_BROKEN where ID_PERS < 0

1行影响

create view SL_TRANSPOSE as
SELECT 1 as ID_SPORS, ID_PERS, S_SPORS_1 as S_SPOR from SL_BROKEN  
union all
SELECT 2 as ID_SPORS, ID_PERS, S_SPORS_2 as S_SPOR from SL_BROKEN  
union all
SELECT 3 as ID_SPORS, ID_PERS, S_SPORS_3 as S_SPOR from SL_BROKEN  

✓

select * from SL_TRANSPOSE

ID_SPORS|ID_PERS|S_SPOR-------：|------：|-----：1|10|01|11|02|10|502|11|03|10|03|11|0

insert into SL_RAWS (ID_PERS, ID_SPORS, S_SPOR)
select ID_PERS, ID_SPORS, S_SPOR from SL_TRANSPOSE

违反FOREIGN KEY约束"；IMPOSSIBLE_OVER_SL_BROKEN"；在桌子上"；SL_ RAWS"；外键引用目标不存在问题键值为("ID_PERS"=10，"ID_SPORS"=1(

insert into SL_SPORS 
select 10, 1 from rdb$database union all
select 11, 3 from rdb$database union all
select 11, 1 from rdb$database 

3行影响

insert into SL_RAWS (ID_PERS, ID_SPORS, S_SPOR)
select ID_PERS, ID_SPORS, S_SPOR from SL_TRANSPOSE

6行影响

select * from SL_RAWS

ID_PERS|ID_SPORS|S_SPOR------：|-------：|-----：10|1|011|1|010|2|5011|2|010|3|011|3|0

<blockquote\

select * from SL_RAWS where S_SPOR = 0  -- where S_SPOR IS NULL

>

ID_PERS|ID_SPORS|S_SPOR------：|-------：|-----：10|1|011|1|011|2|010|3|011|3|0

select * from SL_TRANSPOSE where S_SPOR = 0  -- where S_SPOR IS NULL

ID_SPORS|ID_PERS|S_SPOR-------：|------：|-----：1|10|01|11|02|11|03|10|03|11|0

db<gt；小提琴这里