Copy Code code as follows:
---Find the highest-selling staff in a promotional campaign
---you've just got a job at a clothing sales company, the manager asks you to get the sales salesperson with the highest sales promotion activity based on two tables in the database.
---1. A list of promotional activities
---2. One is a list of sales guests
CREATE TABLE Promotions
(
Activity nvarchar (30),
Sdate datetime,
Edate datetime
)
Insert Promotions
Select ' 51 promotional activities ', ' 2011-5-1 ', ' 2011-5-7 '
Union
Select ' 11 promotional activities ', ' 2011-10-1 ', ' 2011-10-7 '
Union
Select ' OA special event ', ' 2011-6-1 ', ' 2011-6-7 '
Go
CREATE TABLE Sales
(
ID int NOT NULL,
Name nvarchar (20),
Saledate datetime,
Price money
)
Go
Insert Sales
Select 1, ' Harry ', ' 2011-5-1 ', 1000 union
Select 1, ' Harry ', ' 2011-5-2 ', union
Select 1, ' Harry ', ' 2011-5-3 ', 3000 union
Select 1, ' Harry ', ' 2011-5-4 ', 4000 union
Select 1, ' John ', ' 2011-5-1 ', 1000 union
Select 1, ' John ', ' 2011-5-3 ', union
Select 1, ' John ', ' 2011-5-4 ', 4000 union
Select 1, ' Dick ', ' 2011-5-6 ', 1000 union
Select 1, ' Zhao Liu ', ' 2011-5-5 ', 1000 union
Select 1, ' Money Seven ', ' 2011-5-8 ', 1000 union
Select 1, ' Sun Wu ', ' 2011-6-1 ', 1000 union
Select 1, ' Sun Wu ', ' 2011-6-2 ', union
Select 1, ' Harry ', ' 2011-6-3 ', 3000 union
Select 1, ' Sun Wu ', ' 2011-6-4 ', 4000 union
Select 1, ' John ', ' 2011-6-1 ', 11000 union
Select 1, ' John ', ' 2011-6-3 ', 20000 union
Select 1, ' John ', ' 2011-6-4 ', 4000 union
Select 1, ' Dick ', ' 2011-6-6 ', 1000 union
Select 1, ' Zhao Liu ', ' 2011-6-5 ', 1000 union
Select 1, ' Money Seven ', ' 2011-6-8 ', 1500 union
Select 1, ' Sun Wu ', ' 2011-10-1 ', 11000 union
Select 1, ' Sun Wu ', ' 2011-10-2 ', 12000 Union
Select 1, ' Harry ', ' 2011-10-3 ', 9000 union
Select 1, ' Sun Wu ', ' 2011-10-4 ', 4000 union
Select 1, ' John ', ' 2011-10-1 ', 11000 union
Select 1, ' John ', ' 2011-10-3 ', union
Select 1, ' John ', ' 2011-10-4 ', 4000 union
Select 1, ' Dick ', ' 2011-10-6 ', 27000 union
Select 1, ' Zhao Liu ', ' 2011-10-5 ', 9000 union
Select 1, ' Money Seven ', ' 2011-10-8 ', 3000
Go
-----We need to find out that the total amount of sales in each promotional event is greater than or equal to
---staff and promotional events for all other staff sales.
---Description: predicate a2.name<>a.name excludes other staff from the subquery totals
Subqueries in the---------predicate between ensure that we use the correct promotional date
--Method One:
Select A.name,b.activity,sum (a.price) as Totalprice
From sales A, promotions as B
where a.saledate between B.sdate and B.edate
GROUP BY A.name,b.activity
have sum (price) >= all (select SUM (Price) from sales A2
where A2.name<>a.name and a2.saledate between
(
Select Sdate from promotions as B2 where b2.activity=b.activity
)
and (select Edate from Promotions B3
where b3.activity=b.activity)
Group BY A2.name)
-----------------
---method two:
---Note: If promotional activity time is not overlapping, then there is only one primary key column in the promotions table, so that the group by
--The use of (activity,sdate,edate) in clauses will not change. However, it will enable the HAVING clause to use sdate and edate
Select A.name,b.activity,sum (a.price) as Totalprice
From sales A, promotions as B
where a.saledate between B.sdate and B.edate
GROUP BY B.activity,b.sdate,b.edate,a.name
have sum (price) >= all (select SUM (Price) from sales A2
where A2.name<>a.name and a2.saledate between
B.sdate
and B.edate
Group BY A2.name)
Go
--Method Three:
---using a CTE (later versions of SQL 2005)
With Clearkstotal (Name,activity,totalprice) as
(
Select A.name,b.activity,sum (Price)
From sales A, promotions b
where a.saledate between B.sdate and B.edate
GROUP BY A.name,b.activity
)
Select C1.name,c1.activity,c1.totalprice
From Clearkstotal C1
where totalprice= (select MAX (c2.totalprice) from Clearkstotal C2
where c1.activity=c2.activity)
Go
DROP TABLE Promotions
Go
DROP TABLE Sales
