CREATE TABLE IF NOT EXISTS `test` (
`nid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`vcwaybill` varchar(20) NOT NULL,
`ntype` smallint(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 AUTO_INCREMENT=14 ;--
-- 转存表中的数据 `test`
--INSERT INTO `test` (`nid`, `vcwaybill`, `ntype`) VALUES
(1, 'xd0001', 1001),
(2, 'xd0001', 1001),
(3, 'xd0002', 1001),
(4, 'xd0002', 1002),
(5, 'xd0002', 1003),
(6, 'xd0002', 1003),
(7, 'xd0002', 1004),
(8, 'xd0003', 1001),
(9, 'xd0003', 1001),
(10, 'xd0003', 1002),
(11, 'xd0004', 1001),
(12, 'xd0004', 1002),
(13, 'xd0004', 1002);表结构和记录如上,我想实现这样的功能:把vcwaybill和ntype都相同的记录列出来。如:(1, 'xd0001', 1001),
(2, 'xd0001', 1001),
(5, 'xd0002', 1003),
(6, 'xd0002', 1003),
(8, 'xd0003', 1001),
(9, 'xd0003', 1001),
(12, 'xd0004', 1002),
(13, 'xd0004', 1002)
`nid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`vcwaybill` varchar(20) NOT NULL,
`ntype` smallint(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 AUTO_INCREMENT=14 ;--
-- 转存表中的数据 `test`
--INSERT INTO `test` (`nid`, `vcwaybill`, `ntype`) VALUES
(1, 'xd0001', 1001),
(2, 'xd0001', 1001),
(3, 'xd0002', 1001),
(4, 'xd0002', 1002),
(5, 'xd0002', 1003),
(6, 'xd0002', 1003),
(7, 'xd0002', 1004),
(8, 'xd0003', 1001),
(9, 'xd0003', 1001),
(10, 'xd0003', 1002),
(11, 'xd0004', 1001),
(12, 'xd0004', 1002),
(13, 'xd0004', 1002);表结构和记录如上,我想实现这样的功能:把vcwaybill和ntype都相同的记录列出来。如:(1, 'xd0001', 1001),
(2, 'xd0001', 1001),
(5, 'xd0002', 1003),
(6, 'xd0002', 1003),
(8, 'xd0003', 1001),
(9, 'xd0003', 1001),
(12, 'xd0004', 1002),
(13, 'xd0004', 1002)
-> where exists (select nid from test t2 where t2.nid<>t1.nid and t2.vcwaybill=t1.vcwaybill and t2.ntype=t1.ntype);
+-----+-----------+-------+
| nid | vcwaybill | ntype |
+-----+-----------+-------+
| 1 | xd0001 | 1001 |
| 2 | xd0001 | 1001 |
| 5 | xd0002 | 1003 |
| 6 | xd0002 | 1003 |
| 8 | xd0003 | 1001 |
| 9 | xd0003 | 1001 |
| 12 | xd0004 | 1002 |
| 13 | xd0004 | 1002 |
+-----+-----------+-------+
8 rows in set (0.00 sec)