无法对重复的SQL Server 2000进行排序 [英] Can't sort duplicates SQL Server 2000

问题描述

嘿所有人 -


对具有共同字段的数据进行排序的最佳方法是什么？这是我的例子：



功能日期订单号

================= ==========================

FLCHS 20070814 073576

STRCH 20070814 073576

AA10 20070814 073576

我需要的只是基于功能层次结构的功能项目之一。它来自同一个订单，我必须弄清楚如何构建我的查询来说 IF功能共享相同的订单号，然后检查IF AA10和FLCHS THEN仅返回STRCH。该客户仅获得STRCH，当我在此订单号上执行COUNT时，它返回3。项目而不仅仅是1。它应该只是一个而不是3.我需要逻辑来过滤出FLCHS和AA10。任何想法？


谢谢

解决方案

嘿所有人 -


对具有公共字段的数据进行排序的最佳方法是什么？这是我的例子：



功能日期订单号

================= ==========================

FLCHS 20070814 073576

STRCH 20070814 073576

AA10 20070814 073576

我需要的只是基于功能层次结构的功能项目之一。它来自同一个订单，我必须弄清楚如何构建我的查询来说 IF功能共享相同的订单号，然后检查IF AA10和FLCHS THEN仅返回STRCH。该客户仅获得STRCH，当我在此订单号上执行COUNT时，它返回3。项目而不仅仅是1。它应该只是一个而不是3.我需要逻辑来过滤出FLCHS和AA10。任何想法？


谢谢

这样的事情能做到这一点吗？

展开 | 选择 | Wrap | 行号

类似这样的工作会做什么工作？

展开 | 选择 | Wrap | 行号

感谢您的回复 - 不确定FROM部分。 T1a，t1 b和t1 c代表什么？无论如何，你可以把它分解成简单的条款吗？

我认为在哪里逻辑 - （有点）我想说IF b。[Feature] =''AA10''和c。[Feature] =''FLCHS''和a。[Feature] ='''STRCH''那么仅返回''STRCH''


谢谢

我做的是加入同一张桌子（我的例子中的T1和你一样）没有提供你的桌子名称三次。一个人在SQL中通过为表分配不同的别名来做到这一点（在我的例子中是a，b和c）。

WHERE子句的第一部分（前两个术语）是连接的，它们是确保连接表中的每一行都属于同一个订单号。但是，如果你走到这一步，只会出现所有可能的功能组合。 （请尝试：SELECT * FROM T1 a，t1 b，t1 c

WHERE a。[订单号] = b。[订单号]和a。[订单号] = c。[订单数字]）

WHERE子句的下一部分确保只选择相关的一行（如果有的话）：如果它具有AA10特征（比如别名''b'） '但这并不重要），它有一个FLCHS功能（在我的情况下别名''c''），并且还有一个STRCH功能，返回该行。如果其中任何一个没有显示（即至少有一个丢失），则没有匹配的组合，因此该行不会显示。显然，如果组合确实存在，它只显示一次（在连接表中所有可能的排列（与相同的订单号相关）只出现一次）。

希望它有意义。

Hey Everyone -

What would be the best way to sort data that has common fields? Here is my example:


Feature Date Order Number
===========================================
FLCHS 20070814 073576
STRCH 20070814 073576
AA10 20070814 073576

What I need is just one of the Items under feature based on a Hierarchy of the features. Its from the same order and I have to figure out how to build my query to say " IF features share the same Order Number, THEN check IF AA10 and FLCHS THEN Return only the STRCH. This customer is only getting STRCH and when I do a COUNT on this Order Number it returns "3" Items instead of just "1" which it should be just one and not 3. I need Logic to filter out the FLCHS and AA10. Any Ideas?

Thanks

解决方案

Hey Everyone -

What would be the best way to sort data that has common fields? Here is my example:


Feature Date Order Number
===========================================
FLCHS 20070814 073576
STRCH 20070814 073576
AA10 20070814 073576

What I need is just one of the Items under feature based on a Hierarchy of the features. Its from the same order and I have to figure out how to build my query to say " IF features share the same Order Number, THEN check IF AA10 and FLCHS THEN Return only the STRCH. This customer is only getting STRCH and when I do a COUNT on this Order Number it returns "3" Items instead of just "1" which it should be just one and not 3. I need Logic to filter out the FLCHS and AA10. Any Ideas?

Thanks

Will something like this do the job?

Expand|Select|Wrap|Line Numbers

Will something like this do the job?

Expand|Select|Wrap|Line Numbers

Thanks for responding - Not sure about the FROM part. What does the T1a, t1 b, and t1 c represent? Anyway you could kind of break it down it simple terms for me?
I ge the "WHERE" logic - (sort of) I would like to say IF b.[Feature] = ''AA10'' and c.[Feature] = ''FLCHS'' and a.[Feature] = ''STRCH'' THEN return ONLY ''STRCH''

Thanks

What I did was to join the same table (T1 in my example as you did not provide the name of your table) three times. One does this in SQL by assigning different aliases to the table (a,b, and c in my example).
The first part of the WHERE clause (first two terms) are the join ones, they make sure each row in the join table belong to the same order number. But if you go this far only all possible combinations of features will show up. (Do try it: SELECT * FROM T1 a, t1 b, t1 c
WHERE a.[order number]=b.[order number] and a.[order number]=c.[order number])
The next part of the WHERE clause makes sure only the relevant one line is selected (if there''s any): if it has an AA10 feature (say in alias ''b'' but it doesn''t really matter), it has an FLCHS feature (in alias ''c'' in my case), and also has a STRCH feature, return the row. If any of those do not show up (i.e. at least one is missing) there will be no matching combo and thus the row will not show up. Obviously if the combo does exist it shows up only once (in the join table all possible permutations (related to the same order number) show up once and only once).
Hope it makes sense.

这篇关于无法对重复的SQL Server 2000进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！