怎么查询A表中字段1里的值，在B表中字段1的值不存在并统计显示出来

贴记录出来看看
TRY:
SELECT A.* FROM A LEFT JOIN B ON INSTR(A.F1,B.F1)>0 WHERE B.F1 IS NULL

select col1 from a where col1 not in(select distinct col1 from b);

OR
select A.* from a LEFT JOIN B ON A.F1=B.F1 WHERE B.F1 IS NULL;

select notification.nf_no from notification left join balancemoney on instr(notification.nf_no,balancemoney.bm_kaifpno)>0 where balancemoney.bm_kaifpno is null
我照着你的改好像查不出来数据

select nf_no from notification where nf_no not in(select distinct nf_no from balancemoney);也没查询出数据来

贴建表及插入记录的SQL，及要求结果出来看看

/*
Navicat MySQL Data TransferSource Server         : mysql5
Source Server Version : 50016
Source Host           : localhost:3307
Source Database       : seawind_sfaTarget Server Type    : MYSQL
Target Server Version : 50016
File Encoding         : 65001Date: 2011-04-27 17:02:31
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `notification`
-- ----------------------------
DROP TABLE IF EXISTS `notification`;
CREATE TABLE `notification` (
  `id` int(11) NOT NULL auto_increment,
  `nf_no` varchar(50) default NULL COMMENT '唯一编码',
  `nf_level` varchar(50) default NULL COMMENT '等级',
  `nf_product` varchar(200) default NULL COMMENT '产品名称',
  `nf_productcode` varchar(200) default NULL,
  `nf_num` decimal(10,3) default NULL COMMENT '数量',
  `nf_item` decimal(10,2) default NULL COMMENT '件数',
  `nf_bzprice` decimal(10,2) default '0.00' COMMENT '标准单价',
  `nf_bzmoney` decimal(10,2) default '0.00' COMMENT '标准金额',
  `nf_floatmoney` decimal(10,2) default '0.00' COMMENT '浮动金额',
  `nf_price` decimal(10,2) default '0.00' COMMENT '特殊单价',
  `nf_money` decimal(10,2) default '0.00' COMMENT '特殊金额',
  `nf_ckid` varchar(200) default NULL COMMENT '出库主键',
  `nf_ckdate` datetime default NULL COMMENT '出库日期',
  `nf_paytype` varchar(50) default NULL COMMENT '付款类型',
  `nf_re` mediumtext COMMENT '注备',
  `nf_unit` int(11) default NULL COMMENT '公司标志',
  `nf_infno` varchar(50) default NULL COMMENT '主表唯一编号',
  `nf_lwight` decimal(10,0) default NULL,
  `nf_measures1` int(9) default NULL,
  `nf_measures2` int(9) default NULL,
  `nf_brand` varchar(200) default NULL,
  `nf_orderno` varchar(50) default NULL COMMENT '订单编号',
  `nf_zlyymoney` decimal(20,2) default '0.00' COMMENT '质量异议',
  `nf_payno` varchar(50) default NULL COMMENT '付款编号',
  `nf_inf_auditeonetime` datetime default NULL,
  `nf_inf_auditetwotime` datetime default NULL,
  `nf_inf_auditeoneman` varchar(200) default NULL,
  `nf_inf_auditetwoman` varchar(200) default NULL,
  `nf_inf_auditeonemind` mediumtext,
  `nf_inf_auditetwomind` mediumtext,
  `nf_inf_inputtime` datetime default NULL,
  `nf_inf_inputman` varchar(200) default NULL,
  `nf_cuscode` varchar(200) default NULL,
  `nf_cusname` varchar(200) default NULL,
  `nf_inf_status` varchar(10) default NULL COMMENT '状态【1初始化【11一级退回【2送审【22二级退回【3一级审核通过【33回退(二级审核)调整【4二级审核通过',
  `nf_fanli` varchar(10) default '0' COMMENT '返利标志(0未返利)',
  `nf_floatfanli` decimal(10,2) default '0.00' COMMENT '返利单价',
  `nf_pricetype` varchar(10) default NULL COMMENT '价格取方式(0订单约定1随行就市2保定)',
  `nf_Invaliddate` datetime default NULL COMMENT '失效日期',
  `nf_jlstly` varchar(50) default NULL COMMENT '计量方式',
  `nf_fanliprice` decimal(20,2) default NULL,
  `nf_fanlimoney` decimal(20,2) default NULL,
  `nf_jingbandept` int(11) default NULL COMMENT '经办部门',
  `nf_jingbanman` varchar(50) default NULL COMMENT '经办人',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COMMENT='(销售）发票通知单表;';-- ----------------------------
-- Records of notification
-- ----------------------------
INSERT INTO `notification` VALUES ('1478', 'XK20101223001', 'one', '热轧槽钢 Q235B 22#a 220*77*7.0 9米', '10003001010005', '28.920', '7.00', '4550.00', '131586.00', '0.00', '0.00', '0.00', '03482c124cfe4dc8ad2e6fd78d7a84e1', '2010-12-22 17:01:29', '1', null, '1', 'XK20101223001', '0', '1', '3', '', 'XD20101205026', '0.00', null, '2010-12-23 14:39:49', '2010-12-23 14:39:58', '系统管理员', '系统管理员', '同意', '同意', '2010-12-23 10:58:28', '邵文呈', '10574', '四川华鑫蓉通贸易有限公司', '33', '0', '0.00', null, null, '0', null, null, '23', '齐广宝');
INSERT INTO `notification` VALUES ('1479', 'XK20101223001', 'one', '热轧槽钢 Q235B 22#b 220*79*9.0 9米', '10003001011004', '28.520', '6.00', '4550.00', '129766.00', '0.00', '0.00', '0.00', '1d19f9524e83424f93c1d428413b3edb', '2010-12-22 17:01:29', '1', null, '1', 'XK20101223001', '0', '1', '3', '', 'XD20101205026', '0.00', null, '2010-12-23 14:39:49', '2010-12-23 14:39:58', '系统管理员', '系统管理员', '同意', '同意', '2010-12-23 10:58:28', '邵文呈', '10574', '四川华鑫蓉通贸易有限公司', '33', '0', '0.00', null, null, '0', null, null, '23', '齐广宝');
INSERT INTO `notification` VALUES ('1480', 'XK20101223001', 'one', '工字钢 Q235B 28#b 280*124*10.5 12米', '10001001011007', '57.660', '11.00', '4550.00', '262353.00', '0.00', '0.00', '0.00', '4f292214e0354361b279c7b60da4d7b2', '2010-12-11 16:47:51', '1', null, '1', 'XK20101223001', '0', '1', '3', '', 'XD20101205026', '0.00', null, '2010-12-23 14:39:49', '2010-12-23 14:39:58', '系统管理员', '系统管理员', '同意', '同意', '2010-12-23 10:58:28', '邵文呈', '10574', '四川华鑫蓉通贸易有限公司', '33', '0', '0.00', null, null, '0', null, null, '23', '齐广宝');
INSERT INTO `notification` VALUES ('1481', 'XK20101223001', 'one', '工字钢 Q235B 20#a 200*100*7.0 9米', '10001001003006', '59.280', '16.00', '4530.00', '268538.40', '0.00', '0.00', '0.00', '57ad877f3fd94fc9b995e0d8e6f405a3', '2010-12-05 00:00:00', '1', null, '1', 'XK20101223001', '0', '1', '3', '', 'XD20101205026', '0.00', null, '2010-12-23 14:39:49', '2010-12-23 14:39:58', '系统管理员', '系统管理员', '同意', '同意', '2010-12-23 10:58:28', '邵文呈', '10574', '四川华鑫蓉通贸易有限公司', '33', '0', '0.00', null, null, '0', null, null, '23', '范圣培');/*
Navicat MySQL Data TransferSource Server         : mysql5
Source Server Version : 50016
Source Host           : localhost:3307
Source Database       : seawind_sfaTarget Server Type    : MYSQL
Target Server Version : 50016
File Encoding         : 65001Date: 2011-04-27 17:03:28
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `balancemoney`
-- ----------------------------
DROP TABLE IF EXISTS `balancemoney`;
CREATE TABLE `balancemoney` (
  `bm_id` bigint(11) NOT NULL auto_increment,
  `bm_no` varchar(50) default NULL COMMENT '编号',
  `bm_cuscode` varchar(50) default NULL COMMENT '客户代码',
  `bm_cusname` varchar(200) default NULL COMMENT '客户名称',
  `bm_yskno` varchar(50) default NULL COMMENT '预收款编号',
  `bm_pjno` varchar(50) default NULL COMMENT '票据号',
  `bm_zddate` datetime default NULL COMMENT '制单日期',
  `bm_cpdate` datetime default NULL COMMENT '出票日期',
  `bm_dqdate` datetime default NULL COMMENT ' 到期日期',
  `bm_paytype` varchar(50) default NULL COMMENT '付款类型(现汇1或承兑2或三个月承兑3)',
  `bm_payStyle` varchar(50) default NULL COMMENT '付款方式',
  `bm_money` decimal(20,2) default NULL COMMENT '本次结转金额',
  `bm_jiezhuantime` datetime default NULL COMMENT '结转时间',
  `bm_jiezhaunmoney` decimal(20,2) default NULL COMMENT '本次结转后余额',
  `bm_typestatus` varchar(20) default NULL COMMENT '1预收款2余额结转3余额冲加4余额冲减5开发票6承兑转现汇',
  `bm_kaifpno` varchar(50) default NULL COMMENT '发票号',
  `bm_unit` int(11) default NULL COMMENT '公司',
  `bm_usestatus` varchar(10) default '0' COMMENT '是否使用（1使用）',
  `bm_cjman` varchar(50) default NULL COMMENT '冲减人',
  `bm_re` mediumtext COMMENT '冲减备注',
  PRIMARY KEY  (`bm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COMMENT='(销售)客户余额表; ';-- ----------------------------
-- Records of balancemoney
-- ----------------------------
INSERT INTO `balancemoney` VALUES ('194', 'XB20101126004', '10576', '重庆林耀物流有限公司', 'XY20101126165', '', '2010-11-26 00:00:00', '2010-11-24 00:00:00', '2010-11-24 00:00:00', '1', '电汇', '2319169.88', '2010-11-26 12:57:51', '2319169.88', '1', '', '1', '1', null, null);
INSERT INTO `balancemoney` VALUES ('195', 'XB20101126005', '10595', '张百军', 'XY20101126186', '', null, '2010-11-24 00:00:00', null, '1', '电汇', '23106.00', '2010-11-26 12:57:51', '23106.00', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('196', 'XB20101126006', '10583', '陕西创维物资经销有限责任公司', 'XY20101126187', '', null, '2010-11-24 00:00:00', null, '1', '电汇', '15.68', '2010-11-26 12:57:51', '15.68', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('197', 'XB20101126007', '10608', '湖南金诚钢铁贸易有限公司', 'XY20101126188', '', '2010-11-24 00:00:00', '2010-11-24 00:00:00', '2010-11-26 00:00:00', '1', '电汇', '0.00', '2010-11-26 12:57:51', '0.00', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('201', 'XB20101126011', '10618', '成都市大红亿钢铁发展有限公司', 'XY20101126192', '', '2010-11-24 00:00:00', '2010-11-24 00:00:00', '2010-11-26 00:00:00', '2', '承兑汇票', '6058.24', '2010-11-27 00:02:54', '6058.24', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('203', 'XB20101126013', '10173', '莱芜市钢城区银海物资中心', 'XY20101124014', '', null, '2010-11-24 00:00:00', null, '1', '电汇', '-50747.82', '2010-11-26 12:58:06', '-50747.82', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('204', 'XB20101126014', '10173', '莱芜市钢城区银海物资中心', 'XY20101124015', '', null, '2010-11-24 00:00:00', null, '2', '承兑汇票', '50746.97', '2010-11-26 12:58:06', '-0.85', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('205', 'XB20101126015', '10055', '莱芜市金都物资有限公司', 'XY20101124016', '', null, '2010-11-24 00:00:00', null, '1', '电汇', '548.70', '2010-11-26 12:58:06', '548.70', '1', null, '1', '0', null, null);
INSERT INTO `balancemoney` VALUES ('206', 'XB20101126016', '10003', '钢都粉末冶金厂', 'XY20101124017', '', '2010-11-24 00:00:00', '2010-11-24 00:00:00', '2010-11-26 00:00:00', '1', '电汇', '-39297.40', '2010-11-26 12:58:06', '-39297.40', '1', null, '1', '0', null, null);

肯定有的我需要查询出 notification表中字段nf_no的值在 balancemoney表中字段bm_kaifpno中不存在的记录

select notification.nf_cusname,notification.nf_no from notification LEFT JOIN balancemoney ON notification.nf_no=balancemoney.bm_kaifpno WHERE balancemoney.bm_kaifpno IS NULL group by notification.nf_no order by notification.nf_cusname,notification.nf_no
OK了

调试易

怎么查询A表中字段1里的值，在B表中字段1的值不存在并统计显示出来

解决方案 »