[SQLServer2008R2]select * From (select * from table) 嵌套查询问题

问题来源：
   1、用户需要一个字段来表示时间，但是时间date字段(字段名：sj)内可输入2013xxxx这种非数字字符的文本信息；
   2、（带来的问题）查询时如何查询？不能用传统的date或者int类型来查询，必须先处理在查询，这就带来了我的嵌套查询问题。
             
我解决用户问题的思路：
 1、数据库字段使用varchar；
 2、在数据库中使用嵌套查询来解决

select tmp.* From (select * From table_A Where PATINDEX('[0-9]%[0-9]',sj)=1 )as tmp )
Where Convert(int,tmp.sj) > 20130101

我的报错情况：
  1、报 tmp.sj 中存在‘2013xxxx’不能将其转换为int类型；
2、查看sqlserver2008R2环境中的数据查询结果窗口，我需要的数据出来了；（虽然结果可以出来，但是我通过VS.net2008写的程序不能得到结果，而且有报错信息，个人觉得不爽，也没觉得自己写错。）
请大家帮忙看看。谢谢

18 个解决方案

#1

改成这样试试呢:

select tmp.* From (select * From table_A Where PATINDEX('[0-9]%[0-9]',sj)=1 and isnumeric(sj)=1 )as tmp )
Where Convert(int,tmp.sj) > 20130101

#2

1.在查询将字符串转换为日期型值以日期型值来比较;
2.在客户端要预先检查用户输入的内容是否能转换.

#3

引用 1 楼 yupeigu 的回复:

改成这样试试呢:

select tmp.* From (select * From table_A Where PATINDEX('[0-9]%[0-9]',sj)=1 and isnumeric(sj)=1 )as tmp )
Where Convert(int,tmp.sj) > 20130101

我明天试试，刚回家了，
但是个人感觉没有用，i因为报的错是在主查询中有2013xxxx这种记录存在，我用子查询的目的就是要除掉2013xxxx这种记录，但是不知道怎么还是有2013xxxx这种记录集出现在主查询的虚拟表中

#4

引用 2 楼 qianjin036a 的回复:

1.在查询将字符串转换为日期型值以日期型值来比较;
2.在客户端要预先检查用户输入的内容是否能转换.

1、转换成日期也不行，也会报格式不对，因为有2013xxxx这种格式存在；
2、你看完我的问题描述就知道了，用户要求有2013xxxx这种描述日期时间的字符存在，处理的时候可以不管它，但是这种形式必须保留，因为这种格式对用户的业务应用也是有意义的，不能在客户端随便转换成我们认为的合法格式。

#5

先用isdate()函数判断列是否复合日期型，如何的才进行操作。另外最好还是改一下设计或者约束，保持 数据列的数据一致性

#6

引用 5 楼 DBA_Huangzj 的回复:

先用isdate()函数判断列是否复合日期型，如何的才进行操作。另外最好还是改一下设计或者约束，保持 数据列的数据一致性

1 用isdate()还是会出现相同的问题，报2013xxxx转换日期格式失败，isdate()函数和我写的PATINDEX或者isnumeric()是一个道理，关键点不在于此，关键是主查询进行Where Convert(int,tmp.sj) > 20130101 判断时出现了被剔除掉的数据（例如含有2013xxxx数据的记录集）。
所以我不是很明白，为什么我在子查询中剔除掉的记录会跑到主查询的where判断语句中，按照select from where的执行顺序，应该先是找from，再找where，最后是select，而我主查询的from是来自于子查询，按理说主查询的from数据源就只能是子查询后得到结果集，为什么我现在这个主查询中的where里有子查询被剔除的数据。不明白~~
2、不能更改设计，只能是varchar字符形式来保存。因为用户要求这个字段是用来放时间的字段，而且必须允许2013xxxx这种数据形式的存在，同时用户的老数据也是用varchar来保存数据，老数据里面有很多2013xxxx类似的非标准时间格式，用户不关心你怎么保存，只关心这些与时间有关的字符必须保留，也不关心你怎么处理。所以从用户需求角度来讲，这些数据就是字符或者是文本，重要的是这个字符本身的意义，重点不是进行时间的处理，那么，我认为从数据角度看，设计成varchar是比较合理的，而且我也认为通过相关数据处理，可以把不符合要求的数据形式过滤掉。
问题是，按照我现在的嵌套处理方法，子查询中被滤掉的数据集为什么会跑到主查询where中来，不明白啊！

