SQL语句练习实例之四 找出促销活动中销售额最高的职员

代码如下:

---找出促销活动中销售额最高的职员
---你刚在一家服装销售公司中找到了一份工作，此时经理要求你根据数据库中的两张表得到促销活动销售额最高的销售员
---1.一张是促销活动表
---2.一张是销售客列表
create table Promotions
(
activity nvarchar(30),
sdate datetime,
edate datetime
)
insert Promotions
select '五一促销活动','2011-5-1','2011-5-7'
union
select '十一促销活动','2011-10-1','2011-10-7'
union
select 'OA专场活动','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,'王五','2011-5-1',1000 union
select 1,'王五','2011-5-2',2000 union
select 1,'王五','2011-5-3',3000 union
select 1,'王五','2011-5-4',4000 union
select 1,'张三','2011-5-1',1000 union
select 1,'张三','2011-5-3',2000 union
select 1,'张三','2011-5-4',4000 union
select 1,'李四','2011-5-6',1000 union
select 1,'赵六','2011-5-5',1000 union
select 1,'钱七','2011-5-8',1000 union

select 1,'孙五','2011-6-1',1000 union
select 1,'孙五','2011-6-2',2000 union
select 1,'王五','2011-6-3',3000 union
select 1,'孙五','2011-6-4',4000 union
select 1,'张三','2011-6-1',11000 union
select 1,'张三','2011-6-3',20000 union
select 1,'张三','2011-6-4',4000 union
select 1,'李四','2011-6-6',1000 union
select 1,'赵六','2011-6-5',1000 union
select 1,'钱七','2011-6-8',1500 union

select 1,'孙五','2011-10-1',11000 union
select 1,'孙五','2011-10-2',12000 union
select 1,'王五','2011-10-3',9000 union
select 1,'孙五','2011-10-4',4000 union
select 1,'张三','2011-10-1',11000 union
select 1,'张三','2011-10-3',2000 union
select 1,'张三','2011-10-4',4000 union
select 1,'李四','2011-10-6',27000 union
select 1,'赵六','2011-10-5',9000 union
select 1,'钱七','2011-10-8',3000
go
-----我们需要找出在每次的促销活动中，其销售总额大于 等于
---所有其他职员销售额的职员及促销事件。
---说明：谓词a2.name<>a.name将其他职员从子查询合计中排除出去
---------谓词Between 中的子查询确保我们使用了正确的促销日期

--方法一：
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
having 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)
-----------------

---方法二：
---说明： 如果促销活动时间是不重叠的，则promotions表中只有一个主键列，这样在group by
--子句中使用(activity,sdate,edate)将不会改变。但是它将使having子句可以使用sdate和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
having 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

