mysql表设计如下
CREATE TABLE `wz` (
`w_id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(60) NOT NULL,
`date1` int(10) unsigned NOT NULL COMMENT '创建日期',
`date2` int(10) unsigned NOT NULL COMMENT '修改日期',
PRIMARY KEY (`w_id`)
) ;####################################3
目前的文章页面是read.php?id=12需要选出date2比这篇文章大和比这篇文章小的文章,date2和date1使用的UNIX_TIMESTAMP(),
如何选出这样的两篇文章,如果没有date2比当前这篇文章大的或者小的就不用选其他的。
如何只用一句SQL语句,不知道大家有什么号的想法,
选出的是w_id,title,(总共两个记录)
CREATE TABLE `wz` (
`w_id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(60) NOT NULL,
`date1` int(10) unsigned NOT NULL COMMENT '创建日期',
`date2` int(10) unsigned NOT NULL COMMENT '修改日期',
PRIMARY KEY (`w_id`)
) ;####################################3
目前的文章页面是read.php?id=12需要选出date2比这篇文章大和比这篇文章小的文章,date2和date1使用的UNIX_TIMESTAMP(),
如何选出这样的两篇文章,如果没有date2比当前这篇文章大的或者小的就不用选其他的。
如何只用一句SQL语句,不知道大家有什么号的想法,
选出的是w_id,title,(总共两个记录)
union all
(select * from wz where date2>(select date2 from wz where id=2) order by date2 limit 1);
mysql> select * from track;
+------+--------+------------+----------+
| id | billid | trackdate | billstat |
+------+--------+------------+----------+
| 1 | 1 | 2009-01-02 | 已备货 |
| 2 | 1 | 2009-01-09 | 已装车 |
| 3 | 2 | 2009-01-12 | 已装车 |
| 4 | 3 | 2009-01-14 | 已备货 |
| 5 | 1 | 2009-01-14 | 已发货 |
| 6 | 3 | 2009-01-15 | 至海关 |
+------+--------+------------+----------+
6 rows in set (0.00 sec)mysql> select * from track where trackdate<(select trackdate from track where id
=2) order by trackdate desc limit 1
-> union all
-> select * from track where trackdate>(select trackdate from track where id
=2) order by trackdate limit 1;
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY
mysql> (select * from track where trackdate<(select trackdate from track where i
d=2) order by trackdate desc limit 1)
-> union all
-> (select * from track where trackdate>(select trackdate from track where i
d=2) order by trackdate limit 1);
+------+--------+------------+----------+
| id | billid | trackdate | billstat |
+------+--------+------------+----------+
| 1 | 1 | 2009-01-02 | 已备货 |
| 3 | 2 | 2009-01-12 | 已装车 |
+------+--------+------------+----------+
2 rows in set (0.00 sec)
union
(select * from wz where if(date2=null,id=2,date2>(select date2 from wz where id=2))order by date2 limit 1);