数据库查询的执行顺序

通过题目的形式来分析，题目如下

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[temp_products](
    [productID] [int] NOT NULL,
    [productName] [nvarchar](50) NOT NULL,
    [cost] [int] NOT NULL,
    [price] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_Businessman]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[temp_Businessman](
    [businessmanID] [int] NULL,
    [businessmanName] [nvarchar](50) NULL,
    [SellProductID] [int] NULL,
    [SellProductCount] [int] NULL
) ON [PRIMARY]
END
INSERT INTO [dbo].[temp_Businessman]
           ([businessmanID]
           ,[businessmanName]
           ,[SellProductID]
           ,[SellProductCount])
     VALUES(1,'太平洋电脑城',1,5)
INSERT INTO [dbo].[temp_Businessman]
           ([businessmanID]
           ,[businessmanName]
           ,[SellProductID]
           ,[SellProductCount])
     VALUES(1,'太平洋电脑城',2,4)
INSERT INTO [dbo].[temp_Businessman]
           ([businessmanID]
           ,[businessmanName]
           ,[SellProductID]
           ,[SellProductCount])
     VALUES(2,'颐高数码',2,2)
INSERT INTO [dbo].[temp_Businessman]
           ([businessmanID]
           ,[businessmanName]
           ,[SellProductID]
           ,[SellProductCount])
     VALUES(2,'颐高数码',3,5)


INSERT INTO [dbo].[temp_products]
           ([productID]
           ,[productName]
           ,[cost]
           ,[price])
     VALUES(1,'笔记本电脑',3000,4000)
INSERT INTO [dbo].[temp_products]
           ([productID]
           ,[productName]
           ,[cost]
           ,[price])
     VALUES(2,'台式电脑',2000,3500)
INSERT INTO [dbo].[temp_products]
           ([productID]
           ,[productName]
           ,[cost]
           ,[price])
     VALUES(3,'数码相机',1500,2500)

select * from temp_products
select * from temp_businessman

 

productID   productName                                        cost        price
----------- -----------------                                 ----------- -----------
1           笔记本电脑                                              3000        4000
2           台式电脑                                               2000        3500
3           数码相机                                               1500        2500

(3 行受影响)

businessmanID businessmanName                                    SellProductID SellProductCount
------------- -------------------------------------------------- ------------- ----------------
1             太平洋电脑城                                             1             5
1             太平洋电脑城                                             2             4
2             颐高数码                                               2             2
2             颐高数码                                               3             5


求每个销售商和他的总利润
答案如下格式
businessmanName    利润(利润=（价格price - 成本cost） * 销售数量sellProductCount)
太平洋电脑城            XXXX
颐高数码                XXXX

businessmanname                                    利润
-------------------------------------------------- -----------
太平洋电脑城                                             11000
颐高数码                                               8000

首先我们需要连接两个表

得到

select * from temp_businessman a inner join temp_products b ON a.sellproductid=b.productid
businessmanID businessmanName SellProductID SellProductCount productID   productName cost        price
------------- --------------- ------------- ---------------- ----------- ----------- ----------- -----------
1             太平洋电脑城          1             5                1           笔记本电脑       3000        4000
1             太平洋电脑城          2             4                2           台式电脑        2000        3500
2             颐高数码            2             2                2           台式电脑        2000        3500
2             颐高数码            3             5                3           数码相机        1500        2500

选择我们需要的两列

select businessmanName, ((price-cost)*sellproductcount)利润 FROM temp_businessman a inner join temp_products b ON a.sellproductid=b.productid

businessmanName 利润
--------------- -----------
太平洋电脑城          5000
太平洋电脑城          6000
颐高数码            3000
颐高数码            5000

 答案已经显而易见了 只需要再聚合分组一下便OK

SELECT businessmanname,SUM((price-cost)*sellproductcount)利润 FROM temp_businessman a inner join temp_products b ON a.sellproductid=b.productid
GROUP BY businessmanname

businessmanname 利润
--------------- -----------
太平洋电脑城          11000
颐高数码            8000

 

我是这么理解的

1. DBMS首先执行from子句，根据from子句中的一个或者多个表创建中间表。如上，有两个表，那么执行第一步。得到一个包含两张表数据的工作表。

2. 执行select子句，去掉工作表中不需要的列

3.  如果有where子句，那么用where子句来筛选过滤第一步得到的工作表，去掉不合规矩的行

4. 执行group by 子句，分组并聚合不在分组列的其他列

5. 执行having 子句，（having子句的列必须包含在分组或者聚合列中）

6. order by 子句

  欢迎讨论