使用 Access 查询设计器轻松构造复杂 SQL 语句 (2)

作者: Stone0823 | 来源:发表于2018-07-14 20:17 被阅读64次

上一篇的 sql 语句蛮简单的。本篇设计一个复杂的场景。假设我们要编写一个计算期间物料进出存的查询。目标是在 10 分钟之内完成。需要用到 5 个表，将创建表的 sql 语句贴在下面：

物料主数据表 (material_numbers)：

create table material_numbers (
    [MaterialNo] varchar(20) primary key,
    [Description_e] varchar(100) null,
    [Description_c] varchar(100) null
);

仓位（storage_locations）

create table storage_locations(
    [StorageLocation] varchar(10) PRIMARY KEY,
    [LocationType] varchar(10) NULL,
    [Description] varchar(50) NULL
);

movement_types:

CREATE TABLE movement_types(
    [MovementTypeID] varchar(10) PRIMARY KEY,
    [Description] varchar(50) NULL,
    [InOutSign] varchar(1) NULL
);

stock_movement_header:

CREATE TABLE stock_movement_headers(
    [DocNo] varchar(10) PRIMARY KEY,
    [MovementType] varchar(10) NULL,
    [RefDocNo] varchar(10) NULL,
    [VendorID] varchar(10) NULL,
    [CustomerID] varchar(10) NULL,
    [DocDate] datetime NULL,
    [PeriodID] varchar(10) NULL,
    [GCInvoiceNo] varchar(15) NULL,
    [LongText] text NULL,
    [PostedBy] varchar(10) NULL,
    [PostedDate] datetime NULL,
    [Remarks] varchar(255) NULL
);

stock_movement_detail:

CREATE TABLE stock_movement_details(
    [DocNo] varchar(10),
    [MaterialNo] varchar(20),
    [StorageLocation] varchar(10) NULL,
    [Qty] float NULL,
    [Remarks] varchar(255) NULL,
    PRIMARY KEY(DocNo, MaterialNo)
);

第一步：构建物料清单

将查询保存为 materials。对应的 sql 语句如下。因为 Access 本身不支持代码格式化和高亮，代码借助 Visual Studio Code 进行格式化。

SELECT
    material_numbers.MaterialNo,
    storage_locations.StorageLocation
FROM
    material_numbers,
    storage_locations
WHERE
    (
        ((material_numbers.MaterialNo) Like 'B180*')
        AND ((storage_locations.StorageLocation) = '1001')
    );

第二步: 计算期初库存

需要先构建物料出入库的清单。首先基于 stock_movement_header 和 stock_movement_detail 两个表创建查询，抽取需要的字段：

将查询保存为 txn_prior_periods，然后将 movement_types 表加入查询，根据 InOutSign 字段来确定是出库还是入库：

增加两个计算列，提取 DocDate 的年和月，用于筛选：

加上筛选条件，只取 2008 年 4 月之前的记录。可视化的条件表达灵活性稍差。注意同一行之间的各列是 and 关系，不同行之间的条件是 or 关系：

可以去掉 TxYear 和 TxMonth 两列的显示，此时对应的 sql 语句如下：

SELECT
    stock_movement_headers.MovementType,
    movement_types.InOutSign,
    stock_movement_headers.DocDate,
    stock_movement_details.MaterialNo,
    stock_movement_details.StorageLocation,
    stock_movement_details.Qty,
    IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty
FROM
    (
        stock_movement_headers
        INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
    )
    LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
WHERE
    (
        ((stock_movement_details.MaterialNo) Like 'B180*')
        AND ((stock_movement_details.StorageLocation) = '1001')
        AND ((Year([DocDate])) = 2008)
        AND ((Month([DocDate])) < 4)
    )
    OR (
        ((stock_movement_details.MaterialNo) Like 'B180*')
        AND ((stock_movement_details.StorageLocation) = '1001')
        AND ((Year([DocDate])) < 2008)
    );

注意到可视化查询设计对条件的表达比较啰嗦，在 SQL 视图下进行简化如下：

SELECT
    stock_movement_headers.MovementType,
    movement_types.InOutSign,
    stock_movement_headers.DocDate,
    stock_movement_details.MaterialNo,
    stock_movement_details.StorageLocation,
    stock_movement_details.Qty,
    IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty
FROM
    (
        stock_movement_headers
        INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
    )
    LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
WHERE
    (
        stock_movement_details.MaterialNo Like 'B180*'
        AND stock_movement_details.StorageLocation = '1001'
    )
    AND (
        (Year([DocDate]) < 2008)
        OR (
            Year([DocDate]) = 2008
            AND Month([DocDate]) < 4
        )
    );

