帮助查询 [英] Help with query
问题描述
先谢谢你的帮助。
如下表所示：
创建表T1（
seq INTEGER NOT NULL，
值CHAR（1）NOT NULL）
插入表T1值（1，''A''）
INSERT INTO TABLE T1 VALUES（2，''A''）
插入表T1值（3，''A''）
插入表T1价值观（5，''B''）
插入表T1价值（6，''B''）
插入表T1价值（7，''' A''）
我想编写一个查询，它将返回每个值的开头和结果seq的结果。以上数据所需的结果
如下：
Beg End Value
1 3 A
5 6 B
7 7 A
我很想发布我一直在尝试的查询，因为我不是
想要玷污别人的想法，但我一直在接近这个想法，自我加入将成为解决方案的一部分。只需
使用max和min函数按值分组将不会返回
所需的结果。
再次感谢您的帮助。
Thanks in advance for your help.
Given the following table:
CREATE TABLE T1 (
seq INTEGER NOT NULL,
value CHAR(1) NOT NULL)
INSERT INTO TABLE T1 VALUES (1, ''A'')
INSERT INTO TABLE T1 VALUES (2, ''A'')
INSERT INTO TABLE T1 VALUES (3, ''A'')
INSERT INTO TABLE T1 VALUES (5, ''B'')
INSERT INTO TABLE T1 VALUES (6, ''B'')
INSERT INTO TABLE T1 VALUES (7, ''A'')
I would like to write a query that would return the beginning and
ending seq for each value. The desired results for the above data
would be as follows:
Beg End Value
1 3 A
5 6 B
7 7 A
I''m hesitant to post the queries I have been trying, because I don''t
want to taint someone''s thinking, but I have been approaching this with
the idea that a self join will be part of the solution. Simply
grouping by value using max and min functions will not return the
desired results.
Thanks again for your help.
推荐答案
Fred写道：
Fred wrote:
提前感谢您的帮助。
鉴于下表：
CREATE TABLE T1（
seq INTEGER NOT NULL，
值CHAR（1）NOT NULL）
INSERT INTO TABLE T1值（1，''A''）
插入表T1值（2，''A''）
插入表T1值（3，''A''）插入表T1值（5，''B''）
插入表T1值（6，''B''）
插入表T1值（7，''A'' ）
我想写一个查询，它将返回每个值的开头和结尾seq。以上数据的理想结果如下：
Beg End Value
1 3 A
5 6 B
7 7 A
我犹豫是否要发布我一直在尝试的查询，因为我不想玷污别人的想法，但我一直在接近这个问题
自我加入将成为解决方案的一部分。简单地使用max和min函数按值分组将不会返回所需的结果。
Thanks in advance for your help.
Given the following table:
CREATE TABLE T1 (
seq INTEGER NOT NULL,
value CHAR(1) NOT NULL)
INSERT INTO TABLE T1 VALUES (1, ''A'')
INSERT INTO TABLE T1 VALUES (2, ''A'')
INSERT INTO TABLE T1 VALUES (3, ''A'')
INSERT INTO TABLE T1 VALUES (5, ''B'')
INSERT INTO TABLE T1 VALUES (6, ''B'')
INSERT INTO TABLE T1 VALUES (7, ''A'')
I would like to write a query that would return the beginning and
ending seq for each value. The desired results for the above data
would be as follows:
Beg End Value
1 3 A
5 6 B
7 7 A
I''m hesitant to post the queries I have been trying, because I don''t
want to taint someone''s thinking, but I have been approaching this with
the idea that a self join will be part of the solution. Simply
grouping by value using max and min functions will not return the
desired results.
这将返回序列的下边界：
SELECT seq，value
FROM t1 AS o
什么不存在（选择1
FROM t1 AS i1
WHERE i1.seq = t1.seq - 1 AND
i1.value = t1.value）
现在你只需要扩展它来添加上边界：
SELECT seq AS开始，
（SELECT MIN（seq）
FROM t1 AS i2
WHERE i2.value = t1.value AND
i2.seq> = t1.value AND
- 没有继承人
NOT EXISTS（选择1
来自t1 AS i3
WHERE i3.seq = i2.seq + 1 AND
i3.value = t2.value））AS结束，
值
来自t1 AS o
什么不存在（SELECT 1
FROM t1 AS i1
WHERE i1.seq = t1.seq - 1 AND
i1.value = t1.value）
这是未经测试的，你可以简化这个......
-
Knut Stolze
DB2信息集成开发
IBM德国
This will return the lower boundary of a sequence:
SELECT seq, value
FROM t1 AS o
WHERE NOT EXISTS ( SELECT 1
FROM t1 AS i1
WHERE i1.seq = t1.seq - 1 AND
i1.value = t1.value )
Now you just extend this to add the upper boundary:
SELECT seq AS begin,
( SELECT MIN(seq)
FROM t1 AS i2
WHERE i2.value = t1.value AND
i2.seq >= t1.value AND
-- no successor
NOT EXISTS ( SELECT 1
FROM t1 AS i3
WHERE i3.seq = i2.seq + 1 AND
i3.value = t2.value ) ) AS end,
value
FROM t1 AS o
WHERE NOT EXISTS ( SELECT 1
FROM t1 AS i1
WHERE i1.seq = t1.seq - 1 AND
i1.value = t1.value )
This is untested and you can probably simplify this...
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Fred写道：
提前感谢您的帮助。
如下表所示：
创建表T1（
seq INTEGER NOT NULL，
值CHAR（1）NOT NULL）
插入表T1值（1，' 'A''）
插入表T1值（2，''A''）
插入表T1值（3，''A''）
插入表T1值（5，''B''）
插入表T1值（6，''B''）
插入表T1值（7，''A''）
我想写一个查询，它将返回每个值的开头和结尾seq。上述数据的预期结果如下：
Beg End Value
1 3 A
5 6 B
7 7 A
怎么样 ：
选择min（seq）为beg，max（seq）为end，value
来自t1
group by 3;
Art S. Kagel
我'我不愿意发布我一直在尝试的查询，因为我不想玷污别人的想法，但我一直在接近这个想法，即自我加入将是部分解决方案。简单地使用max和min函数按值分组将不会返回所需的结果。
再次感谢您的帮助。
Thanks in advance for your help.
Given the following table:
CREATE TABLE T1 (
seq INTEGER NOT NULL,
value CHAR(1) NOT NULL)
INSERT INTO TABLE T1 VALUES (1, ''A'')
INSERT INTO TABLE T1 VALUES (2, ''A'')
INSERT INTO TABLE T1 VALUES (3, ''A'')
INSERT INTO TABLE T1 VALUES (5, ''B'')
INSERT INTO TABLE T1 VALUES (6, ''B'')
INSERT INTO TABLE T1 VALUES (7, ''A'')
I would like to write a query that would return the beginning and
ending seq for each value. The desired results for the above data
would be as follows:
Beg End Value
1 3 A
5 6 B
7 7 A
How about:
select min(seq) as beg, max(seq) as end, value
from t1
group by 3;
Art S. Kagel
I''m hesitant to post the queries I have been trying, because I don''t
want to taint someone''s thinking, but I have been approaching this with
the idea that a self join will be part of the solution. Simply
grouping by value using max and min functions will not return the
desired results.
Thanks again for your help.
我错过了什么吗？为什么不是预期结果？ be：
Beg End Value
1 7 A
5 6 B
？
你在寻找每个CHAR的多个连续范围吗？
如果INSERT INTO TABLE T1 VALUES（2，''A''）不存在，你会吗？ />
期待：
1 1 A
3 3 A
5 6 B
7 7 A
？
或者最后一行是否真的应该是C？
"佛瑞德" < FR ********** @ yahoo.com>在消息中写道
新闻：11 ********************** @ h76g2000cwa.googlegr oups.com ...
Am I missing something? Why wouldn''t the "expected results" be:
Beg End Value
1 7 A
5 6 B
?
Are you looking for multiple contiguous ranges per "CHAR"?
If INSERT INTO TABLE T1 VALUES (2, ''A'') were not present, would you
expect:
1 1 A
3 3 A
5 6 B
7 7 A
?
Or should the last line have actually been "C"?
"Fred" <fr**********@yahoo.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...
提前感谢您的帮助。
给出下表：
CREATE TABLE T1（
seq INTEGER NOT NULL，
值CHAR（1）插入表T1值（1，'A''）
插入表T1值（2，''A''）
插入表中T1值（3，''A''）
插入表T1值（5，''B''）
插入表T1值（6，''B''）插入表T1值（7，''A''）
我想编写一个查询，它将返回每个值的开头和结束序号。以上数据的理想结果如下：
Beg End Value
1 3 A
5 6 B
7 7 A
我犹豫是否要发布我一直在尝试的查询，因为我不想玷污别人的想法，但我一直在接近这个问题
自我加入将成为解决方案的一部分。简单地使用max和min函数按值分组将不会返回所需的结果。
再次感谢您的帮助。
Thanks in advance for your help.
Given the following table:
CREATE TABLE T1 (
seq INTEGER NOT NULL,
value CHAR(1) NOT NULL)
INSERT INTO TABLE T1 VALUES (1, ''A'')
INSERT INTO TABLE T1 VALUES (2, ''A'')
INSERT INTO TABLE T1 VALUES (3, ''A'')
INSERT INTO TABLE T1 VALUES (5, ''B'')
INSERT INTO TABLE T1 VALUES (6, ''B'')
INSERT INTO TABLE T1 VALUES (7, ''A'')
I would like to write a query that would return the beginning and
ending seq for each value. The desired results for the above data
would be as follows:
Beg End Value
1 3 A
5 6 B
7 7 A
I''m hesitant to post the queries I have been trying, because I don''t
want to taint someone''s thinking, but I have been approaching this with
the idea that a self join will be part of the solution. Simply
grouping by value using max and min functions will not return the
desired results.
Thanks again for your help.
这篇关于帮助查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