#7

我也不清楚为什么，昨天也遇到类似的，我在子查询也筛选了，而且只对子查询操作，但是也报错，那些剔除的值的确又出现了，只能说我对T-SQL执行的理解还没到家，但是你这个说存数据是合理的话我就不赞同了，用varchar没问题，但是存进去的时候应该进行规则的判断，统一类型，不然就算你在上面建索引，也一样无效

#8

但是我现在不能设计成datetime，只能是varchar，用户端验证我也可以做一些工作，让我的新数据尽量规则，但我不能把以前用户的老数据改变。
所以我还是要面对2013xxx这种格式（或其他部分不规则格式）数据的问题

#9

猜测：如果 Convert(int,tmp.sj) 报错，那应该SJ这一列中，不只是2013xxxx这种格式，应该还有其他格式的，如带“/”或是“-”，这在日期格式输入时也会有可能的。当然，也不排除会有“:”这个字符存在的可能。
所以，先用select * from table_A where sj like '%-%' or sj like '%/%' or sj like '%:%' or len(sj) <> 8查一下，看看是否有其他格式的存在。
如果有其他格式的存在，替换掉这些字符后再转成int。

#10

20130101-->
'20130101'

#11

还真是会出现转换2013xxxx失败的错误，真奇怪，2008是这样2000也是这样
我想只能这样转换一下了：

select * from(select case when isdate(sj)>0 then sj else '19000101' end as sj1,* from #tb where ISDATE(sj)>0)tmp
where CONVERT(int,sj1)>20130301

#12

怎么办啊，大哥们，提出一个有用的解决方法吧。
我已经发动同事们再弄了，都不行啊

#13

引用 11 楼 sdhp 的回复:

还真是会出现转换2013xxxx失败的错误，真奇怪，2008是这样2000也是这样
我想只能这样转换一下了：

select * from(select case when isdate(sj)>0 then sj else '19000101' end as sj1,* from #tb where ISDATE(sj)>0)tmp
where CONVERT(int,sj1)>20130301

为什么转成Int
而不是
转成日期
CONVERT(datetime,sj1)>'20130301'

#14

我先用sdhp的方法试试再说，先解决再说，至于为什么会出现上述我认为不应该出现的错误，再慢慢理解。

#15

引用 13 楼 wufeng4552 的回复:

Quote: 引用 11 楼 sdhp 的回复:

还真是会出现转换2013xxxx失败的错误，真奇怪，2008是这样2000也是这样
我想只能这样转换一下了：

select * from(select case when isdate(sj)>0 then sj else '19000101' end as sj1,* from #tb where ISDATE(sj)>0)tmp
where CONVERT(int,sj1)>20130301

为什么转成Int
而不是
转成日期
CONVERT(datetime,sj1)>'20130301'

报告，因为直接抄楼主的可以少打字

#16

我觉得在这个问题上SQLServer应该是把
select * from (select * from #tb where isdate(sj)>0)a
where CONVERT(date,sj)>'2013-03-01'
这个语句优化成了
select * from #tb
where isdate(sj)>0 and CONVERT(date,sj)>'2013-03-01'
然后convert和isdate又是优先计算convert的（这是什么原理？）

猜测是这样，仅供娱乐……

#17

可能是优化了，有道理。
sdhp你的方法没问题，分给你了。十分感谢。

#18

有空的话，大家一起琢磨一下SqlServer产生上述结果的原因，是否是上述原因。