数据库查询的执行顺序

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

该题目的数据
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

首先我们需要连接两个表

得到

1
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

选择我们需要的两列

2
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 子句

  欢迎讨论

 

转载于:https://www.cnblogs.com/jianjialin/archive/2009/03/04/1402707.html