基于查询 txn_prior_periods 计算前期物料移动的合计数，作为期初库存。

此时对应的 sql 语句如下：

SELECT
    MaterialNo,
    StorageLocation,
    Sum(ActualQty) AS BeginQty
FROM
    txn_prior_periods
GROUP BY
    MaterialNo,
    StorageLocation;

txn_prior_periods 是上一步定义的查询，将 txn_prior_periods 替换为子查询：

SELECT
    txn_prior_periods.MaterialNo,
    txn_prior_periods.StorageLocation,
    Sum(txn_prior_periods.ActualQty) AS BeginQty
FROM
    (
        SELECT
            stock_movement_headers.MovementType,
            movement_types.InOutSign,
            stock_movement_headers.DocDate,
            stock_movement_details.MaterialNo,
            stock_movement_details.StorageLocation,
            stock_movement_details.Qty,
            IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty
        FROM
            (
                stock_movement_headers
                INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
            )
            LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
        WHERE
            (
                stock_movement_details.MaterialNo Like 'B180*'
                AND stock_movement_details.StorageLocation = '1001'
            )
            AND (
                (Year([DocDate]) < 2008)
                OR (
                    Year([DocDate]) = 2008
                    AND Month([DocDate]) < 4
                )
            )
    ) AS txn_prior_periods
GROUP BY
    txn_prior_periods.MaterialNo,
    txn_prior_periods.StorageLocation;

第三步：计算当期出入库数量

接下来用类似的方法计算 2008 年 4 月物料的合计数。拷贝查询 txn_prior_periods，另存一个查询，名为 txn_curr_period。在 Design 视图如下修改筛选的时间为 2008 年 4 月。

在 SQL 视图下对应的 sql 语句如下：

SELECT
    stock_movement_headers.MovementType,
    movement_types.InOutSign,
    stock_movement_headers.DocDate,
    stock_movement_details.MaterialNo,
    stock_movement_details.StorageLocation,
    stock_movement_details.Qty,
    IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty,
    Year([DocDate]) AS TxYear,
    Month([DocDate]) AS TxMonth
FROM
    (
        stock_movement_headers
        LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
    )
    INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
WHERE
    (
        ((stock_movement_details.MaterialNo) Like 'B180*')
        AND ((stock_movement_details.StorageLocation) = '1001')
        AND ((Year([DocDate])) = 2008)
        AND ((Month([DocDate])) = 4)
    );

基于查询 txn_curr_period 创建当期物料出入库合计，如果 InOutSign 为 +，表示入库，InOutSign 为 - 表示出库。将查询保存为 period_qty:

在 SQL 视图下查看 sql 语句如下：

SELECT
    txn_curr_period.MaterialNo,
    txn_curr_period.StorageLocation,
    Sum(IIf([InOutSign] = '+', [ActualQty], 0)) AS StockIn,
    Sum(IIf([InOutSign] = '-', [ActualQty], 0)) AS StockOut
FROM
    txn_curr_period
GROUP BY
    txn_curr_period.MaterialNo,
    txn_curr_period.StorageLocation;

将 txn_curr_period 替换为子查询：

SELECT
    txn_curr_period.MaterialNo,
    txn_curr_period.StorageLocation,
    Sum(IIf([InOutSign] = '+', [ActualQty], 0)) AS StockIn,
    Sum(IIf([InOutSign] = '-', [ActualQty], 0)) AS StockOut
FROM
    (
        SELECT
            stock_movement_headers.MovementType,
            movement_types.InOutSign,
            stock_movement_headers.DocDate,
            stock_movement_details.MaterialNo,
            stock_movement_details.StorageLocation,
            stock_movement_details.Qty,
            IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty,
            Year([DocDate]) AS TxYear,
            Month([DocDate]) AS TxMonth
        FROM
            (
                stock_movement_headers
                LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
            )
            INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
        WHERE
            (
                ((stock_movement_details.MaterialNo) Like 'B180*')
                AND ((stock_movement_details.StorageLocation) = '1001')
                AND ((Year([DocDate])) = 2008)
                AND ((Month([DocDate])) = 4)
            )
    ) AS txn_curr_period
GROUP BY
    txn_curr_period.MaterialNo,
    txn_curr_period.StorageLocation;

第四步：构建进出存查询

然后基于查询 materials, begin_qty 和 period_qty 创建一个进出存查询，命名为 stock_balances：

增加计算列，计算期末库存：

在 SQL 视图下查看 sql 语句：

