如何使用Top（）+ Count（）选择查询 [英] how to use select query with Top() + Count()

问题描述

Hello Team，



我有一个关于选择查询的查询

Hello Team,

I have one query regarding Select Query

select top qcallocation.TopCountForQC ,count(ID) as Count from Customerloandata with (nolock) where qcuserid = " + qcallocation.AllocateToQC + " AND createdby = " + qcallocation.DEOUserId + " AND isnull(QCPROCESSED,0) = 0 AND isnull(QCTOVERIFY,0) = 1  AND createdon >='" + qcallocation.FromDate.ObjectToDBDateTime() + "'  and createdon<='" + qcallocation.ToDate.ObjectToDBDateTime() + "' And isnull(deleted,0) = 0 and STATE = '" + qcallocation.State + "' and batchid = " + qcallocation.BatchId + " ";

在这个查询的一些列中，Value得到实体填充。

我的文本框如下：

In this query some of the columns,Value getting fill with entity.
I have the Textbox as follows:

<td>
   <input type="text" id="topCountForQCTextbox" style="width: 90px;" />
</td>

我在qcallocation.TopCountForQc中得到的值来自文本框的id是topCountForQCTextbox。所以如果我把价值在文本框中的e 5然后将是我的查询。我想在我的选择查询中得到顶部+计数，我没有得到。请告诉我我在哪里弄错了。

谢谢

Harshal。

I am getting the value in qcallocation.TopCountForQc From Textbox id that is topCountForQCTextbox .So if i put the value 5 in Textbox then what will be my Query .I want top + Count in my select query which i am not getting .Kindly tell me where i am getting the wrong.
Thanks
Harshal.

推荐答案

为了正确使用TOP，你必须指定一个ORDER BY子句 - 或者SQL可以自由地返回任何记录当时看起来合适的订单。



尝试将ORDER BY COUNT（Id）DESC添加到最后。

In order to use TOP at all properly, you have to specify a ORDER BY clause - or SQL is at liberty to return records in any order that seems appropriate at the time.

Try adding ORDER BY COUNT(Id) DESC to the end.

按照我明白，如果你在TextBox控件中输入 5 ，你想获取五条记录。



你应该使用存储过程 [ ^ ]如下：

As per i understand, you want to fetch five records if you type 5 in a TextBox control.

You should use stored procedure[^] like this:

CREATE PROCEDURE GetTopData
    @top INT
AS
BEGIN
    DECLARE @sql VARCHAR(2000)

    SET @sql = 'SELECT TOP(' + CONVERT(VARCHAR(5), @top) + ') * FROM TableName'
    EXEC(@sql)

END

CP KB：

获取表格中的前X行 [ ^ ]

在存储过程中构建动态SQL [ ^ ]

这篇关于如何使用Top（）+ Count（）选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！