" 疯狂Java联盟" 有一宗旨是“分享”，那我也与大家分享一点我曾经的面试题吧！我是用mysql做的，也欢迎大家用其他数据库，如oracle等。呵呵....为了抛砖引 玉，献丑一回了.... 1至5题我只是实现并未考虑效率问题，第6题未做.......也请大家补充及斧正
---------------------------------------------

销售流水表(sale_records)
Trace_id? ?? ?number(10)? ?交易编号(惟一)
Trace_time? ? date? ?? ?? ?交易时间
Sales_id? ?? ?number(10)? ?销售人员编号
Branch_id? ???number(10)? ?分店编号
Trace_type? ? number(4)? ? 交易类型(1现金，2信用卡，3优惠券)
Total_amount??number(8)? ? 交易金额

每天300 ~ 400 万记录,所有字段不为空

1.提取2007-8-8交易次数和交易总金额

??交易数??交易总金额


select count(Trace_id) as 交易次数,sum(Total_amount) as 交易总金额
from sale_records
where Trace_time='2007-8-8';
如：
+----------+------------+
| 交易次数 | 交易总金额 |
+----------+------------+
|? ?? ? 14 |? ?? ?32200 |
+----------+------------+

2.提取2007-8-8，3种付款方式对应的交易次数和交易金额

??现金交易次数 现金交易金额??信用卡交易次数??信用卡交易金额

方法1
select count(Trace_id) as 现金交易次数,sum(Total_amount) as 现金交易金额
from sale_records
where Trace_type=1
union all
select count(Trace_id) as 信用卡交易次数,sum(Total_amount) as 信用卡交易金额
from sale_records
where Trace_type=2;
如：
+--------------+--------------+
| 现金交易次数 | 现金交易金额 |
+--------------+--------------+
|? ?? ?? ???10 |? ?? ???28000 |
|? ?? ?? ?? ?3 |? ?? ?? ?3200 |
+--------------+--------------+
方法2
select 现金交易次数, 现金交易金额,信用卡交易次数,信用卡交易金额
from
(
select Sales_id, count(Trace_id) as 现金交易次数,sum(Total_amount) as 现金交易金额
from sale_records
where Trace_type=1
)type1,
(
select Sales_id, count(Trace_id) as 信用卡交易次数,sum(Total_amount) as 信用卡交易金额
from sale_records
where Trace_type=2
)type2
where type1.Sales_id=type2.Sales_id;
如：
+--------------+--------------+----------------+----------------+
| 现金交易次数 | 现金交易金额 | 信用卡交易次数 | 信用卡交易金额 |
+--------------+--------------+----------------+----------------+
|? ?? ?? ???10 |? ?? ???28000 |? ?? ?? ?? ???3 |? ?? ?? ???3200 |
+--------------+--------------+----------------+----------------+




3.提取2007-8-8 销售次数排前 10 销售人员编号和交易次数

??销售人员编号??交易数


select Sales_id as 销售人员编号,count(Trace_id) as 交易数
from sale_records group by Sales_id having Sales_id order by count(Trace_id) desc;
取出来的是排序的统计
+--------------+--------+
| 销售人员编号 | 交易数 |
+--------------+--------+
|? ?? ???56001 |? ???10 |
|? ?? ???56002 |? ?? ?2 |
|? ?? ???56003 |? ?? ?2 |
+--------------+--------+
(mysql中,因为mysql不支持top，故用limit代替)
select Sales_id as 销售人员编号,count(Trace_id) as 交易数
from sale_records group by Sales_id having Sales_id order by count(Trace_id) desc limit 2;
如：
+--------------+--------+
| 销售人员编号 | 交易数 |
+--------------+--------+
|? ?? ???56001 |? ???10 |
|? ?? ???56002 |? ?? ?2 |
+--------------+--------+??




4.提取2007-8-8 分店的销售金额超过10000的分店编号及交易金额

??分店编号??交易金额

??select Branch_id as 分店编号,sum(Total_amount) as 交易金额
??from sale_records
??group by Branch_id
??having sum(Total_amount)>10000;
如：

+----------+----------+
| 分店编号 | 交易金额 |
+----------+----------+
|? ???1001 |? ? 19200 |
|? ???1002 |? ? 13000 |
+----------+----------+

5.提取2007-8-8 分店号1001、1002的交易，交易金额分别是0-99、100-299、300以上的交易次数

??分店编号??0-99交易次数? ?100-299交易次数??300交易次数


select 分店编号,千元的交易次数,千元至千二元的交易次数 from
(
select Branch_id as 分店编号,count(Trace_id) as 千元的交易次数 from sale_records
where Total_amount=1000
? ?group by Branch_id
? ?having Branch_id in (1001,1002)
)q1,
(
select Branch_id as 分店编号2,count(Trace_id) as 千元至千二元的交易次数 from sale_records
where Total_amount>=1000 and Total_amount<=1200
? ?group by Branch_id
? ?having Branch_id in (1001,1002)
)q2 where q1.分店编号=q2.分店编号2;

如：
+----------+----------------