sql 计算的问题

[libRA_kin]

小弟 现在有一个情况
目前有三张表 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,
  `remark` 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,
  `remark` 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,
  `remark` 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实现。数据比较少 希望能够帮忙的大侠 可以自己加入一两条数据做测试 如果有必要的话

[zuoxingyu]

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;

[ACMAIN_CHM]

你期望的结果什么样？ 另外建议你把无关列删除以方便其它人的理解和测试。

[libRA_kin]

谢谢各位 问题解决了 不过也比较麻烦的，明天把代码贴出来 现在先结贴给分！