--方法三：
---使用cte(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

赞 (0)

相关推荐

  • SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析

    复制代码 代码如下: ---在仓储管理中经常会碰到的一个问题 一.关于LIFO与FIFO的简单说明 ---FIFO: First in, First out.先进先出. ---LIFO: Last in, First out.后进先出. --如货物A:本月1日购买10件,单价10元/件,3日购买20件,单价15元/件:10日购买10件,单价8元/件. --本月15日发货35件. --按FIFO先进先出,就是先购入的存货先发出,所以,先发1日进货的10件,再发3日进货的20件,最后发10日进货的5

  • SQL语句练习实例之六 人事系统中的缺勤（休假）统计

    复制代码 代码如下: ---这是一个人事系统中的示例,要求记录一下员工的缺勤情况 ---1.要在表中记录一下缺勤计分,是对经常缺勤者的一种处罚性计分 ---规则: ---1.如果员工在一年内的缺勤计分达到50,就会可以解雇该员工. ---2.如果员工缺勤连续超过一天,就视为长病假,这时,第二天,第三天及以后的天数都不会统计该员工的缺勤计分 ----这些天也不算为缺勤. create table absence ( empId int not null, absenceDate datetime

  • SQL语句练习实例之七 剔除不需要的记录行

    复制代码 代码如下: --相信大家肯定经常会把数据导入到数据库中,但是可能会有些记录行的所有列的数据是null,这为null的数据是我们不需要 --现在需要一个简单的查询来剔除掉这些为null的记录行. --假设表名为 emplyees --方法1. ---先把数据导入到数据库 ---其次: select * from sys.columns where object_id =(select object_id from sys.objects where name='EMPLYEEs') --

  • SQL语句练习实例之二——找出销售冠军

    复制代码 代码如下: --销售冠军 --问题:在公司中,老板走进来,要一张每个地区销量前3名的销售额与销售员的报表 --- create table salesdetail ( Area int not null, Saler nvarchar(20) not null, SalerId int not null, Sales money not null ) insert salesdetail select 1,'张三',15,3000 union select 1,'赵一',16,3500

  • SQL语句练习实例之三——平均销售等待时间

    复制代码 代码如下: ---1.平均销售等待时间 ---有一张Sales表,其中有销售日期与顾客两列,现在要求使用一条SQL语句实现计算 --每个顾客的两次购买之间的平均天数 --假设:在同一个人在一天中不会购买两次 create table sales ( custname varchar(10) not null, saledate datetime not null ) go insert sales select '张三','2010-1-1' union select '张三','20

  • SQL语句练习实例之一——找出最近的两次晋升日期与工资额

    复制代码 代码如下: --程序员们在编写一个雇员报表,他们需要得到每个雇员当前及历史工资状态的信息, --以便生成报表.报表需要显示每个人的晋升日期和工资数目. --如果将每条工资信息都放在结果集的一行中,并让宿主程序去格式化它. --应用程序的程序员都是一帮懒人,他们需要在每个雇员的一行上得到当前 --和历史工资信息.这样就可以写一个非常简单的循环语句. ---示例: create table salaries ( name nvarchar(50) not null, sal_date da

  • SQL语句练习实例之四 找出促销活动中销售额最高的职员

    复制代码 代码如下: ---找出促销活动中销售额最高的职员 ---你刚在一家服装销售公司中找到了一份工作,此时经理要求你根据数据库中的两张表得到促销活动销售额最高的销售员 ---1.一张是促销活动表 ---2.一张是销售客列表 create table Promotions ( activity nvarchar(30), sdate datetime, edate datetime ) insert Promotions select '五一促销活动','2011-5-1','2011-5-7

  • Mybatis基于注解形式的sql语句生成实例代码

    对其做了些优化,但此种sql生成方式仅适用于复杂程度不高的sql,所以实用性不是很高,仅仅是写着玩的,知道点mybatis的注解形式的使用方式,可能以后会逐渐完善起来.第一次写博客,写的简单点. package com.bob.config.mvc.mybatis; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retenti

  • python找出一个列表中相同元素的多个索引实例

    定义:X=[1,2,3,1,4] 任务:找出元素为1的索引 Solution: # 如果直接用X.index(1),只能得到0这一个索引,而我们需要所有索引. l = len(X) zip_list = zip(*(range(l),X)) id1 = [z[0] for i,z in enumerate(zip_list) if z[1]==1] # 或者更简单的 id1 = [i for i,x in enumerate(X) if x==1] 以上这篇python找出一个列表中相同元素的多

  • PHP实现找出有序数组中绝对值最小的数算法分析

    本文实例讲述了PHP实现找出有序数组中绝对值最小的数算法.分享给大家供大家参考,具体如下: 问题: 一个有序数组,值有可能有负值,也有可能没有,现需要找出其中绝对值最小的值. 方法1: 遍历数组,找到绝对值最小值,时间复杂度O(n),n为元素个数. 方法2: 二分查找,因为数组有序,可以利用二分查找,时间复杂度O(logn). 分析步骤: 1. 如果第一个数为正数,说明整个数组没有负数,直接返回第一个数 2. 如果最后一个数为负数,说明整个数组没有正数,直接返回最后一个数 3. 数组元素有正有负

  • js找出5个数中最大的一个数和倒数第二大的数实现方法示例小结

    本文实例讲述了js找出5个数中最大的一个数和倒数第二大的数实现方法.分享给大家供大家参考,具体如下: 最大的一个数 : 方法1: <script> var max=Math.max(1,2,3,4,5,2,8); console.log(max) //5 //如果这5个数是数组 可以用apply方法 var max1=Math.max.apply(this,[1,2,3,4,5,2,8]); console.log(max1); </script> 方法2: <script&

  • SQL语句过滤条件放在on与where子句中的区别和联系浅析

    目录 摘要: 综述 过滤条件放在on和where中的区别 小结 Reference 摘要: 介绍在多表关联SQL语句中,过滤条件放在on和where子句中的区别——inner join中没区别,外连接就不一样. 综述 蚂蚁金服的一道SQL面试题如下:SQL语句中,过滤条件放在on和where子句中的区别.当时满脑子是left join和inner join等,觉得没区别啊!细思极恐,故梳理一下. 过滤条件放在on和where中的区别 数据库在通过连接两张或多张表来查询记录时,都会先通过join

  • IDEA 使用 SpotBugs 找出你代码中的bug问题

    IDEA 使用 SpotBugs 找出你代码中的bug问题

    SpotBugs 是 Findbugs 的继任者,通过静态分析来查找 Java 代码中的 bug. 下面我们主要是介绍 SpotBugs 在 idea 中的安装和使用 安装 Idea -  Preferences - Plugins,在插件市场搜索 spotbugs 点击安装 安装完成之后需要重启 Idea 以使其生效. 最后一步就是激活 最后一步是通过检查 Find Security Bugs 的条目来激活安全规则(已经内置好的). 推荐配置 对于大多数用户来说,建议的最低置信度为中等.对于寻

  • PHP实现的构造sql语句类实例

    本文实例讲述了PHP实现的构造sql语句类.分享给大家供大家参考,具体如下: /** * @package Database Class * @author injection (mail:injection.mail@gmail.com) * @version 1.0 */ @ini_set( 'display_errors',0 ); class DataBase{ private $mDb_host,$mAb_user,$mAb_pwd,$mConn_No; function DataBa

随机推荐