select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
union
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
其他情况,query1&2中code都是字符型,255位。取query1.code=‘35642’有一条记录;取query2.code=‘35642’有一条记录;取query1.code=‘35642CD’无记录。
9 个解决方案
#1
单独运行下页句,有无结果
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
#2
估计是没有符合条件的记录。
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
#3
数据样例如下: query1.code query2.code
35642 null
1125 null
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
没有返回记录,这里我预想是有一个c.*+a.null的返回啊!
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
就有一条记录返回,返回记录是c.*+a.null。
(另外单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642' or a.code is null)
有一条记录返回,返回c.*+a.*。)
难道是说access2007认为:既然c.code=left(a.code,5)成立,那么where中的条件a.code='35642CD'必须成立?
但是问题又来了,和上面一样的条件下:select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='1125' and (a.code='1125CD' or a.code is null)就有返回c.*+a.null。
35642 null
1125 null
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
没有返回记录,这里我预想是有一个c.*+a.null的返回啊!
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
就有一条记录返回,返回记录是c.*+a.null。
(另外单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642' or a.code is null)
有一条记录返回,返回c.*+a.*。)
难道是说access2007认为:既然c.code=left(a.code,5)成立,那么where中的条件a.code='35642CD'必须成立?
但是问题又来了,和上面一样的条件下:select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='1125' and (a.code='1125CD' or a.code is null)就有返回c.*+a.null。
#4
对不起,数据样例应该如下
query1.code query2.code
35642 35642
1125 1125
…… 1125ERS
35642ERS
query1.code query2.code
35642 35642
1125 1125
…… 1125ERS
35642ERS
#5
如果可以的话,
上传你的MDB(2000格式)到www.access911.net/csdn
只要有问题的表、查询、 报表,用WINRAR压缩
上传你的MDB(2000格式)到www.access911.net/csdn
只要有问题的表、查询、 报表,用WINRAR压缩
#6
谢谢,已经上传到:
http://access911.net/csdn/FileDescription.asp?mdb=2013-3-26&id=22
problem query就是疑问查询;
ref query2和ref query3作参考。
http://access911.net/csdn/FileDescription.asp?mdb=2013-3-26&id=22
problem query就是疑问查询;
ref query2和ref query3作参考。
#7
我觉得问题是不是在(a.code='35642CD' or a.code is null)这一段上:a.code='35642CD'不满足,而因为'35642'的存在a.code is null也不满足……
该怎么办呢?
该怎么办呢?
#8
problem query:
a.gst_company_code='35642CD' or a.gst_company_code is null;
gst_company_code:35642、35642ERS
2个条件均不满足,返回FALSE
a.gst_company_code='35642CD' or a.gst_company_code is null;
gst_company_code:35642、35642ERS
2个条件均不满足,返回FALSE
#9
解决了!
期待返回值为仅一条c表对应'35642'的记录,加上a表中包含'35642'记录;或者至少c表对应'35642'的记录加上a表为空。考虑到'35642CD','35642'和null三种情况都有可能存在,'35642CD'最优先,找不到时使用'35642',最后还是找不到时使用null,只需要一条数据返回,则查询如下:
select top 1 1 as RowNum,c.*,a.* from query1 c left join (select top 1 * from query2 where gcode='35642CD' or code='35642' order by code desc) a on c.code=left(a.code,5) where c.code='35642'
不好意思,一开始思路偏了,没有全盘考虑数据条件。
期待返回值为仅一条c表对应'35642'的记录,加上a表中包含'35642'记录;或者至少c表对应'35642'的记录加上a表为空。考虑到'35642CD','35642'和null三种情况都有可能存在,'35642CD'最优先,找不到时使用'35642',最后还是找不到时使用null,只需要一条数据返回,则查询如下:
select top 1 1 as RowNum,c.*,a.* from query1 c left join (select top 1 * from query2 where gcode='35642CD' or code='35642' order by code desc) a on c.code=left(a.code,5) where c.code='35642'
不好意思,一开始思路偏了,没有全盘考虑数据条件。
#1
单独运行下页句,有无结果
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
#2
估计是没有符合条件的记录。
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
#3
数据样例如下: query1.code query2.code
35642 null
1125 null
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
没有返回记录,这里我预想是有一个c.*+a.null的返回啊!
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
就有一条记录返回,返回记录是c.*+a.null。
(另外单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642' or a.code is null)
有一条记录返回,返回c.*+a.*。)
难道是说access2007认为:既然c.code=left(a.code,5)成立,那么where中的条件a.code='35642CD'必须成立?
但是问题又来了,和上面一样的条件下:select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='1125' and (a.code='1125CD' or a.code is null)就有返回c.*+a.null。
35642 null
1125 null
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642CD' or a.code is null)
没有返回记录,这里我预想是有一个c.*+a.null的返回啊!
单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='35642' and (a.code='35642CD' or a.code is null)
就有一条记录返回,返回记录是c.*+a.null。
(另外单独运行:
select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,5) where c.code='35642' and (a.code='35642' or a.code is null)
有一条记录返回,返回c.*+a.*。)
难道是说access2007认为:既然c.code=left(a.code,5)成立,那么where中的条件a.code='35642CD'必须成立?
但是问题又来了,和上面一样的条件下:select 1,c.*,a.* from query1 c left join query2 a on c.code=left(a.code,4) where c.code='1125' and (a.code='1125CD' or a.code is null)就有返回c.*+a.null。
#4
对不起,数据样例应该如下
query1.code query2.code
35642 35642
1125 1125
…… 1125ERS
35642ERS
query1.code query2.code
35642 35642
1125 1125
…… 1125ERS
35642ERS
#5
如果可以的话,
上传你的MDB(2000格式)到www.access911.net/csdn
只要有问题的表、查询、 报表,用WINRAR压缩
上传你的MDB(2000格式)到www.access911.net/csdn
只要有问题的表、查询、 报表,用WINRAR压缩
#6
谢谢,已经上传到:
http://access911.net/csdn/FileDescription.asp?mdb=2013-3-26&id=22
problem query就是疑问查询;
ref query2和ref query3作参考。
http://access911.net/csdn/FileDescription.asp?mdb=2013-3-26&id=22
problem query就是疑问查询;
ref query2和ref query3作参考。
#7
我觉得问题是不是在(a.code='35642CD' or a.code is null)这一段上:a.code='35642CD'不满足,而因为'35642'的存在a.code is null也不满足……
该怎么办呢?
该怎么办呢?
#8
problem query:
a.gst_company_code='35642CD' or a.gst_company_code is null;
gst_company_code:35642、35642ERS
2个条件均不满足,返回FALSE
a.gst_company_code='35642CD' or a.gst_company_code is null;
gst_company_code:35642、35642ERS
2个条件均不满足,返回FALSE
#9
解决了!
期待返回值为仅一条c表对应'35642'的记录,加上a表中包含'35642'记录;或者至少c表对应'35642'的记录加上a表为空。考虑到'35642CD','35642'和null三种情况都有可能存在,'35642CD'最优先,找不到时使用'35642',最后还是找不到时使用null,只需要一条数据返回,则查询如下:
select top 1 1 as RowNum,c.*,a.* from query1 c left join (select top 1 * from query2 where gcode='35642CD' or code='35642' order by code desc) a on c.code=left(a.code,5) where c.code='35642'
不好意思,一开始思路偏了,没有全盘考虑数据条件。
期待返回值为仅一条c表对应'35642'的记录,加上a表中包含'35642'记录;或者至少c表对应'35642'的记录加上a表为空。考虑到'35642CD','35642'和null三种情况都有可能存在,'35642CD'最优先,找不到时使用'35642',最后还是找不到时使用null,只需要一条数据返回,则查询如下:
select top 1 1 as RowNum,c.*,a.* from query1 c left join (select top 1 * from query2 where gcode='35642CD' or code='35642' order by code desc) a on c.code=left(a.code,5) where c.code='35642'
不好意思,一开始思路偏了,没有全盘考虑数据条件。