多个表的视图。需要删除一个表定义的“double”

Ok, so this is what i'm stuck with.

好，这就是我要讲的。

Full size

全尺寸

SELECT     dbo.InstellingGegevens.INST_SUBTYPE, dbo.InstellingGegevens.INST_BRON, dbo.InstellingGegevens.INST_INSTELLINGSNUMMER, 
                      dbo.InstellingGegevens.INST_NAAM, dbo.InstellingGegevens.INST_KORTENAAM, dbo.InstellingGegevens.INST_VESTIGINGSNAAM, 
                      dbo.InstellingGegevens.INST_ROEPNAAM, dbo.InstellingGegevens.INST_STATUUT, dbo.InstellingGegevens.ONDERWIJSNIVEAU_REF, 
                      dbo.InstellingGegevens.ONDERWIJSSOORT_REF, dbo.InstellingGegevens.DATUM_TOT, dbo.InstellingGegevens.DATUM_VAN, 
                      dbo.InstellingGegevens.VERBOND_REF, dbo.InstellingGegevens.VSKO_LID, dbo.InstellingGegevens.NET_REF, dbo.Instellingen.Inst_ID, dbo.Instellingen.INST_TYPE, 
                      dbo.Instellingen.INST_REF, dbo.Instellingen.INST_LOC_REF, dbo.Instellingen.INST_LOCNR, dbo.Instellingen.Opt_KalStandaard, dbo.InstellingTelecom.INST_TEL, 
                      dbo.InstellingTelecom.INST_FAX, dbo.InstellingTelecom.INST_EMAIL, dbo.InstellingTelecom.INST_WEB, dbo.InstellingAdressen.SOORT, 
                      dbo.InstellingAdressen.STRAAT, dbo.InstellingAdressen.POSTCODE, dbo.InstellingAdressen.GEMEENTE, dbo.InstellingAdressen.GEM_REF, 
                      dbo.InstellingAdressen.FUSIEGEM_REF, dbo.InstellingAdressen.FUSIEGEM, dbo.InstellingAdressen.ALFA_G, dbo.InstellingAdressen.PROVINCIE, 
                      dbo.InstellingAdressen.BISDOM, dbo.InstellingAdressen.ARRONDISSEMENT, dbo.InstellingAdressen.GEWEST, dbo.InstellingLogin.Inst_Gebruikersnaam, 
                      dbo.InstellingLogin.Inst_Concode, dbo.InstellingLogin.Inst_DirCode, dbo.InstellingLogin.DOSSNR, dbo.InstellingLogin.Instelling_ID, 
                      dbo.InstellingContPersDirecteurs.AANSPREKING, dbo.InstellingContPersDirecteurs.CONTACTPERSOON, dbo.InstellingContPersDirecteurs.FUNCTIE
FROM         dbo.InstellingGegevens RIGHT OUTER JOIN
                      dbo.Instellingen ON dbo.InstellingGegevens.INST_TYPE = dbo.Instellingen.INST_TYPE AND dbo.InstellingGegevens.INST_REF = dbo.Instellingen.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.Instellingen.INST_LOC_REF AND 
                      dbo.InstellingGegevens.INST_LOCNR = dbo.Instellingen.INST_LOCNR LEFT OUTER JOIN
                      dbo.InstellingTelecom ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingTelecom.INST_TYPE AND 
                      dbo.InstellingGegevens.INST_REF = dbo.InstellingTelecom.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingTelecom.INST_LOC_REF LEFT OUTER JOIN
                      dbo.InstellingAdressen ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingAdressen.INST_TYPE AND 
                      dbo.InstellingGegevens.INST_REF = dbo.InstellingAdressen.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingAdressen.INST_LOC_REF LEFT OUTER JOIN
                      dbo.InstellingLogin ON dbo.InstellingLogin.Inst_InstellingIKONType = dbo.Instellingen.INST_TYPE AND 
                      dbo.InstellingLogin.Inst_InstellingIKON_REF = dbo.Instellingen.INST_REF AND dbo.InstellingLogin.Inst_Loc_REF = dbo.Instellingen.INST_LOC_REF AND 
                      dbo.InstellingLogin.Inst_Loc_Nr = dbo.Instellingen.INST_LOCNR LEFT OUTER JOIN
                      dbo.InstellingContPersDirecteurs ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingContPersDirecteurs.INST_TYPE AND 
                      dbo.InstellingGegevens.INST_REF = dbo.InstellingContPersDirecteurs.INST_REF AND 
                      dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingContPersDirecteurs.INST_LOC_REF
WHERE     (NOT (dbo.InstellingLogin.Inst_InstellingIKON_REF IS NULL))

So here is the problem:

