存在优先级查询 [英] Exists query with priorities
问题描述
我被告知要执行类似于以下
的查询，我很好奇最快的查询是什么样的。
有什么想法吗？
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 for
that 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）获取两个光标值并进行比较。
你只需要通过循环获得价值
你想要的。
-
通过 http://www.sqlmonster.com
--- 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 http://www.sqlmonster.com
2005年3月18日星期五23:37:46 GMT，Ervs Sevilla通过SQLMonster.com写道：
On Fri, 18 Mar 2005 23:37:46 GMT, Ervs Sevilla via SQLMonster.com 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 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.
嗨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)
这篇关于存在优先级查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
