小弟 现在有一个情况
目前有三张表 tb_score_base_addval a ;tb_score_item b ;tb_score_discount c
三张表 其中 b c 这两张表是一对多的关系,
下面我给出 这几张表的sql 语句以及 一些测试数据SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_score_base_addval
-- ----------------------------
CREATE TABLE `tb_score_base_addval` (
`optrid` varchar(20) default NULL,
`optrorg` varchar(20) default NULL,
`phoneno` varchar(20) default NULL,
`userid` varchar(20) default NULL,
`prodprcinsid` varchar(20) default NULL,
`prodprcinname` varchar(100) default NULL,
`custname` varchar(60) default NULL,
`createtime` datetime default NULL,
`optsn` varchar(30) NOT NULL,
`sessionid` varchar(128) NOT NULL,
`orgcode` varchar(20) default NULL,
`optuserid` varchar(20) NOT NULL,
`optdate` datetime NOT NULL,
`re` varchar(256) default NULL,
PRIMARY KEY (`optsn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tb_score_base_addval` VALUES ('NZZZZZ036', 'N', '139', null, '1232', null, null, null, 'tu686846876', 'hby465767', 'N', 'admin', '2010-10-18 14:51:00', null);
INSERT INTO `tb_score_base_addval` VALUES ('NZZZZZ044', 'N', '137', null, '1233', null, null, null, 'tu6868456', 'hby465524', 'N', 'admin', '2010-10-18 17:32:00', null);
INSERT INTO `tb_score_base_addval` VALUES ('NZZZZZ044', 'N', '137', null, 'G001', null, null, null, 't3456567', 'hby436', 'N', 'admin', '2010-10-18 17:51:43', null);SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_score_item
-- ----------------------------
CREATE TABLE `tb_score_item` (
`scoreitem` varchar(20) NOT NULL,
`scoreitemname` varchar(200) NOT NULL,
`biztype` char(1) NOT NULL COMMENT '0',
`itemparam` varchar(100) default NULL,
`cycle` char(1) default NULL,
`prestate` char(1) default NULL COMMENT '0:有效 1:无效',
`state` char(1) default NULL COMMENT '0:有效 1:无效',
`effdate` datetime default NULL,
`expdate` datetime default NULL,
`audituser` varchar(20) default NULL,
`auditdate` datetime default NULL,
`optsn` varchar(30) default NULL,
`sessionid` varchar(128) default NULL,
`orgcode` varchar(20) default NULL,
`optuserid` varchar(20) default NULL,
`optdate` datetime default NULL,
`re` varchar(256) default NULL,
PRIMARY KEY (`scoreitem`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tb_score_item` VALUES ('AZ20101011001', '娴佸姩绾㈡棗妫€鏌?, '4', null, '0', '0', '1', '2010-10-12 17:23:47', '2010-10-26 17:23:52', 'ADMIN', '2010-10-15 15:42:33', null, null, null, null, null, null);
INSERT INTO `tb_score_item` VALUES ('AZ20101011002', '寰瑧鏈嶅姟', '4', null, '0', '0', '1', '2010-10-11 17:23:39', '2010-10-27 17:23:44', 'ADMIN', '2010-10-15 15:33:24', null, null, null, null, null, null);
INSERT INTO `tb_score_item` VALUES ('AZ20101011003', '鍏朵粬', '4', null, '0', '0', '1', '2010-10-11 17:23:29', '2010-10-21 17:23:33', 'ADMIN', '2010-10-15 15:32:49', null, null, null, null, null, null);
INSERT INTO `tb_score_item` VALUES ('NT10100000000492', '188璐靛鍙?, '0', 'VIP188', '0', '0', '1', '2010-10-11 00:00:00', '2010-10-15 00:00:00', 'ADMIN', '2010-10-13 16:49:53', 'NB101000093942', 'DS101000015731', 'N', 'ADMIN', '2010-10-13 16:48:41', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000494', '娴嬭瘯绉垎椤圭洰', '1', '1232', '0', '0', '1', '2010-10-14 00:00:00', '2010-10-23 00:00:00', 'ADMIN', '2010-10-15 15:36:38', 'NB101000094085', 'DS101000015747', 'N', 'ADMIN', '2010-10-15 15:36:25', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000495', '娴嬭瘯椤圭洰', '0', '', '0', null, '0', '2010-10-13 00:00:00', '2010-11-01 00:00:00', null, null, 'NB101000093952', 'DS101000015735', 'N', 'ADMIN', '2010-10-13 17:06:18', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000496', 'IP5鍏冭鍒?, '1', '1233', '0', '0', '1', '2010-10-07 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:48:45', 'NB101000094127', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:43:08', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000497', 'gprs鍖呮湀', '1', 'G001', '0', '0', '1', '2010-10-01 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:48:41', 'NB101000094128', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:43:59', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000498', '楂橀€熶笂缃?, '2', 'TT001', '0', '0', '1', '2010-10-01 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:49:14', 'NB101000094129', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:47:20', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000499', '400鐢佃瘽涓氬姟', '2', 'C400', '0', '0', '1', '2010-10-01 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:49:08', 'NB101000094130', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:47:52', '');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_score_discount
-- ----------------------------
CREATE TABLE `tb_score_discount` (
`scoreitem` varchar(20) NOT NULL,
`lval` decimal(8,0) NOT NULL,
`rval` decimal(8,0) NOT NULL,
`score1` decimal(8,0) default NULL,
`score1cycle` decimal(8,0) default NULL,
`score2` decimal(8,0) default NULL,
`score2cycle` decimal(8,0) default NULL,
`score3` decimal(8,0) default NULL,
`score3cycle` decimal(8,0) default NULL,
`optsn` varchar(30) NOT NULL,
`sessionid` varchar(128) NOT NULL,
`orgcode` varchar(20) default NULL,
`optuserid` varchar(20) NOT NULL,
`optdate` datetime NOT NULL,
`re` varchar(256) default NULL,
PRIMARY KEY (`optsn`),
KEY `FK_Relationship_1` (`scoreitem`),
CONSTRAINT `FK_Relationship_1` FOREIGN KEY (`scoreitem`) REFERENCES `tb_score_item` (`scoreitem`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tb_score_discount` VALUES ('NT10100000000492', '1', '2', '1', '1', null, null, null, null, 'NT10100000000493', 'DS101000015731', 'N', 'ADMIN', '2010-10-13 16:50:19', '');
INSERT INTO `tb_score_discount` VALUES ('NT10100000000492', '3', '10', '2', '2', null, null, null, null, 'sdfdsf', 'sdfds', 'N', 'sdfd', '2010-10-18 16:35:44', null);
INSERT INTO `tb_score_discount` VALUES ('NT10100000000497', '3', '4', '2', '1', null, null, null, null, 'NT10100000000501', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 18:39:18', '');
INSERT INTO `tb_score_discount` VALUES ('NT10100000000494', '1', '2', '1', '1', null, null, null, null, 'NT10100000000502', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 18:39:33', '');
INSERT INTO `tb_score_discount` VALUES ('NT10100000000496', '1', '2', '2', '1', null, null, null, null, 'NT10100000000503', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 18:39:45', '');上面是几个表的建表语句和数据 数据库编码是utf-8这里看的乱码 我也不知道怎么回事 不过对操作影响不大 我现在想实现的是 按员工的工号统计出 所有的 tb_score_base_addval a 表中的对应tb_score_item b 表中的b.biztype=‘1’同时 b.state='1‘ and b.itemparam=a.prodprcinsid的记录总数再将这个记录数与 tb_score_discount c 这个表中的 c.lval c.rval 进行比较 获取满足条件的c.score1
条件是 这个记录数(暂时叫做acounuts) c.lval <=acounts<=c.rval ;
然后查出来这个acounts与c.score1的乘积我想用一条sql实现。数据比较少 希望能够帮忙的大侠 可以自己加入一两条数据做测试 如果有必要的话
目前有三张表 tb_score_base_addval a ;tb_score_item b ;tb_score_discount c
三张表 其中 b c 这两张表是一对多的关系,
下面我给出 这几张表的sql 语句以及 一些测试数据SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_score_base_addval
-- ----------------------------
CREATE TABLE `tb_score_base_addval` (
`optrid` varchar(20) default NULL,
`optrorg` varchar(20) default NULL,
`phoneno` varchar(20) default NULL,
`userid` varchar(20) default NULL,
`prodprcinsid` varchar(20) default NULL,
`prodprcinname` varchar(100) default NULL,
`custname` varchar(60) default NULL,
`createtime` datetime default NULL,
`optsn` varchar(30) NOT NULL,
`sessionid` varchar(128) NOT NULL,
`orgcode` varchar(20) default NULL,
`optuserid` varchar(20) NOT NULL,
`optdate` datetime NOT NULL,
`re` varchar(256) default NULL,
PRIMARY KEY (`optsn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tb_score_base_addval` VALUES ('NZZZZZ036', 'N', '139', null, '1232', null, null, null, 'tu686846876', 'hby465767', 'N', 'admin', '2010-10-18 14:51:00', null);
INSERT INTO `tb_score_base_addval` VALUES ('NZZZZZ044', 'N', '137', null, '1233', null, null, null, 'tu6868456', 'hby465524', 'N', 'admin', '2010-10-18 17:32:00', null);
INSERT INTO `tb_score_base_addval` VALUES ('NZZZZZ044', 'N', '137', null, 'G001', null, null, null, 't3456567', 'hby436', 'N', 'admin', '2010-10-18 17:51:43', null);SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_score_item
-- ----------------------------
CREATE TABLE `tb_score_item` (
`scoreitem` varchar(20) NOT NULL,
`scoreitemname` varchar(200) NOT NULL,
`biztype` char(1) NOT NULL COMMENT '0',
`itemparam` varchar(100) default NULL,
`cycle` char(1) default NULL,
`prestate` char(1) default NULL COMMENT '0:有效 1:无效',
`state` char(1) default NULL COMMENT '0:有效 1:无效',
`effdate` datetime default NULL,
`expdate` datetime default NULL,
`audituser` varchar(20) default NULL,
`auditdate` datetime default NULL,
`optsn` varchar(30) default NULL,
`sessionid` varchar(128) default NULL,
`orgcode` varchar(20) default NULL,
`optuserid` varchar(20) default NULL,
`optdate` datetime default NULL,
`re` varchar(256) default NULL,
PRIMARY KEY (`scoreitem`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tb_score_item` VALUES ('AZ20101011001', '娴佸姩绾㈡棗妫€鏌?, '4', null, '0', '0', '1', '2010-10-12 17:23:47', '2010-10-26 17:23:52', 'ADMIN', '2010-10-15 15:42:33', null, null, null, null, null, null);
INSERT INTO `tb_score_item` VALUES ('AZ20101011002', '寰瑧鏈嶅姟', '4', null, '0', '0', '1', '2010-10-11 17:23:39', '2010-10-27 17:23:44', 'ADMIN', '2010-10-15 15:33:24', null, null, null, null, null, null);
INSERT INTO `tb_score_item` VALUES ('AZ20101011003', '鍏朵粬', '4', null, '0', '0', '1', '2010-10-11 17:23:29', '2010-10-21 17:23:33', 'ADMIN', '2010-10-15 15:32:49', null, null, null, null, null, null);
INSERT INTO `tb_score_item` VALUES ('NT10100000000492', '188璐靛鍙?, '0', 'VIP188', '0', '0', '1', '2010-10-11 00:00:00', '2010-10-15 00:00:00', 'ADMIN', '2010-10-13 16:49:53', 'NB101000093942', 'DS101000015731', 'N', 'ADMIN', '2010-10-13 16:48:41', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000494', '娴嬭瘯绉垎椤圭洰', '1', '1232', '0', '0', '1', '2010-10-14 00:00:00', '2010-10-23 00:00:00', 'ADMIN', '2010-10-15 15:36:38', 'NB101000094085', 'DS101000015747', 'N', 'ADMIN', '2010-10-15 15:36:25', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000495', '娴嬭瘯椤圭洰', '0', '', '0', null, '0', '2010-10-13 00:00:00', '2010-11-01 00:00:00', null, null, 'NB101000093952', 'DS101000015735', 'N', 'ADMIN', '2010-10-13 17:06:18', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000496', 'IP5鍏冭鍒?, '1', '1233', '0', '0', '1', '2010-10-07 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:48:45', 'NB101000094127', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:43:08', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000497', 'gprs鍖呮湀', '1', 'G001', '0', '0', '1', '2010-10-01 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:48:41', 'NB101000094128', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:43:59', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000498', '楂橀€熶笂缃?, '2', 'TT001', '0', '0', '1', '2010-10-01 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:49:14', 'NB101000094129', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:47:20', '');
INSERT INTO `tb_score_item` VALUES ('NT10100000000499', '400鐢佃瘽涓氬姟', '2', 'C400', '0', '0', '1', '2010-10-01 00:00:00', '2010-11-30 00:00:00', 'ADMIN', '2010-10-18 17:49:08', 'NB101000094130', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 17:47:52', '');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_score_discount
-- ----------------------------
CREATE TABLE `tb_score_discount` (
`scoreitem` varchar(20) NOT NULL,
`lval` decimal(8,0) NOT NULL,
`rval` decimal(8,0) NOT NULL,
`score1` decimal(8,0) default NULL,
`score1cycle` decimal(8,0) default NULL,
`score2` decimal(8,0) default NULL,
`score2cycle` decimal(8,0) default NULL,
`score3` decimal(8,0) default NULL,
`score3cycle` decimal(8,0) default NULL,
`optsn` varchar(30) NOT NULL,
`sessionid` varchar(128) NOT NULL,
`orgcode` varchar(20) default NULL,
`optuserid` varchar(20) NOT NULL,
`optdate` datetime NOT NULL,
`re` varchar(256) default NULL,
PRIMARY KEY (`optsn`),
KEY `FK_Relationship_1` (`scoreitem`),
CONSTRAINT `FK_Relationship_1` FOREIGN KEY (`scoreitem`) REFERENCES `tb_score_item` (`scoreitem`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tb_score_discount` VALUES ('NT10100000000492', '1', '2', '1', '1', null, null, null, null, 'NT10100000000493', 'DS101000015731', 'N', 'ADMIN', '2010-10-13 16:50:19', '');
INSERT INTO `tb_score_discount` VALUES ('NT10100000000492', '3', '10', '2', '2', null, null, null, null, 'sdfdsf', 'sdfds', 'N', 'sdfd', '2010-10-18 16:35:44', null);
INSERT INTO `tb_score_discount` VALUES ('NT10100000000497', '3', '4', '2', '1', null, null, null, null, 'NT10100000000501', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 18:39:18', '');
INSERT INTO `tb_score_discount` VALUES ('NT10100000000494', '1', '2', '1', '1', null, null, null, null, 'NT10100000000502', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 18:39:33', '');
INSERT INTO `tb_score_discount` VALUES ('NT10100000000496', '1', '2', '2', '1', null, null, null, null, 'NT10100000000503', 'DS101000015759', 'N', 'ADMIN', '2010-10-18 18:39:45', '');上面是几个表的建表语句和数据 数据库编码是utf-8这里看的乱码 我也不知道怎么回事 不过对操作影响不大 我现在想实现的是 按员工的工号统计出 所有的 tb_score_base_addval a 表中的对应tb_score_item b 表中的b.biztype=‘1’同时 b.state='1‘ and b.itemparam=a.prodprcinsid的记录总数再将这个记录数与 tb_score_discount c 这个表中的 c.lval c.rval 进行比较 获取满足条件的c.score1
条件是 这个记录数(暂时叫做acounuts) c.lval <=acounts<=c.rval ;
然后查出来这个acounts与c.score1的乘积我想用一条sql实现。数据比较少 希望能够帮忙的大侠 可以自己加入一两条数据做测试 如果有必要的话
解决方案 »
- Mysql中如何将id大于某一值,如2,的所有记录的id,减1, 再写回数据库?
- 求问mysql update内部工作机制
- MYSQL下的幽灵表`````怎么能把它找出来`~~
- 求个SQL语句
- 急救
- Mysql是否存在函数,可将result的结果(只有一列,N行)存为一个数组,请教了.
- 设置完root密码后mysql不能启动,该如何设置?
- mysql查询求助~
- You have an error in your SQL syntax; check the manual that corresponds to your
- 新手求教:使用MAX后无法SQL报错
- 求删除语句
- 问个简单的查询问题,速度来拿分!
select count(*) into @A from tb_score_base_addval a inner join tb_score_item b on a.optuserid=b.optuserid where b.biztype='1' and( b.state='1' and b.itemparam=a.prodprcinsid);
select @A*c.score1 from tb_score_discount c where c.lval <=@A and @A<=c.rval;