SQL查询删除与两个不同数据库内部联接的3个表中的重复值

Question

我正在使用SQL Server数据库，并且编写了以下查询：

SELECT 
    "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, "AM-Martin".dbo.CpCore_Site.Number
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
INNER JOIN 
    "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId
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;

查询返回的是我需要的数据，但它正在复制某些数据。当我尝试添加Distinct以消除重复的数据时…

SELECT DISTINCT 
    "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, "AM-Martin".dbo.CpCore_Site.Number
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
INNER JOIN 
    "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId
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;

由于此列，它不允许我使用它，"AM-Martin_bin".dbo.CpCore_Image.Bytes此列是实际的图像数据。我收到的错误消息是：

错误：无法将图像数据类型选择为DISTINCT，因为它不可比较。错误代码：421

我试图摆脱所有重复的数据。要访问数据库，我使用RazorSQL，而远程数据库是使用SQL Server的Microsoft Server 2008。

更新

我使用了GROUP BY，它看起来像这样：

SELECT 
    "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, "AM-Martin".dbo.CpCore_Site.Number
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
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId

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.Name;

现在我收到以下错误：

错误：“ AM-Martin.dbo.CpCore_Site.Latitude”列在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP
BY子句中。错误代码：8120

现在，如果我向该组添加更多列，它会继续对下一列说同样的话，直到我得到"AM- Martin_bin".dbo.CpCore_Image.Bytes以下错误消息：

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

Answer 1

我想通了，这要归功于group
by子句不能与Image一起工作，
我所要做的就是像这样通过将其CASTING到VARBINARY来添加更改图像列的文字：

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,**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**, "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
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId

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,**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**, "AM-Martin".dbo.CpCore_Site.Name, "AM-Martin".dbo.CpCore_Site.Zipcode;

我也必须赞扬@Myonara，以帮助我在小组的建议下走上正确的道路。