单独的col。多选 [英] Single distinct col. On multiple selection

问题描述

这里我选择了很多col。但我只想要

 x.PurchaseOrderNumber 
 

才能与众不同。

i已经使用了

 SELECT disctinct（x.PurchaseOrderNumber），x.DateRaised AS DateRaised，LEFT（x.Description，100）as Description，x.TotalValue，x.SupplierName，
 x.POStatus ，
 x.TenantRecharge，x.JobActivity，x.BuildingName，x.BuildingID，x.ReferenceNumber，x.PaymentStatus，x.ACCTNUM，x.TotalCost 

但它没有达到目的



i甚至使用了

 x.PurchaseOrderNumber 
 但是我已经使用了我不想使用的聚合函数!!  
 
 
 
 
 SELECT x.PurchaseOrderNumber，x.DateRaised AS DateRaised，LEFT（x.Description，100）as Description，x.TotalValue，x.SupplierName，
 x.POStatus，
 x.TenantRecharge，x.JobActivity ，x.BuildingName，x.BuildingID，x.ReferenceNumber，x.PaymentStatus，x.ACCTNUM，x.TotalCost 
 FROM wsm_View_PODas hboardList AS X 
 INNER JOIN dbo.wsm_WorkOrder_PurchaseOrder wp ON wp.PurchaseOrderNumber = X.PurchaseOrderNumber 
 INNER JOIN dbo.wsm_WorkOrderSchedule wo ON wo.WONumber = wp.WONumber 
其中X.POStatus in（'创建'，'授权'，'已发送'，'匹配'）
和wo.WOStatus in（'新请求'，'延迟'，'预定'，'正在进行中'）
和wo .EstCompletionDate< = getdate（）
和wo.siteid in（从wsm_ContactSite中选择wsm_ContactSite.siteid，其中wsm_ContactSite.contactid = @ContactID）订购X.DateRaised DESC 
 
 
 
 
 我尝试了什么： 
 
 
 
i使用了
 
 
 
 
 SELECT disctinct（x.PurchaseOrderNumber），x.DateRaised AS DateRaised，LEFT（x.Description，100）as Description，x.TotalValue，x.SupplierName，
 x.POStatus，
 x.TenantRecharge，x.JobActivity，x.BuildingName，x.BuildingID，x.ReferenceNumber，x.PaymentStatus，x.ACCTNUM，x.TotalCost 
 
 
 
 
但它不能达到目的
 
 
 
i甚至使用<$ pre> x.PurchaseOrderNumber 

但是我已经使用了我不想使用的聚合函数!!

解决方案

首先，您不能在单个列上使用 DISTINCT 。如果您的查询返回多列。 DISTINCT 总是将结果减少到那些行，其中 所有 列值的组合是唯一的。



我想 JOIN 导致<$ c中每行的多行结果$ c> X 因为连接的表与它有1：n的关系。如果您只想要一行结果，则必须修改where子句，以便它只返回一个匹配项或考虑您要在 PurchaseOrderNumber 中返回的内容：

1）只有第一场比赛 - 然后使用 TOP 1 子句将结果限制为第一个匹配的记录

2） MIN ， MAX ， AVERAGE 或<$结果行中所有 PurchaseOrderNumber 的c $ c> SUM - 然后使用归属聚合函数

here i am selecting many col. but i want only

x.PurchaseOrderNumber

to be distinct.
i have used

SELECT  disctinct(x.PurchaseOrderNumber), x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,   
  x.POStatus,  
   x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost  

but it does not serve the purpose

i have even used group by

x.PurchaseOrderNumber

but it for that i have use aggregated function which i don't want to use !!

SELECT  x.PurchaseOrderNumber, x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,   
  x.POStatus,  
   x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost  
  FROM  wsm_View_PODashboardList AS X
  INNER JOIN dbo.wsm_WorkOrder_PurchaseOrder wp ON wp.PurchaseOrderNumber = X.PurchaseOrderNumber
  INNER JOIN dbo.wsm_WorkOrderSchedule wo ON wo.WONumber = wp.WONumber
  where X.POStatus  in ('Created','Authorised','Sent','Matched')  
and wo.WOStatus in ('New Request','Delayed','Scheduled','Work In Progress')  
and wo.EstCompletionDate <= getdate ()  
and wo.siteid in (select wsm_ContactSite.siteid from wsm_ContactSite where wsm_ContactSite.contactid = @ContactID)  ORDER BY X.DateRaised DESC

What I have tried:

i have used

SELECT  disctinct(x.PurchaseOrderNumber), x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,   
  x.POStatus,  
   x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost  

but it does not serve the purpose

i have even used group by

x.PurchaseOrderNumber

but it for that i have use aggregated function which i don't want to use !!

解决方案

First of all, you can't use DISTINCT on a single column. If your query returns multiple columns. DISTINCT always reduce the result to those rows, where the combination of all column values is unique.

I guess that the JOINs lead to multiple row results for each row in X because the joined tables have 1:n relations to it. If you want only a single row as result, you either have to modify the where clause so that it returns only a single match or think about what you want to return in PurchaseOrderNumber:
1) only the first match - then use TOP 1 clause to restrict the result to the first matching record
2) the MIN, MAX, AVERAGE or SUM of all PurchaseOrderNumbers in resulting rows - then use the belonging aggregate function

这篇关于单独的col。多选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！