/*
Navicat MySQL Data TransferSource Server : 192.168.1.168
Source Server Version : 50016
Source Host : 192.168.1.168:3306
Source Database : testTarget Server Type : MYSQL
Target Server Version : 50016
File Encoding : 65001Date: 2010-07-07 13:25:05
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `orderdiscount`
-- ----------------------------
DROP TABLE IF EXISTS `orderdiscount`;
CREATE TABLE `orderdiscount` (
`od_id` bigint(20) NOT NULL auto_increment,
`od_orderno` varchar(50) default NULL COMMENT '订单编号',
`od_product` varchar(200) default NULL COMMENT '产品名称',
`od_productcode` varchar(200) default NULL COMMENT '产品代码',
`od_num` decimal(10,2) default NULL COMMENT '产品数量',
`od_type` varchar(200) default NULL COMMENT '折扣类型',
`od_typecode` varchar(50) default NULL,
`od_typevalue` varchar(200) default NULL COMMENT '折扣类型值',
`od_money` decimal(10,2) default NULL COMMENT '折扣金额',
`od_status` varchar(1) default NULL COMMENT '状态',
`od_prolevel` varchar(50) default NULL COMMENT '产品等级',
PRIMARY KEY (`od_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COMMENT='订单折扣存保表;';-- ----------------------------
-- Records of orderdiscount
-- ----------------------------
INSERT INTO `orderdiscount` VALUES ('354', '100521134954109', '热轧/宽带钢/Q235B/2.0/800', 'C08001001001001', '21.00', '产品等级', 'chanpindengji', '', '315.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('355', '100521134954109', '热轧/宽带钢/Q235B/2.0/801', 'C08001001001002', '85.00', '产品等级', 'chanpindengji', '', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('356', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '80.00', '产品等级', 'chanpindengji', '', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('357', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '2.00', '产品等级', 'chanpindengji', '', '30.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('358', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '1.00', '产品等级', 'chanpindengji', '', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('359', '100521134954109', '热轧/宽带钢/Q235B/2.0/800', 'C08001001001001', '21.00', '地区差价', 'city', '天津', '420.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('360', '100521134954109', '热轧/宽带钢/Q235B/2.0/801', 'C08001001001002', '85.00', '地区差价', 'city', '天津', '-850.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('361', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '80.00', '地区差价', 'city', '天津', '-800.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('362', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '2.00', '地区差价', 'city', '天津', '40.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('363', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '1.00', '地区差价', 'city', '天津', '-10.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('364', '100521134954109', '热轧/宽带钢/Q235B/2.0/800', 'C08001001001001', '21.00', '订单类型', 'dingdanleixing', '正式订单', '-735.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('365', '100521134954109', '热轧/宽带钢/Q235B/2.0/801', 'C08001001001002', '85.00', '订单类型', 'dingdanleixing', '正式订单', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('366', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '80.00', '订单类型', 'dingdanleixing', '正式订单', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('367', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '2.00', '订单类型', 'dingdanleixing', '正式订单', '-70.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('368', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '1.00', '订单类型', 'dingdanleixing', '正式订单', '0.00', '1', 'two');//要求
//1.查询所有记录显示
//2.产品等级 产品代码 产品名称 折扣类型相同时 把他们对应的数量和金额进行合计成为一条记录显示
Navicat MySQL Data TransferSource Server : 192.168.1.168
Source Server Version : 50016
Source Host : 192.168.1.168:3306
Source Database : testTarget Server Type : MYSQL
Target Server Version : 50016
File Encoding : 65001Date: 2010-07-07 13:25:05
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `orderdiscount`
-- ----------------------------
DROP TABLE IF EXISTS `orderdiscount`;
CREATE TABLE `orderdiscount` (
`od_id` bigint(20) NOT NULL auto_increment,
`od_orderno` varchar(50) default NULL COMMENT '订单编号',
`od_product` varchar(200) default NULL COMMENT '产品名称',
`od_productcode` varchar(200) default NULL COMMENT '产品代码',
`od_num` decimal(10,2) default NULL COMMENT '产品数量',
`od_type` varchar(200) default NULL COMMENT '折扣类型',
`od_typecode` varchar(50) default NULL,
`od_typevalue` varchar(200) default NULL COMMENT '折扣类型值',
`od_money` decimal(10,2) default NULL COMMENT '折扣金额',
`od_status` varchar(1) default NULL COMMENT '状态',
`od_prolevel` varchar(50) default NULL COMMENT '产品等级',
PRIMARY KEY (`od_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COMMENT='订单折扣存保表;';-- ----------------------------
-- Records of orderdiscount
-- ----------------------------
INSERT INTO `orderdiscount` VALUES ('354', '100521134954109', '热轧/宽带钢/Q235B/2.0/800', 'C08001001001001', '21.00', '产品等级', 'chanpindengji', '', '315.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('355', '100521134954109', '热轧/宽带钢/Q235B/2.0/801', 'C08001001001002', '85.00', '产品等级', 'chanpindengji', '', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('356', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '80.00', '产品等级', 'chanpindengji', '', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('357', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '2.00', '产品等级', 'chanpindengji', '', '30.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('358', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '1.00', '产品等级', 'chanpindengji', '', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('359', '100521134954109', '热轧/宽带钢/Q235B/2.0/800', 'C08001001001001', '21.00', '地区差价', 'city', '天津', '420.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('360', '100521134954109', '热轧/宽带钢/Q235B/2.0/801', 'C08001001001002', '85.00', '地区差价', 'city', '天津', '-850.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('361', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '80.00', '地区差价', 'city', '天津', '-800.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('362', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '2.00', '地区差价', 'city', '天津', '40.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('363', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '1.00', '地区差价', 'city', '天津', '-10.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('364', '100521134954109', '热轧/宽带钢/Q235B/2.0/800', 'C08001001001001', '21.00', '订单类型', 'dingdanleixing', '正式订单', '-735.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('365', '100521134954109', '热轧/宽带钢/Q235B/2.0/801', 'C08001001001002', '85.00', '订单类型', 'dingdanleixing', '正式订单', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('366', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '80.00', '订单类型', 'dingdanleixing', '正式订单', '0.00', '1', 'two');
INSERT INTO `orderdiscount` VALUES ('367', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '2.00', '订单类型', 'dingdanleixing', '正式订单', '-70.00', '1', 'one');
INSERT INTO `orderdiscount` VALUES ('368', '100521134954109', '热轧/宽带钢/Q235B/2.1/1.5', 'C08001001002001', '1.00', '订单类型', 'dingdanleixing', '正式订单', '0.00', '1', 'two');//要求
//1.查询所有记录显示
//2.产品等级 产品代码 产品名称 折扣类型相同时 把他们对应的数量和金额进行合计成为一条记录显示
2 select 产品等级,产品代码,产品名称,折扣类型 from tb group by 产品等级,产品代码,产品名称,折扣类型
2 select 产品等级,产品代码,产品名称,折扣类型 from tb group by 产品等级,产品代码,产品名称,折扣类型
订单类型 一等级 热轧/宽带钢/Q235B/2.1/1.5 2.00 -35.00 -70.00
订单类型 二等品 热轧/宽带钢/Q235B/2.0/801 85.00 0.00 0.00
订单类型 二等品 热轧/宽带钢/Q235B/2.1/1.5 80.00 0.00 0.00
订单类型 二等品 热轧/宽带钢/Q235B/2.1/1.5 1.00 0.00 0.00
地区差价 一等级 热轧/宽带钢/Q235B/2.0/800 21.00 20.00 420.00
地区差价 一等级 热轧/宽带钢/Q235B/2.1/1.5 2.00 20.00 40.00
地区差价 二等品 热轧/宽带钢/Q235B/2.0/801 85.00 -10.00 -850.00
地区差价 二等品 热轧/宽带钢/Q235B/2.1/1.5 80.00 -10.00 -800.00
地区差价 二等品 热轧/宽带钢/Q235B/2.1/1.5 1.00 -10.00 -10.00 显示如上面:红色的就是需要合计成一条记录的 并把他们的数量和金额进行相加
这样就行了 SQL怎么写?
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
2 select 产品等级,产品代码,产品名称,折扣类型,sum(产品数量),sum(折扣金额) from orderdiscount group by 产品等级,产品代码,产品名称,折扣类型