问题是:

the 'should be' PK is a 1 varchar 3 int's key. for every key there is supposed to be 1 row in each of the tables which you can see in the image. the 'parent' of those keys is the table Instellingen. This table is generated with a distinct select of InstellingenLogin

“应该是”PK是1 varchar 3 int的关键。对于每个键，每个表中都应该有一行在图像中可以看到。这些键的“父”是表Instellingen。此表是使用不同的InstellingenLogin选择生成的

the real problem is that there are about 10 doubles in InstellingenLogin (of about 5k records) and because of this, some rows return double in the view, with only the columns of InstellingLogin different.

真正的问题是在InstellingenLogin中大约有10个双精度登录(大约有5k条记录)，因此，一些行在视图中返回双精度登录，只有InstellingLogin的列不同。

what i want is that if there are 2 or more rows in InstellingLogin with the same key, that only 1 will show (the first one,... doenst matter which one, just 1 will do).

我想要的是，如果在安装相同键的登录中有2行或更多行，那么只有1行会显示(第一个，……)关键是哪一个，只有一个)。

in short that means that for every record in Instellingen i want 1 record in this view.

简而言之，这意味着对于Instellingen中的每条记录，我希望在这个视图中有一条记录。

is there any way to do that?

有什么办法吗?

4 个解决方案

#1

I'm a bit confused but I think the answer below should illustrate how to acheive what you need:

我有点困惑，但我认为下面的答案应该说明如何满足你的需要:

SELECT * FROM Instellingen as i
CROSS APPLY
(   
    SELECT TOP (1) * FROM InstellingLogin as il
    WHERE i.INST_LOC_REF = il.Inst_Loc_REF 
    and i.INST_LOCNR=il.Inst_Loc_Nr 
    and i.INST_REF=il.Inst_InstellingIKON_REF 
    and i.INST_TYPE=il.Inst_InstellingIKONType
    order by il.Datum_tot
) la

This will basically join on Instellingen and InstellingenLogin but only on the first record found

这将基本上连接到Instellingen和InstellingenLogin，但只在第一个记录中找到。

#2

You can use DENSE_RANK to Arbitrarily select a row. Basically create a CTE that ranks the instellinglogin and then only select the one with a Rank of 1. The tricky bit is that you have a left join to instellinglogin so you'll need to do the inner join inside the left join like so. Also I kept the Join condition as the the columns to partition. This may not be correct.

可以使用DENSE_RANK任意选择一行。基本上创建一个CTE，对instellinglogin进行排名，然后只选择排名为1的那个。棘手的一点是，您有一个左连接来安装登录，所以您需要像这样在左连接中执行内部连接。我还保留了Join条件作为到分区的列。这可能不对。

LEFT OUTER JOIN (dbo.instellinglogin 
                            INNER JOIN unique_login 
                              ON dbo.instellinglogin.inst_instellingikontype = 
                                      unique_login .inst_instellingikontype 
                                 AND dbo.instellinglogin.inst_instellingikon_ref = 
                                     unique_login.inst_instellingikon_ref 
                                 AND dbo.instellinglogin.inst_loc_ref = 
                                     unique_login.inst_loc_ref 
                                 AND dbo.instellinglogin.inst_loc_nr = 
                                     unique_login.inst_loc_nr 
                                 AND unique_login.therank = 1 ) 
             ON dbo.instellinglogin.inst_instellingikontype = 
                dbo.instellingen.inst_type 
                AND dbo.instellinglogin.inst_instellingikon_ref = 
                    dbo.instellingen.inst_ref 
                AND dbo.instellinglogin.inst_loc_ref = dbo.instellingen.inst_loc_ref 
                AND dbo.instellinglogin.inst_loc_nr = dbo.instellingen.inst_locnr 

Here's the complete SQL Below

下面是完整的SQL语句

WITH unique_login 
     AS (SELECT instellinglogin.inst_instellingikontype, 
                instellinglogin.inst_instellingikon_ref, 
                instellinglogin.inst_loc_ref, 
                instellinglogin.inst_loc_nr, 
                Dense_rank() OVER (ORDER BY 
                instellinglogin.inst_instellingikontype, 
                instellinglogin.inst_instellingikon_ref, 
                instellinglogin.inst_loc_ref, 
                instellinglogin.inst_loc_nr) AS therank) 
