文章/答案/技术大牛

发布

社区首页 >问答首页 >在标识符之前选择10个，从标识符中选择10个

问在标识符之前选择10个，从标识符中选择10个
EN

Stack Overflow用户

提问于 2016-06-15 19:23:42

回答 3查看 120关注 0票数 2

您认为在下面的SQL语句可以以比显示更好的方式执行吗？

以下是要求：

查询需要TextIdentifier和用户名。
标识之前创建的10个MP3文件，以及之后创建的10个MP3文件。
如果在之前或之后存在少于10个MP3文件，则选择所有可用文件。
无法使用表中的自动编号ID，因为DateCreated不相关。

代码在图像下面

这里有一些潜在的场景：

查询：

 DECLARE @UserName NVARCHAR(255)
    DECLARE @TextIdentifier NVARCHAR(255)
    DECLARE @ProximityDate AS DATETIME
    SET @UserName = 'Pete'
    SET @TextIdentifier = '456'

    SELECT  @ProximityDate = [DateCreated]
    FROM    dbo.ProximitySelectTable
    WHERE   UserName = @UserName
            AND TextIdentifier = @TextIdentifier


    SELECT * FROM (

    SELECT TOP 10 *
    FROM    dbo.ProximitySelectTable
    WHERE   UserName = @UserName
            AND [DateCreated] < @ProximityDate
    UNION
    SELECT  *
    FROM    dbo.ProximitySelectTable
    WHERE   UserName = @UserName
            AND TextIdentifier = @TextIdentifier                      
    UNION
    SELECT TOP 10 *
    FROM    dbo.ProximitySelectTable
    WHERE   UserName = @UserName
            AND [DateCreated] > @ProximityDate        
    ) x
    ORDER BY x.DateCreated

表

CREATE TABLE [dbo].[ProximitySelectTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TextIdentifier] [nvarchar](255) NULL,
    [UserName] [nvarchar](255) NULL,
    [FileName] [nvarchar](255) NULL,
    [DateCreated] [datetime] NULL
) ON [PRIMARY]

测试数据

