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

上一篇的 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);

大功告成。

最后编辑于：2018.07.25 14:10:48

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,902评论 5赞 468
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 84,037评论 2赞 377
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,978评论 0赞 332
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,867评论 1赞 272
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,763评论 5赞 360
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,104评论 1赞 277
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,565评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,236评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,379评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,313评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,363评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,034评论 3赞 315
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,637评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,719评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,952评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,371评论 2赞 346
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,948评论 2赞 341

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

第一步：构建物料清单

第二步: 计算期初库存

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

第四步：构建进出存查询

推荐阅读更多精彩内容