从表中获取特定记录的问题 [英] problem in fetching specific records from table

问题描述

我从特定的表中获取前10条记录。在我的应用程序中，如果用户想要获取某些特定记录，那么我也希望输出该特定记录，但只应获取包含该特定记录的10条记录。



例如。假设我想要2个不在前10名的记录，但是2个记录也包括前8个，所以总数将是10.

Hi,
I am fetching top 10 records from particular table. In my application if user want some specific record to be fetched so I want output that specific record also but only 10 record should be fetched including that specific.

Eg. suppose I want 2 records which are not in top 10 but that 2 records also come including top 8 so total will be 10.

推荐答案

试试这个想法，希望它有所帮助你。

首先将数据加载到DataTable_1中。 （前10名）

然后用户详细信息到DataTable_2。 （用户所需数据）

现在使用union合并DataTable_1和DataTable_2。

然后选择Top 10.

确保已加载DataTable_2记录首先。

这很简单。

Try this idea, Hope it helps you.
First load the data into DataTable_1. (Top 10)
Then User details into DataTable_2. (User Required Data)
Now merge the DataTable_1 and DataTable_2 using union.
Then Select Top 10.
Make sure that DataTable_2 records are loaded first.
It is simple.

我不确定你为什么要获取用户未请求的记录，但这是你的一种方法问题

让我们考虑一个带有随机名称列表的样本表

I am not sure why would you want to fetch records that were not requested by user, but here is one approach to your problem
Lets consider a sample table with a list of random names

DECLARE @Sample TABLE
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	SampleText VARCHAR(50)
)

INSERT INTO @Sample
SELECT 'abc' UNION ALL
SELECT 'abc1' UNION ALL
SELECT 'abc2' UNION ALL
SELECT 'abc3' UNION ALL
SELECT 'abc4' UNION ALL
SELECT 'xyz' UNION ALL
SELECT 'xyz1' UNION ALL
SELECT 'xyz2' UNION ALL
SELECT 'xyz3' UNION ALL
SELECT 'efg' UNION ALL
SELECT 'pqr' UNION ALL
SELECT 'mno' UNION ALL
SELECT 'efg' UNION ALL
SELECT 'sfr' UNION ALL
SELECT 'qwd' UNION ALL
SELECT 'gte' UNION ALL
SELECT 'sdf' UNION ALL
SELECT 'gtg' UNION ALL
SELECT 'sde' 

让我们假设用户请求以abc开头的记录。首先要做的是找到符合此条件的记录数。

Lets assume user requested for records that start with abc. First thing to do is to find the number of records that match this criteria.

DECLARE @Count INT
SELECT @Count = COUNT(*) FROM @Sample WHERE SampleText LIKE 'abc%'

接下来获取符合条件的所有记录，如果符合条件的记录数小于10则获取其他记录（不符合用户的标准），因此检索到的总记录为10.

Next get all the records that match the criteria and if the number of records matching the criteria is less than 10 then fetch other records (which don''t match the user''s criteria) so total records retrieved is 10.

SELECT TOP 10 * FROM @Sample WHERE SampleText LIKE 'abc%'
UNION ALL
SELECT TOP (10 - CASE WHEN @Count > 10 THEN 10 ELSE @Count END) *  FROM @Sample WHERE ID NOT IN (SELECT ID FROM @Sample WHERE SampleText LIKE 'abc%')

这篇关于从表中获取特定记录的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！