将SQL查询拆分为嵌套查询（子查询） [英] Split SQL query into a nested query (subquery)

问题描述

我很难将以下内容转换为带有子查询的sql语句：

I am having difficulty converting the following into a sql statement with a subquery:

SELECT 
    "AM-Martin".dbo.CpCore_Site.Number
    , "AM-Martin".dbo.CpCore_Site.Latitude
    , "AM-Martin".dbo.CpCore_Site.Longitude
    , "AM-Martin".dbo.CpSm_Face.RetiredOn
    , "AM-Martin_bin".dbo.CpCore_Image.Bytes
    , "AM-Martin".dbo.CpCore_Site.Name
    , "AM-Martin".dbo.CpCore_Site.Zipcode
FROM
    "AM-Martin".dbo.CpCore_Site
    INNER JOIN "AM-Martin".dbo.CpSm_Face on "AM-Martin".dbo.CpSm_Face.SiteId = "AM-Martin".dbo.CpCore_Site.Oid
WHERE
    "AM-Martin".dbo.CpSm_Face.RetiredOn LIKE '%9999%'
     AND "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LA%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LC%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%BH%'
     AND "AM-Martin".dbo.CpCore_Site.Latitude > 0.0
GROUP BY 
    "AM-Martin".dbo.CpCore_Site.Number
    , "AM-Martin".dbo.CpCore_Site.Latitude
    , "AM-Martin".dbo.CpCore_Site.Longitude
    , "AM-Martin".dbo.CpSm_Face.RetiredOn
    , "AM-Martin_bin".dbo.CpCore_Image.Bytes
    , "AM-Martin".dbo.CpCore_Site.Name
    , "AM-Martin".dbo.CpCore_Site.Zipcode;

图像数据类型给我有关尝试使用GROUP BY的问题，我得到的错误是：

The image data type is giving me issues in regards to trying to use GROUP BY the error I get is:

错误：无法比较text，ntext和image数据类型或对
进行排序，除非使用IS NULL或像运算符。错误代码：306

ERROR: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. Error Code: 306

我想出了一个解决方案，请参见上一个问题使用SQL查询删除3个表内部联接中具有两个不同数据库的重复值，但是图像存在转换问题。

I figured out a solution see my previous question SQL Query to delete duplicate values in a 3 tables inner join with two different databases but I am having conversion issues with the images.

我的目标是编写一个子查询来获取主sql语句之外的图像，然后再加入图像。到目前为止，这就是我所拥有的：

My goal is to write a subquery to get the images outside of the main sql statement then join the images afterwards. So far this is what I have:

SELECT 
    "AM-Martin_bin".dbo.CpCore_Image.Bytes
    FROM
    (
SELECT 
    "AM-Martin".dbo.CpCore_Site.Number
    , "AM-Martin".dbo.CpCore_Site.Latitude
    , "AM-Martin".dbo.CpCore_Site.Longitude
    , "AM-Martin".dbo.CpSm_Face.RetiredOn
    , "AM-Martin".dbo.CpCore_Site.Name
    , "AM-Martin".dbo.CpCore_Site.Zipcode
FROM
    "AM-Martin".dbo.CpCore_Site
    INNER JOIN "AM-Martin".dbo.CpSm_Face on "AM-Martin".dbo.CpSm_Face.SiteId = "AM-Martin".dbo.CpCore_Site.Oid
WHERE
    "AM-Martin".dbo.CpSm_Face.RetiredOn LIKE '%9999%'
     AND "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LA%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LC%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%BH%'
     AND "AM-Martin".dbo.CpCore_Site.Latitude > 0.0
GROUP BY 
    "AM-Martin".dbo.CpCore_Site.Number
    , "AM-Martin".dbo.CpCore_Site.Latitude
    , "AM-Martin".dbo.CpCore_Site.Longitude
    , "AM-Martin".dbo.CpSm_Face.RetiredOn
    , "AM-Martin".dbo.CpCore_Site.Name
    , "AM-Martin".dbo.CpCore_Site.Zipcode
) AS a
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId;