/****** Object:  Table [dbo].[ProximitySelectTable]    Script Date: 06/15/2016 15:15:09 ******/
SET IDENTITY_INSERT [dbo].[ProximitySelectTable] ON
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (1, N'2has721', N'Pete', N'c:\123456.mp3', CAST(0x0000A13900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (2, N'123', N'Pete', N'c:\223456.mp3', CAST(0x0000A58000000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (3, N'456', N'Pete', N'c:\323456.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (4, N'789', N'Pete', N'c:\423456.mp3', CAST(0x0000A58000000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (5, N'1011', N'Pete', N'c:\523456.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (6, N'1112', N'Jack', N'c:\623456.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (7, N'1312', N'Jack', N'c:\723456.mp3', CAST(0x0000A56C00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (8, N'1412', N'Jack', N'c:\823456.mp3', CAST(0x0000A54300000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (9, N'1516', N'Jack', N'c:\923456.mp3', CAST(0x0000A58000000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (10, N'1817', N'Jack', N'c:\133456.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (11, N'1819', N'Sherry', N'c:\143456.mp3', CAST(0x0000A52400000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (12, N'2021', N'Sherry', N'c:\153456.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (13, N'2122', N'Sherry', N'c:\163456.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (14, N'2123', N'Pete', N'c:\173456.mp3', CAST(0x0000A52400000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (15, N'2124', N'Pete', N'c:\183456.mp3', CAST(0x0000A13900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (16, N'2125', N'Sherry', N'c:\193456.mp3', CAST(0x0000A50600000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (17, N'2126', N'Jack', N'c:\124456.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (18, N'2127', N'Pete', N'c:\125456.mp3', CAST(0x0000A13900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (19, N'2128', N'Sherry', N'c:\aaaa1.mp3', CAST(0x0000A4E700000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (20, N'2129', N'Jack', N'c:\aaaa2.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (21, N'3121', N'Jack', N'c:\aaaa3.mp3', CAST(0x0000A13900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (22, N'3122', N'Sherry', N'c:\aaaa4.mp3', CAST(0x0000A54300000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (23, N'3123', N'Jack', N'c:\aaaa5.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (24, N'3124', N'Jack', N'c:\aaaa6.mp3', CAST(0x0000A13900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (25, N'3125', N'Sherry', N'c:\aaaa7.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (26, N'3126', N'Jack', N'c:\aaaa8.mp3', CAST(0x0000A56C00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (27, N'3127', N'Pete', N'c:\aaaa9.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (28, N'3128', N'Pete', N'c:\bbbbb1.mp3', CAST(0x0000A54300000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (29, N'3129', N'Sherry', N'c:\bbbbb2.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (30, N'4121', N'Jack', N'c:\bbbbb3.mp3', CAST(0x0000A52400000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (31, N'4122', N'Pete', N'c:\bbbbb4.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (32, N'4123', N'Jack', N'c:\bbbbb5.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (33, N'4124', N'Jack', N'c:\bbbbb6.mp3', CAST(0x0000A50600000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (34, N'4125', N'Sherry', N'c:\bbbbb7.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (35, N'4126', N'Jack', N'c:\bbbbb8.mp3', CAST(0x0000A56C00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (36, N'4127', N'Jack', N'c:\bbbbb9.mp3', CAST(0x0000A4E700000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (37, N'4128', N'Jack', N'c:\cccccc1.mp3', CAST(0x0000A54300000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (38, N'4129', N'Pete', N'c:\cccccc2.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (39, N'5121', N'Sherry', N'c:\cccccc3.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (40, N'5122', N'Jack', N'c:\cccccc4.mp3', CAST(0x0000A56C00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (41, N'5123', N'Jack', N'c:\cccccc5.mp3', CAST(0x0000A54300000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (42, N'5124', N'Sherry', N'c:\cccccc6.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (43, N'5125', N'Pete', N'c:\cccccc7.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (44, N'5126', N'Jack', N'c:\cccccc8.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (45, N'5127', N'Pete', N'c:\cccccc9.mp3', CAST(0x0000A4E700000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (46, N'5128', N'Pete', N'c:\ddddd1.mp3', CAST(0x0000A54300000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (47, N'5129', N'Pete', N'c:\ddddd2.mp3', CAST(0x0000A4E700000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (48, N'6121', N'Pete', N'c:\ddddd3.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (49, N'6122', N'Pete', N'c:\ddddd4.mp3', CAST(0x0000A50600000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (50, N'6123', N'Pete', N'c:\ddddd5.mp3', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (51, N'6124', N'Pete', N'c:\ddddd6.mp3', CAST(0x0000A52400000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (52, N'6125', N'Pete', N'c:\ddddd7.mp3', CAST(0x0000A5F900000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (53, N'6126', N'Pete', N'c:\ddddd8.mp3', CAST(0x0000A50600000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (54, N'6127', N'Pete', N'c:\ddddd9.mp3', CAST(0x0000A5DB00000000 AS DateTime))
INSERT [dbo].[ProximitySelectTable] ([ID], [TextIdentifier], [UserName], [FileName], [DateCreated]) VALUES (55, N'6128', N'Jack', N'c:\ddddd0.mp3', CAST(0x0000A5F900000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[ProximitySelectTable] OFF

sql-server

tsql

sql-server-2008-r2

sql

回答 3

Stack Overflow用户

回答已采纳

发布于 2016-06-15 19:46:23

当前的查询是不正确的(缺少ORDER BY和不需要的UNION而不是UNION ALL)，但是使用正确的索引，固定版本的查询可能是最有效的。

我的row_number答案首先是

WITH CTE1
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY DateCreated) AS RN
         FROM   [ProximitySelectTable]
         WHERE  UserName = @UserName),
     CTE2
     AS (SELECT *,
                MAX(CASE
                      WHEN @TextIdentifier = TextIdentifier
                        THEN RN
                    END) OVER () AS FocusRN
         FROM   CTE1)
SELECT ID,
       TextIdentifier,
       UserName,
       FileName,
       DateCreated
FROM   CTE2
WHERE  RN BETWEEN FocusRN - 10 AND FocusRN + 10
ORDER  BY DateCreated

和当前查询的固定版本。

SELECT * FROM (
SELECT TOP 10 *
FROM    dbo.ProximitySelectTable
WHERE   UserName = @UserName
        AND [DateCreated] < @ProximityDate
ORDER BY [DateCreated] DESC 
UNION ALL
SELECT  *
FROM    dbo.ProximitySelectTable
WHERE   UserName = @UserName
        AND TextIdentifier = @TextIdentifier                      
UNION ALL
SELECT TOP 10 *
FROM    dbo.ProximitySelectTable
WHERE   UserName = @UserName
        AND [DateCreated] > @ProximityDate  
ORDER BY [DateCreated] ASC            
) x
ORDER BY x.DateCreated

这将需要在UserName, TextIdentifier上为初始搜索提供一个索引，而在UserName, DateCreated上则需要一个索引来获得TOP 10的搜索，而不是任何类型的索引。

票数 2

Stack Overflow用户

发布于 2016-06-15 19:43:38

手头没有运行Server 2008的框，但我检查了以下所有功能在2008年可用：

DECLARE @UserName       NVARCHAR(255) = 'Pete'
DECLARE @TextIdentifier NVARCHAR(255) = '456'

;WITH
    cte1 AS
    (
        SELECT      *,
                    ROW_NUMBER() OVER (ORDER BY DateCreated) AS RowNumber
        FROM        ProximitySelectTable
    ),
    cte2 AS
    (
        SELECT TOP 1
                    RowNumber
        FROM        cte1
        WHERE       UserName = @UserName AND TextIdentifier = @TextIdentifier
        ORDER BY    RowNumber
    )


SELECT      *
FROM        cte1, cte2
WHERE       cte1.RowNumber BETWEEN (cte2.RowNumber - 10) AND (cte2.RowNumber + 10)

cte1编号从1到n的所有行。cte2查找目标行的行号。最后的SELECT只需扫描过去的10行和接下来的10行。

票数 1

Stack Overflow用户

发布于 2016-06-15 19:44:25

下面是ROW_NUMBER解决方案的伪代码：

WITH cte1 AS (
SELECT desiredcolumns, ROW_NUMBER() OVER (ORDER BY DateCol) AS rn
FROM MyTable
),
cte2 AS (
SELECT ID, rn
FROM cte1
WHERE TextIdentifier = @TextIdentifier 
)
SELECT desiredcolumns
FROM cte1
CROSS JOIN cte2
WHERE cte1.rn > cte2.rn - 10
AND cte1.rn < cte2.rn + 9

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/37844096

复制

相似问题

问在标识符之前选择10个，从标识符中选择10个
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在标识符之前选择10个，从标识符中选择10个EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在标识符之前选择10个，从标识符中选择10个
EN