SQL Server查询返回太多记录 [英] SQL Server query returns too many records

问题描述

我的SQL Server数据库中有3个表.

I have 3 tables in my SQL Server database.

如图所示，它们链接在一起(线连接到图中的右行)

They are linked together as shown in this picture (lines are connected to right rows in picture)

我有一个查询，该查询应该返回 tblreparations 的所有赔偿，并提供有关所维修内容的一些信息，但是它将返回赔偿3次，对于客户端的每台笔记本电脑都是一次赔偿(倾斜).荷兰语)分配给它，而赔偿表(荷兰语中的reparaties)每行仅包含一个 laptopID

I have a query which should return all the reparations from tblreparations with some information about what is repaired, but instead it returns the reparation 3 times, one time for each laptop that the client (klant in dutch) has assigned to it, while the reparations table (reparaties in dutch) only contains one laptopID each row

这是查询:

SELECT AankopenReparaties.Id, 
       AankopenReparaties.KlantenId, 
       AankopenReparaties.actietype, 
       AankopenReparaties.voorwerptype, 
       laptopscomputers.merk, 
       laptopscomputers.model, 
       laptopscomputers.info, 
       AankopenReparaties.info, 
       AankopenReparaties.Prijs, 
       AankopenReparaties.lopend 
FROM AankopenReparaties, laptopscomputers 
WHERE (aankopenreparaties.lopend = 'lopend');

它返回此

，它应该只有一行，因为赔偿表(aankopenreparaties)仅包含一行，其中一行带有一个 laptopID

and it should be only one row since the reparations table (aankopenreparaties) only contains one row with one laptopID

有人知道如何解决此问题吗?

Does anyone know how to fix this?

请帮助，因为应该尽快修复(这是学校的作业)

Please help because it should be fixed soon (it's an assignment for school)

推荐答案

之所以返回太多记录是因为您的查询会生成两个表的笛卡尔积.您需要告诉服务器这两个表如何相互关联.

The reason why you are returning too many records is because your query produces cartesian product of both tables. You need to tell the server on how the two tables are related with each other.

SELECT  AankopenReparaties.Id,
        AankopenReparaties.KlantenId,
        AankopenReparaties.actietype,
        AankopenReparaties.voorwerptype,
        laptopscomputers.merk,
        laptopscomputers.model,
        laptopscomputers.info,
        AankopenReparaties.info,
        AankopenReparaties.Prijs,
        AankopenReparaties.lopend
FROM    AankopenReparaties
        INNER JOIN laptopscomputers
            ON AankopenReparaties.LaptopID = laptopscomputers.ID -- specify relationship
WHERE   aankopenreparaties.lopend = 'lopend'

要进一步获得有关联接的知识，请访问下面的链接:

To further gain more knowledge about joins, kindly visit the link below:

• SQL联接的可视表示

• INNER JOIN ON与WHERE子句

这篇关于SQL Server查询返回太多记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！