运行此查询时遇到的错误是：

The error I am getting when I run this query is:

错误：可能没有绑定多部分标识符 AM-Martin.dbo.CpSm_Face.Oid。错误代码：4104

ERROR: The multi-part identifier "AM-Martin.dbo.CpSm_Face.Oid" could not be bound. Error Code: 4104

对获得此权利的任何帮助，将不胜感激。

Any help to get this right would be appreciated.

推荐答案

编辑：添加a

中的所有列我似乎无法再编辑我的评论，但这应该可以第二个查询的技巧：

I cannot seem to be able to edit my comment anymore, but this should do the trick for the second query:

SELECT 
    a.*
    , "AM-Martin_bin".dbo.CpCore_Image.Bytes
FROM
    (
SELECT DISTINCT
    "AM-Martin".dbo.CpCore_Site.Number
    , "AM-Martin".dbo.CpCore_Site.Latitude
    , "AM-Martin".dbo.CpCore_Site.Longitude
    , "AM-Martin".dbo.CpSm_Face.RetiredOn
    , "AM-Martin".dbo.CpCore_Site.Name
    , "AM-Martin".dbo.CpCore_Site.Zipcode
    , "AM-Martin".dbo.CpSm_Face.Oid
FROM
    "AM-Martin".dbo.CpCore_Site
    INNER JOIN "AM-Martin".dbo.CpSm_Face on "AM-Martin".dbo.CpSm_Face.SiteId = "AM-Martin".dbo.CpCore_Site.Oid
WHERE
    "AM-Martin".dbo.CpSm_Face.RetiredOn LIKE '%9999%'
     AND "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LA%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LC%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%BH%'
     AND "AM-Martin".dbo.CpCore_Site.Latitude > 0.0
) AS a
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image on a.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId;

请注意，我已将 GROUP BY 从内部查询，而是添加了DISTINCT来消除可能的重复项。但是，由于我不知道您的数据，因此我无法测试这是否行得通。

Note I removed the GROUP BY from the inner query, and instead added a DISTINCT to get rid of possible duplicates. However, I cannot test whether this will work, as I don't know your data.

出于可读性考虑，我建议您为表加别名，而不是重复 AM-MARTIN .dbo。< tablename> 部分随处可见（最初让我无法找到合适的列，等等）。

For readability's sake, I would recommend you to alias your tables, rather than repeating the "AM-MARTIN".dbo.<tablename> part everywhere (that initially threw me off with regards to finding the proper columns, etc.`).

例如：

SELECT 
    a.*
    , b.Bytes
FROM
    (
SELECT DISTINCT
    a_inner.Number
    , a_inner.Latitude
    , a_inner.Longitude
    , b_inner.RetiredOn
    , "a_inner.Name
    , a_inner.Zipcode
    , b_inner.Oid
FROM
    "AM-Martin".dbo.CpCore_Site a_inner
    INNER JOIN "AM-Martin".dbo.CpSm_Face b_inner on b_inner.SiteId = a_inner.Oid
WHERE
    b_inner.RetiredOn LIKE '%9999%'
     AND b_inner.Number LIKE N'%LA%' OR b_inner.Number LIKE N'%LC%' OR b_inner.Number LIKE N'%BH%'
     AND b_inner.Latitude > 0.0
) AS a
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image b on a.Oid = b.OwnerId;

请注意，我使用了 a_inner 和<$子查询的c $ c> b_inner 别名。但是，您也可以使用 a 和 b 。我非常想确保您看到差异，而不是想知道这些列的来源。

Note that I used a_inner and b_inner aliases for the subquery. However, you could just as well have used a and b. I mrerely wanted to ensure you'd see the difference, rather than wondering where the columns were coming from.

这篇关于将SQL查询拆分为嵌套查询（子查询）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！