表中的不同行 [英] distinct rows in table

问题描述

嗨！
我想加入三个表.我想在选择数据时获得不同的值.
Scenerio就像我有一个名为property，resident和images的表，property表具有propertyID作为主键，images和residential具有propertyID作为外键.images表具有具有相同propertyID的多行.现在我想选择唯一的行从属性表中具有唯一的proppertyID的同时连接这三个表，但问题是我没有获得唯一的行...这是查询...请告诉我是什么问题...

hi!
i want to join three tables.and i want to get distinct values while selecting the data.
scenerio is like " i have tables named property, residential and images, and property table has propertyID as a primary key and images and residential has propertyID as foreign key.and images table has multiple rows with same propertyID.now i want to select unique rows from property table with a unique proppertyID while joining these three tables but the problem is that i am not getting unique rows... here is the query...please tell me what is the problem ...

SELECT DISTINCT p.PropertyID, p.AgentID, p.UserID, p.Title, p.PropertyType, 
                p.Location, p.Address, p.City, p.Country, p.TotalArea, 
                p.CoveredArea, p.BuiltAfter, p.for_sale_rent, p.Status, 
                p.Description, p.Price, p.Latitude, p.Longitude, 
                p.CornerPosition, p.KeyWord, 
                r.Res_ID, r.PropertyID AS Expr1, r.HomeType, r.Rooms, 
                r.DrawingDining, r.Garages, r.Kitchen, r.Bath, r.Lounge, 
                r.Furnished, r.Basemant, r.serventQuarter, r.Stories, 
                i.ImageName AS Expr2
FROM Property AS p 
    LEFT OUTER JOIN Residential AS r ON p.PropertyID = r.PropertyID 
        LEFT OUTER JOIN Images AS i ON p.PropertyID = i.PropertyID

推荐答案

对于SqlServer2005及更高版本，请看一下 SQL Server交叉应用和外部应用 [ ^ ]

因此查询将是:

For SqlServer2005 and up take a look SQL Server CROSS APPLY and OUTER APPLY[^]

So the query will be:

SELECT DISTINCT p.PropertyID, p.AgentID, p.UserID, p.Title, p.PropertyType, 
                p.Location, p.Address, p.City, p.Country, p.TotalArea, 
                p.CoveredArea, p.BuiltAfter, p.for_sale_rent, p.Status, 
                p.Description, p.Price, p.Latitude, p.Longitude, 
                p.CornerPosition, p.KeyWord, 
                r.Res_ID, r.PropertyID AS Expr1, r.HomeType, r.Rooms, 
                r.DrawingDining, r.Garages, r.Kitchen, r.Bath, r.Lounge, 
                r.Furnished, r.Basemant, r.serventQuarter, r.Stories, 
                i.ImageName AS Expr2
FROM Property AS p 
OUTER
APPLY (
      SELECT TOP 1
             *
      FROM   Residential
      WHERE  PropertyID = p.PropertyID
      ORDER
      BY     Res_ID --- change this for your criteria
      ) AS r
OUTER
APPLY (
      SELECT TOP 1
             ImageName 
      FROM   Images
      WHERE  PropertyID = P.PropertyID
      ORDER
      BY     ImageName --- change this for your criteria
      ) AS i

由于您要返回Images.ImageName并且有多个图像记录，DISTINCT仍会给出你们所有的行.

这取决于您要执行的操作.是否要获取每张图像?只有一个?您是否要为至少包含一张图片的属性获取一个属性行?有技巧，但是从此查询中了解您想要的内容将很有帮助.

下面的解决方案2似乎很可行，但是您也可以通过更改联接来实现相同的目的:

从此:

Because you are returning Images.ImageName and there are multiple image records, DISTINCT will still give you all the rows.

It depends on what you are trying to do. Do you want to get every one of the images? Just one? Are you trying to get one property row for properties that have at least one image? There are techniques, but it would be helpful to know what you want from this query.

Solution 2 below seems like the way to go, but you could also achieve the same thing by changing the join like this:

From this:

LEFT OUTER JOIN Images AS i ON p.PropertyID = i.PropertyID

为此:

To this:

LEFT OUTER JOIN (SELECT TOP 1 ImageName FROM Images WHERE PropertyId = p.PropertyId ORDER BY id) AS i ON p.PropertyID = i.PropertyID

抱歉，我的解决方案很糟糕.这是一种可行的方法.

摆脱LEFT OUTER JOIN Images并在SELECT子句中更改i.ImageName AS Expr2
对此:

My apologies, my solution was crap. Here is a way that works.

Get rid of the LEFT OUTER JOIN Images and change the i.ImageName AS Expr2 in the SELECT clause
to this:

Expr2 = (SELECT TOP 1 ImageName FROM Images WHERE PropertyId = p.PropertyId ORDER BY id)
from vehicle V

我假设您的图片"表格就是创建多行的表格.

您将需要将图像"表连接到自身，其中第二个实例标识要显示的照片.例如，假设您需要该物业的最新"照片.在这种情况下，您的图片表中必须有一个日期，这样您就可以执行以下操作:

I assume that your "Images" table is the one creating the multiple lines.

What you will need is to join that Images table to itself where the second instance of itself identifies the photo you want to display. For example, I will assume you want the "latest" photo of the property. In that case there must be a date in your Images table so you will do something like this:

... LEFT OUTER JOIN  Images i1 ON p.PropertyID = i1.PropertyID
    INNER JOIN
      (select max(t.imagedate) maximagedate, t.propertyid
      from images t
      group by t.propertyid) i2
  ON i1.propertyid = i2.propertyid and i1.imagedate = i2.maximagedate

这还假设您的照相日期在您的媒体资源ID中没有重复.但是您应该从我的工作中得到灵感.

祝你好运

This also assumes that your photo date is not duplicated among your property ids. But you should get the idea from what I am doing.

Good Luck

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