返回带有“DISTINCT"的唯一集和多个其他选定的列 [英] Return Unique set with "DISTINCT" and multiple other Selected columns
问题描述
我正在从多个表创建一个视图.包含的每个表都有一个名为 Contact_ID 的列.我使用的是 MS SQL 2005.我看到我可以使用 DISTINCT 返回一个由 Contact_ID 设置的唯一集合,但是我的视图只显示了 Contact_ID.
I am creating a view from multiple tables. Each table being included has a column named Contact_ID. I am using MS SQL 2005. I see that I can use DISTINCT to return a unique set by Contact_ID but then my view only shows the Contact_ID.
我需要视图不仅仅包含 Contact_ID,所以我需要创建一个包含所有正确列的 SELECT 语句,但我不能有重复的记录.
I need the view to contain more than just the Contact_ID so I need to create a SELECT statement with all the correct columns but I can't have duplicate records.
这是我目前拥有的:(这将返回我需要的所有数据,但包含具有重复 Contact_ID 的记录)
Here is what I currently have: (This returns all the data I need but contains records with duplicates of Contact_ID)
SELECT dbo.[1_MAIN - Contacts].Contact_ID, dbo.[1_MAIN - Contacts].Date_entered_into_Database, dbo.[1_MAIN - Contacts].Date_of_Initial_Contact,
dbo.[1_MAIN - Contacts].[Company_ Name], dbo.[1_MAIN - Contacts].Key_Contact_Title, dbo.[1_MAIN - Contacts].Key_Contact_First_Name,
dbo.[1_MAIN - Contacts].Key_Contact_Middle, dbo.[1_MAIN - Contacts].Key_Contact_Last_Name, dbo.[1_MAIN - Contacts].Key_Credential,
dbo.[1_MAIN - Contacts].Key_Contact_Occupation, dbo.[1_MAIN - Contacts].Key_Degree_1, dbo.[1_MAIN - Contacts].Key_Degree_2,
dbo.[1_MAIN - Contacts].Key_Degree_3, dbo.[1_MAIN - Contacts].Date_of_Highest_Degree, dbo.[1_MAIN - Contacts].Work_Setting,
dbo.[1_MAIN - Contacts].Website_Address, dbo.[1_MAIN - Contacts].Email_1_Key_Contact, dbo.[1_MAIN - Contacts].Email_2,
dbo.[1_MAIN - Contacts].Email_3, dbo.[1_MAIN - Contacts].Day_Time_Phone_Number, dbo.[1_MAIN - Contacts].Extension,
dbo.[1_MAIN - Contacts].Mobile_Phone_Number, dbo.[1_MAIN - Contacts].Bus_Fax_Number, dbo.[1_MAIN - Contacts].Home_Phone_Number,
dbo.[1_MAIN - Contacts].Home_Fax_Number, dbo.[1_MAIN - Contacts].Mailing_Street_1, dbo.[1_MAIN - Contacts].Mailing_Street_2,
dbo.[1_MAIN - Contacts].Mailing_City, dbo.[1_MAIN - Contacts].Mailing_State, dbo.[1_MAIN - Contacts].[Mailing_Zip/Postal],
dbo.[1_MAIN - Contacts].Mailing_Country, dbo.[1_MAIN - Contacts].[Bad_Address?], dbo.[1_MAIN - Contacts].[PROV/REG?],
dbo.[1_MAIN - Contacts].status_flag, dbo.[1_MAIN - Contacts].status_flag AS status_flag2, dbo.Providers.Contact_ID AS Expr1,
dbo.Providers.Referral_Source, dbo.Resource_Center.access, dbo.Referral.Contact_Source, dbo.Resource_Center.cert_start_date,
dbo.Resource_Center.cert_exp_date, dbo.prov_training_records.Contact_ID AS Expr2, dbo.prov_training_records.date_reg_email_sent
FROM dbo.[1_MAIN - Contacts] INNER JOIN
dbo.Referral ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID INNER JOIN
dbo.prov_training_records ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID LEFT OUTER JOIN
dbo.Resource_Center ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID FULL OUTER JOIN
dbo.Providers ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
具有别名的表名的相同查询:
Same query with table names aliased:
SELECT c.Contact_ID, c.Date_entered_into_Database, c.Date_of_Initial_Contact,
c.[Company_ Name], c.Key_Contact_Title, c.Key_Contact_First_Name,
c.Key_Contact_Middle, c.Key_Contact_Last_Name, c.Key_Credential,
c.Key_Contact_Occupation, c.Key_Degree_1, c.Key_Degree_2,
c.Key_Degree_3, c.Date_of_Highest_Degree, c.Work_Setting,
c.Website_Address, c.Email_1_Key_Contact, c.Email_2,
c.Email_3, c.Day_Time_Phone_Number, c.Extension,
c.Mobile_Phone_Number, c.Bus_Fax_Number, c.Home_Phone_Number,
c.Home_Fax_Number, c.Mailing_Street_1, c.Mailing_Street_2,
c.Mailing_City, c.Mailing_State, c.[Mailing_Zip/Postal],
c.Mailing_Country, c.[Bad_Address?], c.[PROV/REG?],
c.status_flag, c.status_flag AS status_flag2, p.Contact_ID AS Expr1,
p.Referral_Source, rc.access, r.Contact_Source, rc.cert_start_date,
rc.cert_exp_date, tr.Contact_ID AS Expr2, tr.date_reg_email_sent
FROM dbo.[1_MAIN - Contacts] c INNER JOIN
dbo.Referral r ON c.Contact_ID = r.Referral_ID INNER JOIN
dbo.prov_training_records tr ON c.Contact_ID = tr.Contact_ID LEFT OUTER JOIN
dbo.Resource_Center rc ON c.Contact_ID = rc.Contact_ID FULL OUTER JOIN
dbo.Providers p ON c.Contact_ID = p.Contact_ID
推荐答案
WITH q AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID ORDER BY dbo.[1_MAIN - Contacts].Contact_ID) AS rn
FROM dbo.[1_MAIN - Contacts]
INNER JOIN
dbo.Referral
ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
INNER JOIN
dbo.prov_training_records
ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
LEFT OUTER JOIN
dbo.Resource_Center
ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
FULL OUTER JOIN
dbo.Providers
ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
)
SELECT *
FROM q
WHERE rn = 1
这篇关于返回带有“DISTINCT"的唯一集和多个其他选定的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!