存在优先级查询

本文介绍了存在优先级查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我被告知要执行类似于以下 的查询，我很好奇最快的查询是什么样的。 有什么想法吗？ Some_Id价值 1 A 1 B 1 C 2 C 2 A 2 B 3 B 3 C 4 C 5 Q 5 C 5 R 6 T 7 P 7 B 问题是我想为每个ID选择一条记录。如果存在值为A的 记录，那么我想为该ID选择该记录 。如果没有，我想为该ID选择值为''B'' 的记录（如果存在）。否则，只需给我 的第一条记录即存在的ID。结果集如下所示： Some_ID值 1 A 2 A 3 B 4 C 5 Q 6 T 7 B 感谢您的投入！

I''ve been presented with a task to do a query similar to the following and I was curious as to what the quickest query would look like. Anyone have any ideas?? Some_Id Value 1 A 1 B 1 C 2 C 2 A 2 B 3 B 3 C 4 C 5 Q 5 C 5 R 6 T 7 P 7 B The problem is that I want to select one record for each ID. If a record with the value of ''A'' exists, then I want to select that record for that ID. If not, I want to select the record with the value ''B'' for that ID if it exists. Otherwise, just give me the first record for that ID that exists. The result set would look like this: Some_ID Value 1 A 2 A 3 B 4 C 5 Q 6 T 7 B Thanks for your input!

推荐答案

2005年3月18日14:19:34 -0800，shootsie写道： （snip） On 18 Mar 2005 14:19:34 -0800, shootsie wrote: (snip) 否则，只需给我第一条记录即存在的ID。 Otherwise, just give me the first record forthat ID that exists.

嗨笋， 这里是你遇到问题的地方。表格中的数据是无序的，所以 没有第一这样的东西。直到你强制执行排序顺序。 我能得到的最接近的数据是 SELECT Some_ID，MIN（Value） 来自YourTable GROUP BY Some_ID 但是这不符合你所有预期的输出。 如果你能告诉我如何找出第一行对于一个ID，在表格中存储的数据的，我可以帮你写一个查询到 得到你需要的结果。 Best，Hugo - （删除_NO_和_SPAM_以获取我的电子邮件地址）

Hi shootsie, Here''s where you run into problems. The data in a table is unordered, so there is no such thing as a "first" row until you impose a sort order. The closest I can get with the data you have is SELECT Some_ID, MIN(Value) FROM YourTable GROUP BY Some_ID But that won''t match ALL your expected output. If you can tell me how to find out "the first row" for an ID, in terms of the data stored in the table, then I can help you write a query to get the results you need. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)

--- 1）将下面的结果集放入游标中 SELECT DISTINCT id 来自YourTable --- 2）将第二个结果集放入另一个游标中 SELECT id，valueStr from yourTable ORDER BY id，valueStr --- 3）获取两个光标值并进行比较。 你只需要通过循环获得价值 你想要的。 - 通过 www.sqlmonster --- 1) Put the result set below into a cursor SELECT DISTINCT id FROM YourTable --- 2) Put the 2nd result set into another cursor SELECT id, valueStr FROM yourTable ORDER BY id, valueStr --- 3) Fetch both cursor values and compare them. You just have to go thru the loop and get the values you want. -- Message posted via www.sqlmonster

2005年3月18日星期五23:37:46 GMT，Ervs Sevilla通过SQLMonster写道： On Fri, 18 Mar 2005 23:37:46 GMT, Ervs Sevilla via SQLMonster wrote: --- 1）将下面的结果集放入游标中 SELECT DISTINCT id FROM YourTable --- 2）将第二个结果集放入另一个游标中 SELECT id，valueStr FROM yourTable ORDER BY id，valueStr --- 3）获取两个游标值并进行比较他们只需要通过循环获得你想要的价值。 --- 1) Put the result set below into a cursorSELECT DISTINCT idFROM YourTable--- 2) Put the 2nd result set into another cursorSELECT id, valueStrFROM yourTableORDER BY id, valueStr--- 3) Fetch both cursor values and compare them. You just have to go thru the loop and get the values you want.

嗨Ervs， 为什么你真的想要使用两个游标来获得一个结果集 你可以用一个单一的方式生成更多更简单的quikcer 查询？ Best，Hugo - （删除_NO_和_SPAM_以获取我的电子邮件地址）

Hi Ervs, Why on earth would you ever want to use two cursors to get a result set that you could produce lots easier and lots quikcer with one single query? Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)