SELECT
    materials.MaterialNo,
    materials.StorageLocation,
    begin_qty.BeginQty,
    period_qty.StockIn,
    period_qty.StockOut,
    Nz([BeginQty]) + Nz([StockIn]) + Nz([StockOut]) AS EndQty
FROM
    (
        materials
        LEFT JOIN begin_qty ON (
            materials.StorageLocation = begin_qty.StorageLocation
        )
        AND (materials.MaterialNo = begin_qty.MaterialNo)
    )
    LEFT JOIN period_qty ON (
        materials.StorageLocation = period_qty.StorageLocation
    )
    AND (materials.MaterialNo = period_qty.MaterialNo);

将 sql 语句中 materials, begin_qty 和 period_qty 替换为子查询，即这几个查询对应的 sql 语句：

SELECT
    materials.MaterialNo,
    materials.StorageLocation,
    begin_qty.BeginQty,
    period_qty.StockIn,
    period_qty.StockOut,
    Nz([BeginQty]) + Nz([StockIn]) + Nz([StockOut]) AS EndQty
FROM
    (
        (
            SELECT
                material_numbers.MaterialNo,
                storage_locations.StorageLocation
            FROM
                material_numbers,
                storage_locations
            WHERE
                (
                    ((material_numbers.MaterialNo) Like 'B180*')
                    AND ((storage_locations.StorageLocation) = '1001')
                )
        ) AS materials
        LEFT JOIN (
            SELECT
                txn_prior_periods.MaterialNo,
                txn_prior_periods.StorageLocation,
                Sum(txn_prior_periods.ActualQty) AS BeginQty
            FROM
                (
                    SELECT
                        stock_movement_headers.MovementType,
                        movement_types.InOutSign,
                        stock_movement_headers.DocDate,
                        stock_movement_details.MaterialNo,
                        stock_movement_details.StorageLocation,
                        stock_movement_details.Qty,
                        IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty
                    FROM
                        (
                            stock_movement_headers
                            INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
                        )
                        LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
                    WHERE
                        (
                            stock_movement_details.MaterialNo Like 'B180*'
                            AND stock_movement_details.StorageLocation = '1001'
                        )
                        AND (
                            (Year([DocDate]) < 2008)
                            OR (
                                Year([DocDate]) = 2008
                                AND Month([DocDate]) < 4
                            )
                        )
                ) AS txn_prior_periods
            GROUP BY
                txn_prior_periods.MaterialNo,
                txn_prior_periods.StorageLocation
        ) AS begin_qty ON (
            materials.StorageLocation = begin_qty.StorageLocation
        )
        AND (materials.MaterialNo = begin_qty.MaterialNo)
    )
    LEFT JOIN (
        SELECT
            txn_curr_period.MaterialNo,
            txn_curr_period.StorageLocation,
            Sum(IIf([InOutSign] = '+', [ActualQty], 0)) AS StockIn,
            Sum(IIf([InOutSign] = '-', [ActualQty], 0)) AS StockOut
        FROM
            (
                SELECT
                    stock_movement_headers.MovementType,
                    movement_types.InOutSign,
                    stock_movement_headers.DocDate,
                    stock_movement_details.MaterialNo,
                    stock_movement_details.StorageLocation,
                    stock_movement_details.Qty,
                    IIf([InOutSign] = '+', [Qty], -1 * [Qty]) AS ActualQty,
                    Year([DocDate]) AS TxYear,
                    Month([DocDate]) AS TxMonth
                FROM
                    (
                        stock_movement_headers
                        LEFT JOIN movement_types ON stock_movement_headers.MovementType = movement_types.MovementTypeID
                    )
                    INNER JOIN stock_movement_details ON stock_movement_headers.DocNo = stock_movement_details.DocNo
                WHERE
                    (
                        ((stock_movement_details.MaterialNo) Like 'B180*')
                        AND (
                            (stock_movement_details.StorageLocation) = '1001'
                        )
                        AND ((Year([DocDate])) = 2008)
                        AND ((Month([DocDate])) = 4)
                    )
            ) AS txn_curr_period
        GROUP BY
            txn_curr_period.MaterialNo,
            txn_curr_period.StorageLocation
    ) AS period_qty ON (
        materials.StorageLocation = period_qty.StorageLocation
    )
    AND (materials.MaterialNo = period_qty.MaterialNo);

大功告成。

使用 Access 查询设计器轻松构造复杂 SQL 语句 (2)

第一步：构建物料清单

第二步: 计算期初库存

第三步：计算当期出入库数量

第四步：构建进出存查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读