通过题目的形式来分析,题目如下
该题目的数据
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
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 笔记本电脑 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
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
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
GROUP BY businessmanname
businessmanname 利润
-- ------------- -----------
太平洋电脑城 11000
颐高数码 8000
我是这么理解的
1. DBMS首先执行from子句,根据from子句中的一个或者多个表创建中间表。如上,有两个表,那么执行第一步。得到一个包含两张表数据的工作表。
2. 执行select子句,去掉工作表中不需要的列
3. 如果有where子句,那么用where子句来筛选过滤第一步得到的工作表,去掉不合规矩的行
4. 执行group by 子句,分组并聚合不在分组列的其他列
5. 执行having 子句,(having子句的列必须包含在分组或者聚合列中)
6. order by 子句
欢迎讨论