SQL View 或 Crystal Reports 中的前 N 个？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3104520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Top N in View or Crystal Reports?
提问by AndyD273
I am wondering if it's possible to use a view to get the top 5 lines from a table. I am finding that Crystal reports doesn't seem to have anything built in to do this, or I'd do it there.
我想知道是否可以使用视图从表格中获取前 5 行。我发现 Crystal Reports 似乎没有内置任何东西来做到这一点，或者我会在那里做。
When I query the view Select * from qryTranHistory, it returns the first 5 items, but if I try to select a specific type Select * from qryTranHistory Where tID = 45it returns nothing, since there are no tID=45 in the top 5 normally.
当我查询视图时Select * from qryTranHistory，它返回前 5 个项目，但如果我尝试选择特定类型，Select * from qryTranHistory Where tID = 45它什么也不返回，因为通常前 5 个中没有 tID=45。
Is it possible to do this?
Can it be accomplished in a sub report in Crystal Reports?
是否有可能做到这一点？
可以在 Crystal Reports 的子报表中完成吗？
回答by John Price
It is easy to limit a report to the top 5 records. In the menu, just choose
很容易将报告限制为前 5 条记录。在菜单中，只需选择
Report --> Selection Formulas... --> Group
报告 --> 选择公式... --> 组
In the formula, enter "RecordNumber <= 5" and you are done.
在公式中，输入“RecordNumber <= 5”就完成了。
You don't need to have a group field nor summary field to do the group filter. You don't need a sort order, but using top N records without a sort order doesn't usually make much sense. It might not be efficient as OMG Ponies suggested, but for small number of records it is OK.
您不需要有组字段或汇总字段来执行组过滤器。您不需要排序顺序，但使用没有排序顺序的前 N 条记录通常没有多大意义。它可能不像 OMG Ponies 建议的那样高效，但对于少量记录来说是可以的。
回答by Josaph
You can reference a sproc from Crystal Reports. In the sproc, use a conditional on the parameter.
您可以从 Crystal Reports 引用 sproc。在 sproc 中，对参数使用条件。
ALTER PROCEDURE dbo.Get_TOP5
(
@tID INT = NULL
)
AS
IF @tID IS NULL
BEGIN
SELECT TOP 5
FIELD1,
FIELD2
FROM qryTranHistory
END
ELSE
BEGIN
SELECT
FIELD1,
FIELD2
FROM qryTranHistory
WHERE tID =@tID
END回答by sanrns
A simple setting can limit the records to top 5!! Here it is, if you're using .Net 1.1 (similar arrangement of options in higher frameworks too!).
一个简单的设置就可以将记录限制在前5名！！如果您使用的是 .Net 1.1（在更高的框架中也有类似的选项安排！）。
- Right click on the report layout > Reports > Top N/Sort Group Expert > Choose Top Nin the Dropdown that asks for the type of filtering/ sorting you wish to do > Set the Value of top N(5 in your case) > Uncheck the option that includes other records. Your report will be filtered for only the top 5 records from the Dataset.
- 右键单击报告布局 > 报告 > 前 N 个/排序组专家 >在下拉列表中选择前 N 个，询问您希望执行的过滤/排序类型> 设置前N 个值（在您的情况下为 5）> 取消选中包含其他记录的选项。您的报告将仅过滤数据集中的前 5 条记录。
There's another way how it could be done and that is through the Record selection formula where you limit the No. of records, as suggested by John Price in this thread.
还有另一种方法可以完成，那就是通过记录选择公式来限制记录数，正如 John Price 在此线程中所建议的那样。
Cheers!
干杯!
回答by Sun
If you have a small recordset, you can create a running total that counts the change of rows (field1), then in Section Expert in Details, tell it to supress RTotal0 (your running total variable) to > 5
如果您有一个小记录集，您可以创建一个运行总计来计算行（字段 1）的变化，然后在“详细信息”部分专家中，告诉它将 RTotal0（您的运行总计变量）抑制为 > 5
回答by Tom H
Can you put the TOP in your SELECT statement instead of in the view?
您可以将 TOP 放在 SELECT 语句中而不是视图中吗？
SELECT TOP 5
col1,
col2,
...
FROM
qryTranHistory
WHERE
tid = 45
回答by JonH
If your table has more then 5 rows I hope this query:
如果您的表有超过 5 行，我希望这个查询：
SELECT * FROM qryTranHistory
Returns more then 5 rows because you never mentioned TOP 5. Your question doesn't make a lot of sense as I am not sure waht you are after. You mentioned if you ran your query with WHERE tID=45, it returns nothing, what exactly do you want it to return ?
返回超过 5 行，因为您从未提到 TOP 5。您的问题没有多大意义，因为我不确定您是在追求什么。你提到如果你用 WHERE tID=45 运行你的查询，它什么都不返回，你到底想要它返回什么？
Read up on TOPin BOL:
继续阅读TOPBOL：
SELECT TOP 10 Recs FROM Records WHERE...
By the way you do not want to do this in the report / a form interface, you want to do this in your db layer.
顺便说一句，您不想在报表/表单界面中执行此操作，而是希望在 db 层中执行此操作。
回答by JonH
You cando Top N processing in Crystal Reports, but it's a little obscure - you have to use the group sort expert (and in order to use that, you need to have groups and summary fields inserted into the groups.)
您可以在 Crystal Reports 中进行 Top N 处理，但它有点晦涩——您必须使用组排序专家（为了使用它，您需要将组和汇总字段插入到组中。）
Doing the Top N processing in the query should be more efficient, where possible.
在可能的情况下，在查询中执行 Top N 处理应该更有效。
