急求SQL语句! 本帖最后由 woshimajia0005 于 2010-07-13 18:13:24 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 例如上面 ec 为 WNZ 的有很多条, 选了3条后,就选下一个 ec CGO 具体SQL如下:---- 表的结构 `newtable`--CREATE TABLE IF NOT EXISTS `newtable` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `sc` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `ec` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `day` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `price` int(10) NOT NULL, `discount` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;---- 转存表中的数据 `newtable`--INSERT INTO `newtable` (`id`, `sc`, `ec`, `day`, `price`, `discount`) VALUES(3258, 'SHA', 'NKG', '2010-07-15', 310, '61'),(4254, 'SHA', 'NKG', '2010-07-18', 310, '61'),(3257, 'SHA', 'NKG', '2010-07-14', 310, '61'),(4255, 'SHA', 'NKG', '2010-07-19', 310, '61'),(914, 'SHA', 'WNZ', '2010-07-29', 330, '71'),(915, 'SHA', 'WNZ', '2010-07-30', 330, '71'),(916, 'SHA', 'WNZ', '2010-07-31', 330, '71'),(976, 'SHA', 'WNZ', '2010-07-28', 330, '71'),(1190, 'SHA', 'WNZ', '2010-07-25', 330, '71'),(1192, 'SHA', 'WNZ', '2010-07-27', 330, '71'),(1635, 'SHA', 'WNZ', '2010-07-24', 330, '71'),(1191, 'SHA', 'WNZ', '2010-07-26', 330, '71'),(191, 'SHA', 'CGO', '2010-09-05', 350, '57'),(145, 'SHA', 'CGO', '2010-09-04', 350, '57'),(144, 'SHA', 'CGO', '2010-09-03', 350, '57'),(66, 'SHA', 'CGO', '2010-08-30', 350, '57'),(69, 'SHA', 'CGO', '2010-09-02', 350, '57'),(64, 'SHA', 'CGO', '2010-08-28', 350, '57'),(65, 'SHA', 'CGO', '2010-08-29', 350, '57'),(68, 'SHA', 'CGO', '2010-09-01', 350, '57'),(67, 'SHA', 'CGO', '2010-08-31', 350, '57'),(192, 'SHA', 'CGO', '2010-09-06', 350, '57'),(193, 'SHA', 'CGO', '2010-09-07', 350, '57'),(1186, 'SHA', 'CGO', '2010-08-24', 350, '57'),(1184, 'SHA', 'CGO', '2010-08-22', 350, '56'), mysql> select * from newtable;+------+-----+-----+------------+-------+----------+| id | sc | ec | day | price | discount |+------+-----+-----+------------+-------+----------+| 3258 | SHA | NKG | 2010-07-15 | 310 | 61 || 4254 | SHA | NKG | 2010-07-18 | 310 | 61 || 3257 | SHA | NKG | 2010-07-14 | 310 | 61 || 4255 | SHA | NKG | 2010-07-19 | 310 | 61 || 914 | SHA | WNZ | 2010-07-29 | 330 | 71 || 915 | SHA | WNZ | 2010-07-30 | 330 | 71 || 916 | SHA | WNZ | 2010-07-31 | 330 | 71 || 976 | SHA | WNZ | 2010-07-28 | 330 | 71 || 1190 | SHA | WNZ | 2010-07-25 | 330 | 71 || 1192 | SHA | WNZ | 2010-07-27 | 330 | 71 || 1635 | SHA | WNZ | 2010-07-24 | 330 | 71 || 1191 | SHA | WNZ | 2010-07-26 | 330 | 71 || 191 | SHA | CGO | 2010-09-05 | 350 | 57 || 145 | SHA | CGO | 2010-09-04 | 350 | 57 || 144 | SHA | CGO | 2010-09-03 | 350 | 57 || 66 | SHA | CGO | 2010-08-30 | 350 | 57 || 69 | SHA | CGO | 2010-09-02 | 350 | 57 || 64 | SHA | CGO | 2010-08-28 | 350 | 57 || 65 | SHA | CGO | 2010-08-29 | 350 | 57 || 68 | SHA | CGO | 2010-09-01 | 350 | 57 || 67 | SHA | CGO | 2010-08-31 | 350 | 57 || 192 | SHA | CGO | 2010-09-06 | 350 | 57 || 193 | SHA | CGO | 2010-09-07 | 350 | 57 || 1186 | SHA | CGO | 2010-08-24 | 350 | 57 || 1184 | SHA | CGO | 2010-08-22 | 350 | 56 |+------+-----+-----+------------+-------+----------+25 rows in set (0.00 sec)mysql> select * -> from newtable a -> where 3>(select count(*) from newtable where sc=a.sc and ec=a.ec and id<a.id);+------+-----+-----+------------+-------+----------+| id | sc | ec | day | price | discount |+------+-----+-----+------------+-------+----------+| 3258 | SHA | NKG | 2010-07-15 | 310 | 61 || 4254 | SHA | NKG | 2010-07-18 | 310 | 61 || 3257 | SHA | NKG | 2010-07-14 | 310 | 61 || 914 | SHA | WNZ | 2010-07-29 | 330 | 71 || 915 | SHA | WNZ | 2010-07-30 | 330 | 71 || 916 | SHA | WNZ | 2010-07-31 | 330 | 71 || 66 | SHA | CGO | 2010-08-30 | 350 | 57 || 64 | SHA | CGO | 2010-08-28 | 350 | 57 || 65 | SHA | CGO | 2010-08-29 | 350 | 57 |+------+-----+-----+------------+-------+----------+9 rows in set (0.05 sec)mysql> 参考下贴中的多种方法http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html[征集]分组取最大N条记录方法征集,及散分.... 为什么指定索引更新数据也会造成死锁? insert union all 不能成功执行 请教一个模糊查询匹配的问题 请教一条SQL语句字符串截取问题 linux下 mysql不能 启动?? 【敢问MySQL除了免费之外,还有什么优点?况且操作起来这么麻烦!】 关于网页无法连接数据库,高手请帮忙看看! 请教:关于 MySQL 和 MySQL JDBC 的问题。 win7上安装多个mysql失败 新手mySQL查询不会了 连思路都没 咋办? 安装mysql出现10061错误 mysql 不能创建叫call的表
-- 表的结构 `newtable`
--CREATE TABLE IF NOT EXISTS `newtable` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`sc` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`ec` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`day` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`price` int(10) NOT NULL,
`discount` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;--
-- 转存表中的数据 `newtable`
--INSERT INTO `newtable` (`id`, `sc`, `ec`, `day`, `price`, `discount`) VALUES
(3258, 'SHA', 'NKG', '2010-07-15', 310, '61'),
(4254, 'SHA', 'NKG', '2010-07-18', 310, '61'),
(3257, 'SHA', 'NKG', '2010-07-14', 310, '61'),
(4255, 'SHA', 'NKG', '2010-07-19', 310, '61'),
(914, 'SHA', 'WNZ', '2010-07-29', 330, '71'),
(915, 'SHA', 'WNZ', '2010-07-30', 330, '71'),
(916, 'SHA', 'WNZ', '2010-07-31', 330, '71'),
(976, 'SHA', 'WNZ', '2010-07-28', 330, '71'),
(1190, 'SHA', 'WNZ', '2010-07-25', 330, '71'),
(1192, 'SHA', 'WNZ', '2010-07-27', 330, '71'),
(1635, 'SHA', 'WNZ', '2010-07-24', 330, '71'),
(1191, 'SHA', 'WNZ', '2010-07-26', 330, '71'),
(191, 'SHA', 'CGO', '2010-09-05', 350, '57'),
(145, 'SHA', 'CGO', '2010-09-04', 350, '57'),
(144, 'SHA', 'CGO', '2010-09-03', 350, '57'),
(66, 'SHA', 'CGO', '2010-08-30', 350, '57'),
(69, 'SHA', 'CGO', '2010-09-02', 350, '57'),
(64, 'SHA', 'CGO', '2010-08-28', 350, '57'),
(65, 'SHA', 'CGO', '2010-08-29', 350, '57'),
(68, 'SHA', 'CGO', '2010-09-01', 350, '57'),
(67, 'SHA', 'CGO', '2010-08-31', 350, '57'),
(192, 'SHA', 'CGO', '2010-09-06', 350, '57'),
(193, 'SHA', 'CGO', '2010-09-07', 350, '57'),
(1186, 'SHA', 'CGO', '2010-08-24', 350, '57'),
(1184, 'SHA', 'CGO', '2010-08-22', 350, '56'),
+------+-----+-----+------------+-------+----------+
| id | sc | ec | day | price | discount |
+------+-----+-----+------------+-------+----------+
| 3258 | SHA | NKG | 2010-07-15 | 310 | 61 |
| 4254 | SHA | NKG | 2010-07-18 | 310 | 61 |
| 3257 | SHA | NKG | 2010-07-14 | 310 | 61 |
| 4255 | SHA | NKG | 2010-07-19 | 310 | 61 |
| 914 | SHA | WNZ | 2010-07-29 | 330 | 71 |
| 915 | SHA | WNZ | 2010-07-30 | 330 | 71 |
| 916 | SHA | WNZ | 2010-07-31 | 330 | 71 |
| 976 | SHA | WNZ | 2010-07-28 | 330 | 71 |
| 1190 | SHA | WNZ | 2010-07-25 | 330 | 71 |
| 1192 | SHA | WNZ | 2010-07-27 | 330 | 71 |
| 1635 | SHA | WNZ | 2010-07-24 | 330 | 71 |
| 1191 | SHA | WNZ | 2010-07-26 | 330 | 71 |
| 191 | SHA | CGO | 2010-09-05 | 350 | 57 |
| 145 | SHA | CGO | 2010-09-04 | 350 | 57 |
| 144 | SHA | CGO | 2010-09-03 | 350 | 57 |
| 66 | SHA | CGO | 2010-08-30 | 350 | 57 |
| 69 | SHA | CGO | 2010-09-02 | 350 | 57 |
| 64 | SHA | CGO | 2010-08-28 | 350 | 57 |
| 65 | SHA | CGO | 2010-08-29 | 350 | 57 |
| 68 | SHA | CGO | 2010-09-01 | 350 | 57 |
| 67 | SHA | CGO | 2010-08-31 | 350 | 57 |
| 192 | SHA | CGO | 2010-09-06 | 350 | 57 |
| 193 | SHA | CGO | 2010-09-07 | 350 | 57 |
| 1186 | SHA | CGO | 2010-08-24 | 350 | 57 |
| 1184 | SHA | CGO | 2010-08-22 | 350 | 56 |
+------+-----+-----+------------+-------+----------+
25 rows in set (0.00 sec)mysql> select *
-> from newtable a
-> where 3>(select count(*) from newtable where sc=a.sc and ec=a.ec and id<a.id);
+------+-----+-----+------------+-------+----------+
| id | sc | ec | day | price | discount |
+------+-----+-----+------------+-------+----------+
| 3258 | SHA | NKG | 2010-07-15 | 310 | 61 |
| 4254 | SHA | NKG | 2010-07-18 | 310 | 61 |
| 3257 | SHA | NKG | 2010-07-14 | 310 | 61 |
| 914 | SHA | WNZ | 2010-07-29 | 330 | 71 |
| 915 | SHA | WNZ | 2010-07-30 | 330 | 71 |
| 916 | SHA | WNZ | 2010-07-31 | 330 | 71 |
| 66 | SHA | CGO | 2010-08-30 | 350 | 57 |
| 64 | SHA | CGO | 2010-08-28 | 350 | 57 |
| 65 | SHA | CGO | 2010-08-29 | 350 | 57 |
+------+-----+-----+------------+-------+----------+
9 rows in set (0.05 sec)mysql>
[征集]分组取最大N条记录方法征集,及散分....