SELECT dbo.instellinggegevens.inst_subtype, 
       dbo.instellinggegevens.inst_bron, 
       dbo.instellinggegevens.inst_instellingsnummer, 
       dbo.instellinggegevens.inst_naam, 
       dbo.instellinggegevens.inst_kortenaam, 
       dbo.instellinggegevens.inst_vestigingsnaam, 
       dbo.instellinggegevens.inst_roepnaam, 
       dbo.instellinggegevens.inst_statuut, 
       dbo.instellinggegevens.onderwijsniveau_ref, 
       dbo.instellinggegevens.onderwijssoort_ref, 
       dbo.instellinggegevens.datum_tot, 
       dbo.instellinggegevens.datum_van, 
       dbo.instellinggegevens.verbond_ref, 
       dbo.instellinggegevens.vsko_lid, 
       dbo.instellinggegevens.net_ref, 
       dbo.instellingen.inst_id, 
       dbo.instellingen.inst_type, 
       dbo.instellingen.inst_ref, 
       dbo.instellingen.inst_loc_ref, 
       dbo.instellingen.inst_locnr, 
       dbo.instellingen.opt_kalstandaard, 
       dbo.instellingtelecom.inst_tel, 
       dbo.instellingtelecom.inst_fax, 
       dbo.instellingtelecom.inst_email, 
       dbo.instellingtelecom.inst_web, 
       dbo.instellingadressen.soort, 
       dbo.instellingadressen.straat, 
       dbo.instellingadressen.postcode, 
       dbo.instellingadressen.gemeente, 
       dbo.instellingadressen.gem_ref, 
       dbo.instellingadressen.fusiegem_ref, 
       dbo.instellingadressen.fusiegem, 
       dbo.instellingadressen.alfa_g, 
       dbo.instellingadressen.provincie, 
       dbo.instellingadressen.bisdom, 
       dbo.instellingadressen.arrondissement, 
       dbo.instellingadressen.gewest, 
       dbo.instellinglogin.inst_gebruikersnaam, 
       dbo.instellinglogin.inst_concode, 
       dbo.instellinglogin.inst_dircode, 
       dbo.instellinglogin.dossnr, 
       dbo.instellinglogin.instelling_id, 
       dbo.instellingcontpersdirecteurs.aanspreking, 
       dbo.instellingcontpersdirecteurs.contactpersoon, 
       dbo.instellingcontpersdirecteurs.functie 
FROM   dbo.instellinggegevens 
       RIGHT OUTER JOIN dbo.instellingen 
         ON dbo.instellinggegevens.inst_type = dbo.instellingen.inst_type 
            AND dbo.instellinggegevens.inst_ref = dbo.instellingen.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingen.inst_loc_ref 
            AND dbo.instellinggegevens.inst_locnr = dbo.instellingen.inst_locnr 
       LEFT OUTER JOIN dbo.instellingtelecom 
         ON dbo.instellinggegevens.inst_type = dbo.instellingtelecom.inst_type 
            AND dbo.instellinggegevens.inst_ref = dbo.instellingtelecom.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingtelecom.inst_loc_ref 
       LEFT OUTER JOIN dbo.instellingadressen 
         ON dbo.instellinggegevens.inst_type = dbo.instellingadressen.inst_type 
            AND dbo.instellinggegevens.inst_ref = 
                dbo.instellingadressen.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingadressen.inst_loc_ref 
       LEFT OUTER JOIN (dbo.instellinglogin 
                        INNER JOIN unique_login 
                          ON dbo.instellinglogin.inst_instellingikontype = 
                                  unique_login .inst_instellingikontype 
                             AND dbo.instellinglogin.inst_instellingikon_ref = 
                                 unique_login.inst_instellingikon_ref 
                             AND dbo.instellinglogin.inst_loc_ref = 
                                 unique_login.inst_loc_ref 
                             AND dbo.instellinglogin.inst_loc_nr = 
                                 unique_login.inst_loc_nr 
                             AND unique_login.therank = 1 ) 
         ON dbo.instellinglogin.inst_instellingikontype = 
            dbo.instellingen.inst_type 
            AND dbo.instellinglogin.inst_instellingikon_ref = 
                dbo.instellingen.inst_ref 
            AND dbo.instellinglogin.inst_loc_ref = dbo.instellingen.inst_loc_ref 
            AND dbo.instellinglogin.inst_loc_nr = dbo.instellingen.inst_locnr 
       LEFT OUTER JOIN dbo.instellingcontpersdirecteurs 
         ON dbo.instellinggegevens.inst_type = 
            dbo.instellingcontpersdirecteurs.inst_type 
            AND dbo.instellinggegevens.inst_ref = 
                dbo.instellingcontpersdirecteurs.inst_ref 
            AND dbo.instellinggegevens.inst_loc_ref = 
                dbo.instellingcontpersdirecteurs.inst_loc_ref 
WHERE  ( NOT ( dbo.instellinglogin.inst_instellingikon_ref IS NULL ) ) 

#3

