表tab_rec : v_path, 类型是varchar(255),内容形如d:\temp\20091116\,其中20091118是日期;
表tab_rec_copy : v_path
表tab_bak : v_path , 怎样从tab_rec和tab_rec_copy两表中找到离当前日期最近的v_path,且该v_path值不存在于表tab_bak中
表tab_rec_copy : v_path
表tab_bak : v_path , 怎样从tab_rec和tab_rec_copy两表中找到离当前日期最近的v_path,且该v_path值不存在于表tab_bak中
(
select * from tab_rec
union all
select * from tab_rec_copy
) a
where v_path=(
select max(max_v_path)
from (
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec
where v_path not in (select v_path from tab_bak)
union all
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec_copy
where v_path not in (select v_path from tab_bak)
) t
)感觉很复杂,直接按你的语义来写,建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。这样或许有更有效的语句来实现相同的功能。
表tab_rec_copy : v_path
表tab_bak : v_path , 怎样从tab_rec和tab_rec_copy两表中找到离某个日期(不妨定为20091112)最近的v_path,且该v_path值不存在于表tab_bak中
(
select v_path from tab_rec
union all
select v_path from tab_rec_copy
) a
where v_path=(
select max(max_v_path)
from (
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec
where v_path not in (select v_path from tab_bak)
union all
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec_copy
where v_path not in (select v_path from tab_bak)
) t
)
我需要‘找到离某个日期(不妨定为20091112)最近的v_path', 而不是’最大的日期‘
(
select v_path from tab_rec where v_path not in (select v_path from tab_bak)
union
select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
) t2
where
left(substring_index(t2.v_path,'\\',-2),8) =
(
select max(left(substring_index(t1.v_path,'\\',-2),8))
(
select v_path from tab_rec where v_path not in (select v_path from tab_bak)
union all
select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
) t1
)
select t2.*
(
select v_path from tab_rec where v_path not in (select v_path from tab_bak)
union
select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
) t2
where
left(substring_index(t2.v_path,'\\',-2),8) =
(
select max(left(substring_index(t1.v_path,'\\',-2),8))
(
select v_path from tab_rec where v_path not in (select v_path from tab_bak) and left(substring_index(t1.v_path,'\\',-2),8)<'20091112'
union all
select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak) and left(substring_index(t1.v_path,'\\',-2),8)<'20091112'
) t1
)
select ma from (select max(v_path) as ma from (
select v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') AS NEWRQ from tab_rec
union all
select v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') from tab_rec_copy) a where
NEWRQ<DATE_FORMAT(CURDATE(),'%Y%m%%d')) a1
left join
tab_bak a2
on a1.ma=a2.v_path where a2.v_path is null
d:\temp\20091109\,
d:\temp\20091110\,
d:\temp\20091116\,
表tab_rec_copy : v_path
d:\temp\20091110\,
d:\temp\20091111\,
d:\temp\20091116\,
表tab_bak : v_path
d:\temp\20091111\-----------------------------------
期望结果 d:\temp\20091110\,
20091113哪一个算是最近?如果是 20091110 ,也就是比20091112 小的最近的,则可以如下,
当然我是瞎猜的,具体怎么定义只有你自己清楚了。select v_path from
(
select v_path from tab_rec
union all
select v_path from tab_rec_copy
) a
where v_path=(
select max(max_v_path)
from (
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec
where v_path not in (select v_path from tab_bak)
and SUBSTRING_INDEX(v_path,'\\',-2),'20091112\\'
union all
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec_copy
where v_path not in (select v_path from tab_bak)
and SUBSTRING_INDEX(v_path,'\\',-2),'20091112\\'
) t
)
d:\temp\20091109\,
d:\temp\20091110\,
d:\temp\20091116\,
表tab_rec_copy : v_path
d:\temp\20091110\,
d:\temp\20091111\,
d:\temp\20091116\,
表tab_bak : v_path
d:\temp\20091111\ -----------------------------------
输入:20091112
期望结果 d:\temp\20091110\,
----------------
输入:20091110
期望结果 d:\temp\20091109\,
(
select v_path from tab_rec where v_path not in (select v_path from tab_bak)
union
select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
) t2
where
left(substring_index(t2.v_path,'\\',-2),8) =
(
select max(left(substring_index(t1.v_path,'\\',-2),8))
(
select v_path from tab_rec where v_path not in
(select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091112'
union all
select v_path from tab_rec_copy where v_path not in
(select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091112'
) t1
)
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') AS NEWRQ FROM tab_rec
UNION ALL
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') FROM tab_rec_copy) a3
INNER JOIN
(SELECT ma FROM (
SELECT MAX(v_path) AS ma FROM (
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') AS NEWRQ FROM tab_rec
UNION ALL
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') FROM tab_rec_copy) a
WHERE NEWRQ <'20091110') a1
LEFT JOIN
tab_bak a2
ON a1.ma=a2.v_path WHERE a2.v_path IS NULL) a4
ON a3.NEWRQ=a4.ma
WWWWA的语句倒是可以执行,不过输入是什么,结果都是NULL
我把测试表贴出来吧,哪位有空就帮我测下,咳,测试结果预期是
-----------------------------------
输入:20091112
期望结果 d:\temp\20091110\,
----------------
输入:20091110
期望结果 d:\temp\20091109\, 以下是表结构
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tab_rec
-- ----------------------------
CREATE TABLE `tab_rec` (
`v_path` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tab_rec` VALUES ('d:\\temp\\20091109\\');
INSERT INTO `tab_rec` VALUES ('d:\\temp\\20091110\\');
INSERT INTO `tab_rec` VALUES ('d:\\temp\\20091116\\');
INSERT INTO `tab_rec` VALUES ('d:\\temp\\20091110\\');
INSERT INTO `tab_rec` VALUES ('d:\\temp\\20091116\\');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tab_rec_copy
-- ----------------------------
CREATE TABLE `tab_rec_copy` (
`v_path` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tab_rec_copy` VALUES ('d:\\temp\\20091110\\');
INSERT INTO `tab_rec_copy` VALUES ('d:\\temp\\20091116\\');
INSERT INTO `tab_rec_copy` VALUES ('d:\\temp\\20091111\\');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tab_bak
-- ----------------------------
CREATE TABLE `tab_bak` (
`v_path` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tab_bak` VALUES ('d:\\temp\\20091111\\');
from
(
select v_path from tab_rec where v_path not in (select v_path from tab_bak)
union
select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
) t2
where
left(substring_index(t2.v_path,'\\',-2),8) =
(
select max(left(substring_index(t1.v_path,'\\',-2),8))
from
(
select v_path from tab_rec where v_path not in
(select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091112'
union all
select v_path from tab_rec_copy where v_path not in
(select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091112'
) t1
)
Database changed
mysql> select t2.v_path
-> from
-> (
-> select v_path from tab_rec where v_path not in (select v_path from tab_bak)
-> union
-> select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
-> ) t2
-> where
-> left(substring_index(t2.v_path,'\\',-2),8) =
-> (
-> select max(left(substring_index(t1.v_path,'\\',-2),8))
-> from
-> (
-> select v_path from tab_rec where v_path not in
-> (select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091112'
-> union all
-> select v_path from tab_rec_copy where v_path not in
-> (select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091112'
-> ) t1
-> );
+-------------------+
| v_path |
+-------------------+
| d:\temp\20091110\ |
+-------------------+
1 row in set (0.00 sec)mysql>
-> from
-> (
-> select v_path from tab_rec where v_path not in (select v_path from tab_bak)
-> union
-> select v_path from tab_rec_copy where v_path not in (select v_path from tab_bak)
-> ) t2
-> where
-> left(substring_index(t2.v_path,'\\',-2),8) =
-> (
-> select max(left(substring_index(t1.v_path,'\\',-2),8))
-> from
-> (
-> select v_path from tab_rec where v_path not in
-> (select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091110'
-> union all
-> select v_path from tab_rec_copy where v_path not in
-> (select v_path from tab_bak) and left(substring_index(v_path,'\\',-2),8)<'20091110'
-> ) t1
-> );
+-------------------+
| v_path |
+-------------------+
| d:\temp\20091109\ |
+-------------------+
1 row in set (0.00 sec)mysql>
a3.NEWRQ->a3.v_pathSELECT * FROM (
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') AS NEWRQ FROM tab_rec
UNION ALL
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') FROM tab_rec_copy) a3
INNER JOIN
(SELECT ma FROM (
SELECT MAX(v_path) AS ma FROM (
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') AS NEWRQ FROM tab_rec
UNION ALL
SELECT v_path,REPLACE(SUBSTRING_INDEX(v_path,'\\',-2),'\\','') FROM tab_rec_copy) a
WHERE NEWRQ <'20091110') a1
LEFT JOIN
tab_bak a2
ON a1.ma=a2.v_path WHERE a2.v_path IS NULL) a4
ON a3.v_path=a4.ma;
(
select v_path from tab_rec
union all
select v_path from tab_rec_copy
) a
where SUBSTRING_INDEX(v_path,'\\',-2)=(
select max(max_v_path)
from (
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec
where v_path not in (select v_path from tab_bak)
and SUBSTRING_INDEX(v_path,'\\',-2)<'20091112\\'
union all
select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec_copy
where v_path not in (select v_path from tab_bak)
and SUBSTRING_INDEX(v_path,'\\',-2)<'20091112\\'
) t
)mysql> select v_path from
-> (
-> select v_path from tab_rec
-> union all
-> select v_path from tab_rec_copy
-> ) a
-> where SUBSTRING_INDEX(v_path,'\\',-2)=(
-> select max(max_v_path)
-> from (
-> select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec
-> where v_path not in (select v_path from tab_bak)
-> and SUBSTRING_INDEX(v_path,'\\',-2)<'20091112\\'
-> union all
-> select max(SUBSTRING_INDEX(v_path,'\\',-2)) as max_v_path from tab_rec_copy
-> where v_path not in (select v_path from tab_bak)
-> and SUBSTRING_INDEX(v_path,'\\',-2)<'20091112\\'
-> ) t
-> );
+-------------------+
| v_path |
+-------------------+
| d:\temp\20091110\ |
| d:\temp\20091110\ |
| d:\temp\20091110\ |
+-------------------+
3 rows in set (0.00 sec)mysql>
-> from (
-> select v_path from tab_rec
-> union all
-> select v_path from tab_rec_copy
-> ) t
-> where v_path<concat('d:\\temp\\',20091112,'\\')
-> order by v_path desc
-> limit 1;
+-------------------+
| v_path |
+-------------------+
| d:\temp\20091111\ |
+-------------------+
1 row in set (0.00 sec)mysql>
表tab_rec_copy : v_path
表tab_bak : v_path , 怎样从tab_rec和tab_rec_copy两表中找到离当前日期最近的v_path,且该v_path值不存在于表tab_bak中