用于限制Access 2000中检索的详细信息数量的SQL语句？ [英] SQL Statement for limiting the number of detail retrieved in Access 2000?

问题描述

Hello All，


如何使用SQL限制在Master-Detail集中选择的详细记录数？


I想要选择日期的所有主记录，但只选择前3个记录的详细信息（基于详细记录的主键）。我已经用TOP 3尝试了
，但是无法到达任何地方。使用Access 2000.


类似于：


SELECT t1。*，TOP 3 t2。*

FROM t1，t2

WHERE（t1.item = t2.item）AND（t1.TrnDate =＃09/23/06＃）


任何帮助表示感谢，


Hexman

Hello All,

How do I limit the number of detail records selected in a Master-Detail set using SQL?

I want to select all master records for a date, but only the first 3 records for the details (based on the primary key of the detail record). I''ve
been trying with "TOP 3", but can''t get anywhere. Using Access 2000.

Something like:

SELECT t1.*, TOP 3 t2.*
FROM t1, t2
WHERE (t1.item = t2.item) AND (t1.TrnDate = #09/23/06#)

Any help appreciated,

Hexman

推荐答案

试试这个:(或类似的东西）


SELECT t1。*，t3。*

FROM t1

INNER JOIN

（SELECT TOP 3 t2。*

FROM t2）t3 ON t1.Item = t3.Item

WHERE（t1.TrnDate =＃09/23/06＃）


您甚至可能想要使用LEFT OUTER JOIN，取决于您的

数据。


Izzy

Hexman写道：

Try this: (Or something of the like)

SELECT t1.*, t3.*
FROM t1
INNER JOIN
(SELECT TOP 3 t2.*
FROM t2) t3 ON t1.Item = t3.Item
WHERE (t1.TrnDate = #09/23/06#)

You may even want too use a LEFT OUTER JOIN for this, depends on your
data.

Izzy

Hexman wrote:

Hello All，


如何限制所选的详细记录数量使用SQL设置Master-Detail？

我想为日期选择所有主记录，但只有前3个记录用于详细说明ils（基于详细记录的主键）。我已经用TOP 3尝试了
，但是无法到达任何地方。使用Access 2000.


类似于：


SELECT t1。*，TOP 3 t2。*

FROM t1，t2

WHERE（t1.item = t2.item）AND（t1.TrnDate =＃09/23/06＃）


任何帮助表示感谢，


Hexman

Hello All,

How do I limit the number of detail records selected in a Master-Detail set using SQL?

I want to select all master records for a date, but only the first 3 records for the details (based on the primary key of the detail record). I''ve
been trying with "TOP 3", but can''t get anywhere. Using Access 2000.

Something like:

SELECT t1.*, TOP 3 t2.*
FROM t1, t2
WHERE (t1.item = t2.item) AND (t1.TrnDate = #09/23/06#)

Any help appreciated,

Hexman

Izzy，


谢谢响应。无论是使用Access还是SS EE，我仍然无法使用它。它在执行期间不会产生错误，但不会返回任何

行。如果我拿出（SELECT TOP 3 t2。* FROM t2）并改变所有t3对t2的引用，它检索行（除了太多行）。


这让我很难过。还有什么想法？


我想做的就是限制从t2返回的行数与t1一起加入。


谢谢，


Hexman

2006年9月23日16:17:22 -0700，Izzy <是************ @ gmail.comwrote：

Izzy,

Thanks for the response. I still can''t get it to work, either with Access or SS EE. It generates no error during execution, but doesn''t return any
rows. If I take out the "(SELECT TOP 3 t2.* FROM t2)" and change all "t3" references to "t2", it retrieves the rows (except too many of them).

This has me stumped. Any further ideas?

All I want to do is limit the number of rows returned from t2 to be joined with t1.

Thanks,

Hexman
On 23 Sep 2006 16:17:22 -0700, "Izzy" <is************@gmail.comwrote:

>试试这个:(或类似的东西）

SELECT t1。*，t3。*
FROM t1
INNER JOIN
（选择TOP 3 t2。*

来自t2）t3 ON t1.Item = t3.Item
在哪里（t1.TrnDate =＃09/23/06＃）

你甚至可能想要使用LEFT OUTER JOIN来取决于你的<数据。

Izzy

Hexman写道：

>Try this: (Or something of the like)

SELECT t1.*, t3.*
FROM t1
INNER JOIN
(SELECT TOP 3 t2.*
FROM t2) t3 ON t1.Item = t3.Item
WHERE (t1.TrnDate = #09/23/06#)

You may even want too use a LEFT OUTER JOIN for this, depends on your
data.

Izzy

Hexman wrote:

> Hello All，

如何使用SQL限制在Master-Detail集中选择的详细记录的数量？

我想为日期选择所有主记录，但只选择前3个记录详细信息（基于详细记录的主键）。我已经尝试使用TOP 3，但无法到达任何地方。使用Access 2000.

类似于：

SELECT t1。*，TOP 3 t2。*
FROM t1，t2
WHERE（t1.item = t2.item）AND（t1.TrnDate =＃09/23/06＃）

任何帮助表示赞赏，

Hexman

>Hello All,

How do I limit the number of detail records selected in a Master-Detail set using SQL?

I want to select all master records for a date, but only the first 3 records for the details (based on the primary key of the detail record). I''ve
been trying with "TOP 3", but can''t get anywhere. Using Access 2000.

Something like:

SELECT t1.*, TOP 3 t2.*
FROM t1, t2
WHERE (t1.item = t2.item) AND (t1.TrnDate = #09/23/06#)

Any help appreciated,

Hexman

Hexman写道：

Hexman wrote:

Hello all，


我如何限制在
中选择的详细记录数

Hello All,

How do I limit the number of detail records selected in a

使用SQL设置Master-Detail？

Master-Detail set using SQL?

>

我想选择日期的所有主记录，但只有前3

>
I want to select all master records for a date, but only the first 3

详细记录（基于详细记录的主键）。

我已经

records for the details (based on the primary key of the detail record).
I''ve

一直尝试使用TOP 3，但无法到达任何地方。使用Access 2000.


类似于：


SELECT t1。*，TOP 3 t2。*

FROM t1，t2

WHERE（t1.item = t2.item）AND（t1.TrnDate =＃09/23/06＃）


任何帮助表示感谢，


Hexman

been trying with "TOP 3", but can''t get anywhere. Using Access 2000.

Something like:

SELECT t1.*, TOP 3 t2.*
FROM t1, t2
WHERE (t1.item = t2.item) AND (t1.TrnDate = #09/23/06#)

Any help appreciated,

Hexman

在优化之前你需要了解你在做什么：）

现在，您正在创建一个数据集，该数据集有效地为t1中的每个条目创建t2中所有

记录的副本。如果你有很多记录，这个

会非常快速地得到你的手（即2个表每个1000条记录

会以这种方式在内存中产生10.000条记录）所以使用像Izzy一样的加入

建议通常要好得多。


这个怎么样：


选择*

来自t1内部联接t2（t1.item = t2.item）

其中t1.TrnDate =＃09/23/06＃

和t2.item in

（从t2中选择前3项）


这应该加入2个表格，其中2个表格中的项目匹配并且

t1中的日期是指定的日期，项目出现在

t2的前3位。 t2没有排序顺序，因此结果可能无法预测。

-

Rinze van Huizen

C-Services Holland bv

You need to understand what you''re doing before you can optimize it :)
Now you''re creating a dataset that effectively creates a copy of all
records in t2 for each entry in t1. If you have alot of records, this
will get ouf of hand very quickly (i.e. 2 tables with 1000 records each
will cause 10.000 records in memory this way) So using a join like Izzy
suggested is usually much better.

How about this:

Select *
from t1 inner join t2 on (t1.item=t2.item)
where t1.TrnDate=#09/23/06#
and t2.item in
(select top 3 item from t2)

This should join the 2 tables where the ''item'' in the 2 tables match and
the date in t1 is the one specified and the items appear in the top 3 of
t2. There is no sort order given to t2, so results may be unpredictable.
--
Rinze van Huizen
C-Services Holland b.v

这篇关于用于限制Access 2000中检索的详细信息数量的SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！