如何在SQL Server中使用不同的表的所有列

I want to get unique value form table. But all values should be unique.

我想得到独特的价值形式表。但所有价值观都应该是独特的。

So suggest how to get.

所以建议如何获得。

SELECT DISTINCT ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA';

3 个解决方案

#1

(one day I may be able to post comments!)

(有一天我可以发表评论!)

SQLFiddle to show normal, distinct and returning a single row

SQLFiddle显示正常,不同并返回单行

SELECT DISTINCT works fine but it doesn't work the way you want it to work. From the data you posted in the comment under Klas' answer, it's clear you're expecting a single result when there are data differences somewhere in the columns. For example

SELECT DISTINCT工作正常,但它不能按照您希望的方式工作。根据您在Klas回答中的评论中发布的数据,当列中某处存在数据差异时,很明显您期望获得单个结果。例如

    /Products/CELEBRITY/KANGANA

is completely DISTINCT from

完全是DISTINCT

    /Products/SALWAR

What you appear to be looking for cannot work with DISTINCT nor can it work with GROUP BY. Basically the only way two (or three, or ten, or 100) rows will become ONE row is if the data in ALL SEVEN COLUMNS in your SELECT are IDENTICAL.

您似乎正在寻找的内容无法与DISTINCT一起使用,也无法与GROUP BY一起使用。基本上,只有两行(或三行,或十行或100行)成为一行的唯一方法是,SELECT中所有七列中的数据都是IDENTICAL。

Take a step back and think about what it is, exactly, you're trying to achieve here.

退后一步,想想它是什么,确切地说,你正试图在这里实现。

#2

Are you saying that you want one record only? This is called aggregation. In case there are more records then one (three in your example), you would have to decide for each column, which value to show.

你是说你只想要一张唱片吗?这称为聚合。如果有更多记录,那么一个(在您的示例中为三个),您将必须为每个列决定要显示的值。

Which SubCat, which SmlImgPath, etc. do you want to see in your result line? The maximum value? The minimum? Or the string 'various'? An example:

您希望在结果行中看到哪个SubCat,SmlImgPath等?最大值?最低?或者字符串'各种'?一个例子:

SELECT 
    ProCode
  , CASE WHEN MIN(id) <> MAX(id) THEN 'various' ELSE MIN(id) END
  , MIN(SubCat)
  , MAX(SmlImgPath)
  , AVG(RupPrice)
  , AVG(ActualPrice)
  , MAX(ProName)
FROM product
WHERE ProCode='FZ10003-EBA'
GROUP BY ProCode;

#3

DISTINCT refers to all selected columns, so the answer is that your SELECT already does that.

DISTINCT指的是所有选定的列,所以答案是你的SELECT已经这样做了。

EDIT:

It seems your problem isn't related to DISTINCT. What you want is to get a single row when your search returns multiple rows.

看来你的问题与DISTINCT无关。你想要的是当你的搜索返回多行时获得一行。

If you don't care which row you get then you can use:

如果您不关心您获得哪一行,那么您可以使用:

MS SQL Server syntax:

MS SQL Server语法:

SELECT TOP 1  ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA';

MYSQL syntax:

SELECT ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA'
 LIMIT 1;

Oracle syntax:

SELECT ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA'
   AND rownum <= 1;

#1