ACCESS 浅谈小白如何写出复杂的SQL语句

先声明一下,我本人的SQL的水平也是非常粗浅的,本文就实际开发过程中遇到的问题做一个简单的笔记,不对的地方,请大家指正.

经验一: 合理利SQL语句,不是什么语句都得在视图下完成的.你可以尝试在SQL视图下手打.

一个简单常见的例子: 求库存 .以我这里的数据库为例,有三张表关系到了库存的数量,分别是: 入库单,出库单和报废单 .计算公式是: 库存=入库数量 + 出库数量 *-1 + 报废数量*-1,另外还要求是按仓库和产品ID来统计的.

很多小白遇到这种问题,首先就是把表拖出来,然后就不知道怎么操作了,比如:

 这里利用了union all语句,把三个表里的数据提取出来:

　　 SELECT 入库单.产品ID, Sum(入库单.数量) AS 数量之合计, 入库单.仓库    FROM 入库单    GROUP BY 入库单.产品ID, 入库单.仓库
    UNION ALL
    SELECT 出库单.产品ID, Sum(出库单.数量)*-1 AS 数量之合计, 出库单.仓库    FROM 出库单    GROUP BY 出库单.产品ID, 出库单.仓库
    UNION ALL
    SELECT 报废记录.产品ID, Sum(报废记录.报废数量)*-1 AS 数量之合计, 报废记录.仓库    FROM 报废记录    GROUP BY 报废记录.产品ID, 报废记录.仓库

这种情况下,它是没有设计视图的:

 展现出来的数据如下(浮点计算本文暂不讲解):

 

经验二: 不要老想着直接用一条SQL打天下(也不是不可以,能力问题而已).如果一条SQL语句拿不到想要的数据,那就玩套娃,这是我最后的坚强..起码我能拿到我想要的数据

比如上面用uion all 揉出来的查询并没有完成我查库存的要求,只是粗糙的把数据组合到了一起,所以我们要对它再进行汇总操作.

　　方法1:在SQL视图下进行的,因为它没有设计视图:

SELECT 查询1.产品ID, Sum(查询1.数量之合计) AS 数量之合计之合计, 查询1.仓库
FROM
(
    SELECT 入库单.产品ID, Sum(入库单.数量) AS 数量之合计, 入库单.仓库    FROM 入库单    GROUP BY 入库单.产品ID, 入库单.仓库
    UNION ALL
    SELECT 出库单.产品ID, Sum(出库单.数量)*-1 AS 数量之合计, 出库单.仓库    FROM 出库单    GROUP BY 出库单.产品ID, 出库单.仓库
    UNION ALL
    SELECT 报废记录.产品ID, Sum(报废记录.报废数量)*-1 AS 数量之合计, 报废记录.仓库    FROM 报废记录    GROUP BY 报废记录.产品ID, 报废记录.仓库
) AS 查询1
GROUP BY 查询1.产品ID, 查询1.仓库;

方法2: (强烈安利给小白) 先把union all查询出来的数据保存为"查询1",然后再重新创建一个新的查询,引入"查询1" ,这样就可以在设计视图下操作了,比如:

 

 得到的数据和方法1中的完全相同:

我们来看一下方法2的SQL语句:　　

SELECT 查询1.产品ID, Sum(查询1.数量之合计) AS 数量之合计之合计, 查询1.仓库
FROM 查询1
GROUP BY 查询1.产品ID, 查询1.仓库;

可以看到,"From 查询1" , 现在,我们只需要把"查询1"里面的SQL复制到"查询1"前面就可以了 (注意要用 as 查询1)

SELECT 查询1.产品ID, Sum(查询1.数量之合计) AS 数量之合计之合计, 查询1.仓库
FROM
(
    SELECT 入库单.产品ID, Sum(入库单.数量) AS 数量之合计, 入库单.仓库    FROM 入库单    GROUP BY 入库单.产品ID, 入库单.仓库
    UNION ALL
    SELECT 出库单.产品ID, Sum(出库单.数量)*-1 AS 数量之合计, 出库单.仓库    FROM 出库单    GROUP BY 出库单.产品ID, 出库单.仓库
    UNION ALL
    SELECT 报废记录.产品ID, Sum(报废记录.报废数量)*-1 AS 数量之合计, 报废记录.仓库    FROM 报废记录    GROUP BY 报废记录.产品ID, 报废记录.仓库
) AS 查询1
GROUP BY 查询1.产品ID, 查询1.仓库;

然后我们再看看之前方法1里面的代码,一模一样!而且此时,我们之前保存的"查询1"就可以删除掉,实现了一条SQL解决问题.

最后我们还要插入产品ID对应的产品信息,这些信息都放在了物料信息表里了,所以到这里,我们又可以按照上面方法2的处理方式,把上面的查询结果保存为"查询2",然后按方法2再走一遍就可以了:

  要分好主次,我们的目标是查询所有物料的库存,所以当然要选择第二项:包含物料信息中的所有记录,这里要注意一下,如果查询2中没有对应的值,我们应该用nz函数,把0赋值给库存

 SQL语句如下:

SELECT 物料信息.ID, 物料信息.产品编码, 物料信息.产品名称, 物料信息.规格, Format(Nz([数量之合计之合计],0),"Fixed")*1 AS 参考库存
FROM 物料信息 LEFT JOIN 查询2 ON 物料信息.ID = 查询2.产品ID;

然后我们看 FROM 后面,有个"查询2",你想到了什么?!  对!把 "查询2"里面的语句代入进去就可以了!

SELECT 物料信息.ID,
       物料信息.产品编码,
       物料信息.产品名称,
       物料信息.规格,
       Format(Nz([数量之合计之合计], 0), "Fixed") * 1 AS 参考库存
FROM 物料信息
LEFT JOIN
    (SELECT 查询1.产品ID,
            Sum(查询1.数量之合计) AS 数量之合计之合计,
            查询1.仓库
    FROM
        (SELECT 入库单.产品ID,
                Sum(入库单.数量) AS 数量之合计,
                入库单.仓库
        FROM 入库单
        GROUP BY 入库单.产品ID, 入库单.仓库
        UNION ALL
        SELECT 出库单.产品ID,
                Sum(出库单.数量) * -1 AS 数量之合计,
                出库单.仓库
        FROM 出库单
        GROUP BY 出库单.产品ID, 出库单.仓库
        UNION ALL
        SELECT 报废记录.产品ID,
                Sum(报废记录.报废数量) * -1 AS 数量之合计,
                报废记录.仓库
        FROM 报废记录
        GROUP BY 报废记录.产品ID, 报废记录.仓库
        ) AS 查询1
    GROUP BY 查询1.产品ID, 查询1.仓库
    ) AS 查询2
ON 物料信息.ID = 查询2.产品ID;

 

　　这样一个套娃SQL就做出来了.小白如果不会曲线救国的话,想到死也写不出这段SQL来的.以上,多指教