商场促销问题

 最近，经过你的努力，你得到了一份工作，成为了百货公司的一位经理。 到位后，你发现你的销售数据库中有两张表，一个是商店促销时间的日历，另一个是在促销期间的销售额列表。你需要编写一个查询，告诉我们在每次促销中哪位职员的销售额最高，这样可以给那个职员发绩效奖金。

--商店促销时间的日历

 

create table promotions

 

(
promo_name varchar2(50) not null primary key,  -- 促销活动名称
start_date date not null,                     -- 开始时间
end_date date not null,                      -- 终止时间
check(start_date<=end_date)
)

 

---促销期间的销售额表 （注意：该表只是保存促销期间的销售额）
create table sales
(
ticket_nbr int not null primary key,  --销售票据编号 （自增）
clerk_name varchar2(20) not null,      --销售员姓名
sale_date date not null,                 --销售日期
sale_amount number(9,2) not null       --销售金额
)

 

请编制一条SQL来完成这个查询。(尽量考虑多种写法)

 

 

 

 

 

--oracle数据库环境下测试
---获得结果集：促销活动，促销员，销售额 

--此解法实际上是错误的，题目要求解的是每个活动的销售总额！

-----------------------------------------------------------------
SELECT promo_name,clerk_name,sale_amount
FROM sales,promotions
WHERE sale_amount = ANY
( SELECT MAX(sale_amount)
  FROM promotions,sales
  WHERE sale_date BETWEEN start_date AND end_date
  GROUP BY promo_name
)
AND sale_date BETWEEN start_date AND end_date;

 

 

--正确解法1：

SELECTs1.clerk_name,p.promo_name,p.start_date,p.end_date,SUM(s1.sale_amount)
FROMsales s1
INNER JOIN promotions p
ON s1.sale_date BETWEEN p.start_date AND p.end_date
group by s1.clerk_name,p.promo_name,p.start_date,p.end_date
HAVING SUM(s1.sale_amount)>=
ALL (SELECT SUM(s2.sale_amount)
FROM sales s2
WHERE s2.sale_date BETWEEN p.start_date AND p.end_date
GROUP BY s2.clerk_name)

 

Northwind 商贸公司，业务日益发展，公司OA 系统正不断推出新版本以紧跟公司的发展.
在OA 系统中，有一员工角色表，情况如下：
create table roles(
emp_name varchar2(20) not null,
emp_role char(1) not null,
constraint pk_roles primary key(emp_name,emp_role)
);
数据：
EMP_NAME EMP_ROLE
-------------------- --------
陈城 W
刘海 D
刘海 O
田亮 O
王晓刚 D
张玲 S
张天明 D
张天明 O
其中： W – 搬运工人 D – 主任 O – 高级职员 S – 秘书
OA 开发组的SQL 程序员张明得到了上级的一个任务：
领导要求得到的高级职员信息表如下：
EMP_NAME COMBINE_ROLE
-------------------- ------------
刘海 B
田亮 O
王晓刚 D
张天明 B
要求：
1）只列出主任和高级职员的信息
2）如果即是高级职员又是主任，用B 表示其角色, 其它信息不用再显示 （只一条记录）。
你能不能用单条SQL 语句帮助张明实现这个查询？

 

正确的解答：

 

SELECT person,
CASE WHEN COUNT(*) = 1
THEN role
ELSE 'B' END
FROM Roles
WHERE role IN ('D','O')
GROUP BY person;

 

 

SELECT emp_name, SUBSTR('ODB', SUM(instr('OD',emp_role,1)) combined_role

FORM roles

WHERE EMP_ROLE IN ('O','D')

GROUP BY emp_name