MSSQL查询不同行的IN语句 [英] MSSQL query IN statement for distinct rows

问题描述

好吧，所以我遇到的问题是选择一个不同的属性下面的查询。我理解背后的原因，为什么我不能（rownum是独一无二的），但我在一个解决方案后，将允许我保持我的SQL分页，并拉我的数据库只有不同的属性。我目前使用一对多的数据库关系，还保存结果所需的过滤。我有兴趣听的是如果有一个更好的方法，而不是使用IN我的过滤。 Cheers guys

Okay so the problem I'm having is selecting a distinct property on the query below. I understand the reasoning behind why I can't (rownum is unique) but I'm after a solution that will allow me to keep my SQL paging and pull though only the distinct properties from my database. I'm currently using a one to many database relationships that also holds the filtering needed for the results. What I'd be interested in hearing is if there's a better way than using "IN" for my filtering. Cheers guys

CREATE PROCEDURE [dbo].[getActiveProperties]

@PAGENUM  AS INT,
@PERPAGE AS INT,
@SORTBY AS VARCHAR(50),
@FILTERBY AS VARCHAR(200)

AS

DECLARE @query as VARCHAR(2000)

SET @query = N'SELECT *
        FROM (SELECT
            ROW_NUMBER() OVER (ORDER BY ' + @SORTBY + ') AS [rownum],
            dbo.funcSellIdByPropId(T0.id) as SellerId,
            dbo.funcDefaultImage(T0.id, 1) as propImage,
            dbo.funcDefaultImage(T0.id, 2) as propImage2,
            dbo.funcDefaultImage(T0.id, 3) as propImage3,
            dbo.funcDefaultImage(T0.id, 4) as propImage4,
            dbo.funcCountPropertyImages(T0.id) as imageCount,
            dbo.funcGetPropertyTypeListGB(T0.id) as TypeGB,
            dbo.funcGetPropertyTypeListFR(T0.id) as TypeFR,
            dbo.funcGetPropertyEnviListGB(T0.id) as EnviGB,
            dbo.funcGetPropertyEnviListFR(T0.id) as EnviFR,
            dbo.funcGetDepartmentByTown(T0.Town) as Department,
            dbo.funcCheckFeaturedProperty(T0.id) as Featured,
            T0.id, T0.Price, T0.BedRooms, T0.Town, T0.Postcode, T0.Mandate, T0.MinLandArea, T0.Rooms, T0.HabitableSurface, T0.Active, T0.Budget,
            T1.TitleFR, T1.TitleGB, SUBSTRING(T1.DescFR, 0, 300) as DescFR, SUBSTRING(T1.DescGB, 0, 300) as DescGB
        FROM
            PROPERTIES T0
        INNER JOIN
            PROPERTYTRANSLATIONS T1
        ON
            T1.PropertyId = T0.id
        INNER JOIN
            MATRIXPROPENVIRONMENT T2
        ON
            T2.PropertyId = T0.id
        INNER JOIN
            ENVIRONMENT T3
        ON
            T3.id = T2.EnvironmentId
        WHERE
            T0.Deleted = 0 
        AND 
            T0.Active = 1 
        AND
            T3.GB IN (' + @FILTERBY + ')

        ) T
    WHERE
        rownum BETWEEN (' + CONVERT(varchar(4), @PERPAGE * @PAGENUM) + ') AND (' + CONVERT(varchar(4), @PERPAGE * (@PAGENUM + 1)) + ')'

EXEC (@query)

推荐答案

将rownum从子查询移到外部查询：

Easy. Move rownum from the subquery to the outer query:

SET @query = N'SELECT * FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY ' + @SORTBY + ') AS [rownum],
      *
        FROM (SELECT DISTINCT
            dbo.funcSellIdByPropId(T0.id) as SellerId,
            dbo.funcDefaultImage(T0.id, 1) as propImage,
            dbo.funcDefaultImage(T0.id, 2) as propImage2,
            dbo.funcDefaultImage(T0.id, 3) as propImage3,
            dbo.funcDefaultImage(T0.id, 4) as propImage4,
            dbo.funcCountPropertyImages(T0.id) as imageCount,
            dbo.funcGetPropertyTypeListGB(T0.id) as TypeGB,
            dbo.funcGetPropertyTypeListFR(T0.id) as TypeFR,
            dbo.funcGetPropertyEnviListGB(T0.id) as EnviGB,
            dbo.funcGetPropertyEnviListFR(T0.id) as EnviFR,
            dbo.funcGetDepartmentByTown(T0.Town) as Department,
            dbo.funcCheckFeaturedProperty(T0.id) as Featured,
            T0.id, T0.Price, T0.BedRooms, T0.Town, T0.Postcode, T0.Mandate, T0.MinLandArea, T0.Rooms, T0.HabitableSurface, T0.Active, T0.Budget,
            T1.TitleFR, T1.TitleGB, SUBSTRING(T1.DescFR, 0, 300) as DescFR, SUBSTRING(T1.DescGB, 0, 300) as DescGB
        FROM
            PROPERTIES T0
        INNER JOIN
            PROPERTYTRANSLATIONS T1
        ON
            T1.PropertyId = T0.id
        INNER JOIN
            MATRIXPROPENVIRONMENT T2
        ON
            T2.PropertyId = T0.id
        INNER JOIN
            ENVIRONMENT T3
        ON
            T3.id = T2.EnvironmentId
        WHERE
            T0.Deleted = 0 
        AND 
            T0.Active = 1 
        AND
            T3.GB IN (' + @FILTERBY + ')
        ) T ) TT
    WHERE
        rownum BETWEEN (' + CONVERT(varchar(4), @PERPAGE * @PAGENUM) + ') AND (' + CONVERT(varchar(4), @PERPAGE * (@PAGENUM + 1)) + ')'

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