访问查询：太复杂

Question

我想运行查询作为报表的一部分，以确定是否需要发生事件。有很多组合，因为随后的事件取决于第一和第二事件中的观察结果，这些组合因鱼类大小而异，并按种类分组。希望能够提供一些背景。我已经在这里搜索了这些问题，但还没有找到任何解决方案。这是代码片段。代码大约160行左右，主要包含在Switch功能中。谢谢你的任何想法！

SELECT headerid, species, ageclass, P1, P2, P3, P4, P5, 
switch(ageclass in ('Subyearling', 'Yearling') and P3 is null and P1<4 and P2<1,  
'SHOCK', 
ageclass in ('Subyearling', 'Yearling') and P3 is null and P1 between 4 and 5 and 
P2<2, 'SHOCK', 
ageclass in ('Subyearling', 'Yearling') and P3 is null and P1 between 6 and 7 and 
P2<3, 'SHOCK', 
ageclass in ('Subyearling', 'Yearling') and P3 is null and P1 between 8 and 9 and 
P2<4, 'SHOCK', 

    ageclass like 'Subadult' and P3 is null and P1<5 and P2<1, 'SHOCK', 
    ageclass like 'Subadult' and P3 is null and P1 between 5 and 8 and P2<2, 'SHOCK', 
    ageclass like 'Subadult' and P3 is null and P1 between 9 and 11 and P2<3, 'SHOCK', 
    ageclass like 'Subadult' and P3 is null and P1 between 12 and 13 and P2<4, 'SHOCK', 
    ageclass like 'Subadult' and P3 is null and P1 between 14 and 16 and P2<5, 'SHOCK' 

    as Event from data; 

Answer 1

对于“和”，您可以分阶段进行这些查询。按第一个属性过滤，并在第二个属性上过滤结果。

对于“或”，您可以单独执行这些查询并加入结果。

编程逻辑是将问题分解成更小的问题的艺术。所以你需要将你的查询分解成更小的步骤并按顺序应用它们。它很可能具有很高的复杂性，根据前面的信息，像这些分支这样的查询会有所不同，并且您应该在算法逻辑中进行分支，而不是在select语句中进行分支。