时间戳

首页 > 解决方案 > 需要帮助以简化查询

sql - 需要帮助以简化查询

问题描述

我有一个查询需要简化并且仍然给出相同的结果但速度更快，这是结果查询

SELECT Table2.PoNumber
     , Nz([MaterialCode],"None") AS MatCode
     , Nz([Material],"None") AS Mat
     , Nz([MaterialCons],"None") AS MatCons
     , Nz([MasterCode],"None") AS MastCode
     , Nz([Master],"None") AS Masterr
     , Nz([MasterCons],"None") AS MasterrCons
     , Nz([StretchCode],"None") AS StrCode
     , Nz([Stretch],"None") AS Str
     , Nz([StretchCons],"None") AS StrCons
     , Nz([PackCode],"None") AS PacCode
     , Nz([Pack],"None") AS Package
     , Nz([PackCons],"None") AS PacCons
     , Nz([MasterCons]/[MaterialCons],0) AS MasterPercent
FROM (((Table2 LEFT JOIN Sub1 ON Table2.PoNumber = Sub1.Po) 
LEFT JOIN Sub2 ON Table2.PoNumber = Sub2.Po) 
LEFT JOIN Sub3 ON Table2.PoNumber = Sub3.Po) 
LEFT JOIN Sub4 ON Table2.PoNumber = Sub4.Po;

此查询依赖于 4 个子查询：

子1

SELECT Table1.Code AS MaterialCode
     , Table1.Item AS Material
     , Table1.Cons AS MaterialCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="Material"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

子2

SELECT Table1.Code AS MasterCode
     , Table1.Item AS Master
     , Table1.Cons AS MasterCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="MasterPatch"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

子 3

SELECT Table1.Code AS StretchCode
     , Table1.Item AS Stretch
     , Table1.Cons AS StretchCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="Stretch"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

子 4

SELECT Table1.Code AS PackCode
     , Table1.Item AS Pack
     , Table1.Cons AS PackCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="Package"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

我需要取消这 4 个子查询并且仍然得到相同的结果。注意：主数据库变得丑陋，因为太多查询依赖于相同的加入方式。基本上取决于一两个表，但有多个标准。所以我需要提高效率的方法。

谢谢。

标签： sqlms-access

解决方案

我看不到按子查询分组的目的。我认为您的整个查询应如下所示：

SELECT Table2.PoNumber, ...
FROM Table2 LEFT JOIN Table1 ON Table2.PoNumber = Table1.Po
WHERE Table1.Type IN ("Material", "MasterPatch", "Stretch", "Package");

推荐阅读