需要基于列的不同记录 [英] need distinct record based on a column

问题描述

我有一个2列的表格客户

customerno产品
1001手机
1002椅子
1001表
1004连衣裙
1005风扇
1002椅子
1003个周期

我需要查询以显示与产品不同的customerno

这样一来，无需重复(重复)客户no

例如:
我需要作为
的结果
客户没有产品
1001手机
1002椅子
1004连衣裙
1005风扇
1003个周期

在此先感谢..

i am having a table customer with 2 columns

customerno products
1001 mobile
1002 chair
1001 table
1004 dress
1005 fan
1002 chair
1003 cycle

i need a query to display different customerno with products

in that result no need to repeat(duplicate) the customerno

for example:
i need result as

customerno products
1001 mobile
1002 chair
1004 dress
1005 fan
1003 cycle

thanks in advance..

推荐答案

对我来说没有多大意义.为什么1001 mobile是预期的输出，而不是1001 table.我想说的是，第二列在您想要的输出中没有用，因为没人知道客户是否拥有0或更多其他产品，即客户拥有该特定产品.

那表示您可以使用嵌套的select语句来做到这一点，在该语句中仅占据第一行，因此是这样的:

Doesn''t make much sense to me. Why is 1001 mobile the expected output and not 1001 table. What I try to say is the second column is useless in the output you desire, as no-one know if the customer has 0 or more other products only that, that the customer has that particular product.

that said you can do it with a nested select statement where you take only the first row, so something like this:

SELECT customerno, (select TOP 1 products from myTable where customerno = t.customerno) as products from myTable t group by customerno

但是我认为您应该获得所有产品
(例如1001 mobile, table)，所以我个人会这样做:

However I would think that you should get all the products
(e.g. 1001 mobile, table) so I would personally do something like this:

SELECT customerno,
       REPLACE(RTRIM((SELECT [products] + ' ' FROM myTable
                      WHERE customerno = t.customerno) FOR XML PATH (''))),' ',', ') AS products
FROM myTable t GROUP BY customerno

这篇关于需要基于列的不同记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！