You need to wrap InstellingenLogin in some kind of aggregate to remove the duplicates and then join to the result - you can do this with a subquery. You could do a GROUP BY with MIN() or MAX() to pick a value or you could do ROW_NUMBER() OVER (ORDER BY some_criteria PARTIION BY your_key) and pick the first row.

您需要将InstellingenLogin封装到某种聚合中，以删除重复的内容，然后连接到结果—您可以使用子查询实现这一点。您可以使用MIN()或MAX()进行分组来选择一个值，也可以使用ROW_NUMBER()除以(根据some_criteria PARTIION BY your_key)并选择第一行。

Also, I recommend using aliases on your tables - makes it far more readable

此外，我建议在您的表上使用别名——使其可读性更强

WITH UniqueLogins AS (
    -- How to pick Inst_Gebruikersnaam, Inst_Concode, Inst_DirCode, DOSSNR, Instelling_ID, InstellingIKONType, Inst_Loc_REF, Inst_Loc_Nr, Inst_InstellingIKON_REF
    SELECT key columns, MIN(non key column), MIN(non key column), MIN(non key column)
    FROM dbo.InstellingLogin
    GROUP BY key columns
)
SELECT     G.INST_SUBTYPE, G.INST_BRON, G.INST_INSTELLINGSNUMMER, 
                  G.INST_NAAM, G.INST_KORTENAAM, G.INST_VESTIGINGSNAAM, 
                  G.INST_ROEPNAAM, G.INST_STATUUT, G.ONDERWIJSNIVEAU_REF, 
                  G.ONDERWIJSSOORT_REF, G.DATUM_TOT, G.DATUM_VAN, 
                  G.VERBOND_REF, G.VSKO_LID, G.NET_REF, I.Inst_ID, I.INST_TYPE, 
                  I.INST_REF, I.INST_LOC_REF, I.INST_LOCNR, I.Opt_KalStandaard, T.INST_TEL, 
                  T.INST_FAX, T.INST_EMAIL, T.INST_WEB, A.SOORT, 
                  A.STRAAT, A.POSTCODE, A.GEMEENTE, A.GEM_REF, 
                  A.FUSIEGEM_REF, A.FUSIEGEM, A.ALFA_G, A.PROVINCIE, 
                  A.BISDOM, A.ARRONDISSEMENT, A.GEWEST, UniqueLogins.Inst_Gebruikersnaam, 
                  UniqueLogins.Inst_Concode, UniqueLogins.Inst_DirCode, UniqueLogins.DOSSNR, UniqueLogins.Instelling_ID, 
                  CPD.AANSPREKING, CPD.CONTACTPERSOON, CPD.FUNCTIE
FROM         dbo.InstellingGegevens AS G RIGHT OUTER JOIN
                  dbo.Instellingen AS I ON G.INST_TYPE = I.INST_TYPE AND G.INST_REF = I.INST_REF AND 
                  G.INST_LOC_REF = I.INST_LOC_REF AND 
                  G.INST_LOCNR = I.INST_LOCNR LEFT OUTER JOIN
                  dbo.InstellingTelecom AS T ON G.INST_TYPE = T.INST_TYPE AND 
                  G.INST_REF = T.INST_REF AND 
                  G.INST_LOC_REF = T.INST_LOC_REF LEFT OUTER JOIN
                  dbo.InstellingAdressen AS A ON G.INST_TYPE = A.INST_TYPE AND 
                  G.INST_REF = A.INST_REF AND 
                  G.INST_LOC_REF = A.INST_LOC_REF LEFT OUTER JOIN
                  UniqueLogins ON UniqueLogins.Inst_InstellingIKONType = I.INST_TYPE AND 
                  UniqueLogins.Inst_InstellingIKON_REF = I.INST_REF AND UniqueLogins.Inst_Loc_REF = I.INST_LOC_REF AND 
                  UniqueLogins.Inst_Loc_Nr = I.INST_LOCNR LEFT OUTER JOIN
                  dbo.InstellingContPersDirecteurs AS CPD ON G.INST_TYPE = CPD.INST_TYPE AND 
                  G.INST_REF = CPD.INST_REF AND 
                  G.INST_LOC_REF = CPD.INST_LOC_REF
WHERE     (NOT (UniqueLogins.Inst_InstellingIKON_REF IS NULL))

#4

Or you could substitute the IntellingLogin table in the query with a derived table such as:

或者，您也可以将查询中的智能登录表替换为派生表，例如:

(SELECT RN = row_number() over
(partition by INST_LOC_REF,
INST_LOCNR, INST_REF, INST_TYPE order
by Datum_tot) , *  From
InstellingLogin) A

and add in the join condition: A.RN = 1

并加入连接条件:A。RN = 1