一道sql面试题

来源：互联网发布：禁止软件后台运行编辑：程序博客网时间：2024/05/21 07:50

一商场的销售记帐单
表1
商品单价
电视机 2000
手机 1000
……
表2
时间售货员商品件数
2012-2-8 10:50 李明手机 1
2012-2-8 10:50 王红电视机 2
……
以下问题全部单次查询出结果
求全公司每月的销售额
求全公司每人每月的销售额
求全公司每月最大的单人销售额
求每月销售额最大的售货员

select concat(month(time),'月') as 月份,concat(sum(count*pri.pri),'元') as 销售额 from goods,pri where pri.name=goods.gs_name group by month(time);

select emp,month(time) ,sum(pri*count) from goods,pri where pri.name=goods.gs_name group by emp,month(time);

select m as 月份, max(money) as 本月单人最大销售额 from(select emp as n, month(time) as m ,sum(pri*count) as money from goods,pri where pri.name=goods.gs_name group by month(time),emp) as xx group by m;

select n as 本月最大销售额姓名 ,m as 月份 from (select emp as n, month(time) as m ,sum(pri*count) as money from goods,pri where pri.name=goods.gs_name group by month(time),emp) as xx group by m;

测试数据库sql语句

/*
MySQL Data Transfer
Source Host: localhost
Source Database: test
Target Host: localhost
Target Database: test
Date: 2013/4/18 9:57:53
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for goods
-- ----------------------------
CREATE TABLE `goods` (
`time` date DEFAULT NULL,
`emp` varchar(20) DEFAULT NULL,
`gs_name` varchar(50) DEFAULT NULL,
`count` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for pri
-- ----------------------------
CREATE TABLE `pri` (
`name` varchar(50) DEFAULT NULL,
`pri` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `goods` VALUES ('2013-04-18', '李明 ', '手机', '2');
INSERT INTO `goods` VALUES ('2013-04-12', '王红', '手机', '5');
INSERT INTO `goods` VALUES ('2013-04-12', '王红', '电视机', '1');
INSERT INTO `goods` VALUES ('2013-04-12', '李明', '电视机', '1');
INSERT INTO `goods` VALUES ('2013-04-12', '小王', '电视机', '10');
INSERT INTO `goods` VALUES ('2013-04-13', '小王', '手机', '1');
INSERT INTO `goods` VALUES ('2013-03-13', '小王', '手机', '1');
INSERT INTO `goods` VALUES ('2013-03-12', '李明', '电视机', '1');
INSERT INTO `goods` VALUES ('2013-02-12', '李明', '电视机', '1');
INSERT INTO `goods` VALUES ('2013-01-12', '王红', '手机', '5');
INSERT INTO `pri` VALUES ('手机', '1200');
INSERT INTO `pri` VALUES ('电视机', '2100');