BETWEEN子句中的子查询 [英] Subquery in BETWEEN clause

问题描述

大家好。

我想在子句中使用子查询但是我在SELECT中遇到错误。

  SELECT  TagNumber，ParkingType，DistanceToNextTag 
  FROM  MyCar 
 < span class =code-keyword> WHERE  Id  BETWEEN （ SELECT  Id  FROM  MyCar  WHERE  TagNumber = 6554） AND （ SELECT  Id  FROM  MyCar  WHERE  TagNumber = 65635）

解决方案

如果你想使用SubQuery你可以使用

  SELECT  TagNumber，ParkingType，DistanceToNextTag 
  FROM  MyCar 
  WHERE  Id 中的class =code-keyword>（选择  Distinct  Id 发件人 MyCar 其中 TagNumber  BETWEEN   6554  和  65635 ）

如果你想使用连接...

  SELECT  mcid.TagNumber，mcid .ParkingType，mcid.DistanceToNextTag 
  FROM  MyCar mcid 
 内部  join  MyCar mctg  on  mcid.Id = mctg.Id 
  WHERE  mctg.TagNumber  BETWEEN   6554   AND   65635  

I有可能对一个 TagNumber 有多个 Id 值吗？



您可能想尝试

 顶部  1  
 SQL>  BETWEEN  

SubQueries。

  SELECT  TagNumber，ParkingType，DistanceToNextTag 
  FROM  MyCar 
  WHERE  Id  BETWEEN （ SELECT   top   1  Id  FROM  MyCar  WHERE  TagNumber = 6554） AND （ SELECT   top   1  Id  FROM  MyCar  WHERE  TagNumber = 65635）

或者在子查询中添加另一个条件，以便它们只返回一条记录。



希望它有所帮助。



祝你好运



Azee ...

如果不知道确切的错误信息，就不可能说出问题所在。



但是，如果TagNumber是唯一的，您可以尝试备用版本以防万一。有一点是您没有在查询中为列定义表，而旧版本可能在其间存在标量问题。



所以尝试类似：

  SELECT  mc.TagNumber，
 mc.ParkingType，
 mc.DistanceToNextTag 
  FROM  MyCar mc 
  WHERE  mc.Id> =（ SELECT  mc2.Id  FROM  MyCar mc2  WHERE  mc2.TagNumber = 6554）
  AND  mc.Id< =（  SELECT  mc2.Id  FROM  MyCar mc2  WHERE  mc2.TagNumber = 65635）

Hi every one
I want to use subquery in between clause but I'm getting error in SELECT.

SELECT TagNumber,ParkingType,DistanceToNextTag
FROM MyCar
WHERE Id BETWEEN (SELECT Id FROM MyCar WHERE TagNumber=6554) AND (SELECT Id FROM MyCar WHERE TagNumber=65635)

解决方案

If you want to use SubQuery u can use

SELECT TagNumber,ParkingType,DistanceToNextTag
FROM MyCar
WHERE Id in (Select Distinct Id From MyCar Where TagNumber BETWEEN 6554 AND 65635)

If you want to use a join...

SELECT mcid.TagNumber,mcid.ParkingType,mcid.DistanceToNextTag
FROM MyCar mcid
Inner join MyCar mctg on mcid.Id = mctg.Id
WHERE mctg.TagNumber BETWEEN 6554 AND 65635

Is there a chance that there are multiple Id values against one TagNumber?

You might wanna try

Top 1

in your

BETWEEN

SubQueries.

SELECT TagNumber,ParkingType,DistanceToNextTag
FROM MyCar
WHERE Id BETWEEN (SELECT top 1 Id FROM MyCar WHERE TagNumber=6554) AND (SELECT top 1 Id FROM MyCar WHERE TagNumber=65635)

Or add another condition in sub queries so that they return only one record.

Hope it helps.

Good luck

Azee...

Without knowing the exact error message, it's quite impossible to say what the problem is.

However, if the TagNumber is unique, you could try an alternate version just in case. One point is that you haven't defined the tables for your columns in the query and older versions may have problems with scalars in between.

So try something like:

SELECT mc.TagNumber,
       mc.ParkingType,
       mc.DistanceToNextTag
FROM MyCar mc
WHERE mc.Id >= (SELECT mc2.Id FROM MyCar mc2 WHERE mc2.TagNumber=6554) 
AND   mc.Id <= (SELECT mc2.Id FROM MyCar mc2 WHERE mc2.TagNumber=65635)

这篇关于BETWEEN子句中的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！