困难的查询：这在SQL中可能吗？ [英] Difficult Query: is this possible in SQL?
问题描述
假设我有下表：
CREATE TABLE（int level，color varchar，length int，width int，height
int）
它有以下行
1，RED，8,10,12
2，NULL ，NULL，NULL，20
3，NULL，9,82,25
4，" BLUE"，NULL，67，NULL
5，GRAY，NULL NULL，NULL
我想写一个查询，它将返回一个视图折叠起来
" bottom-到顶部的"按级别排序（级别1是顶部，级别5是底部）
所以我想要一个将返回的查询
灰色，9 ，67,25
原则是从每列的底层向上看
我们首先看到灰色为灰色，9为长度，67为宽度，25为
身高。换句话说，较低级别中的任何非NULL行都会覆盖更高级别设置的值。
如果不使用存储，这在SQL中是否可行程序？
谢谢！
- 罗伯特
" Robert Brown" < RO ************* @ yahoo.com>在消息中写道
news：24 ************************** @ posting.google.c om ...假设我有下表：
CREATE TABLE（int level，color varchar，length int，width int，height
int）
<它有以下几行
1，RED，8,10,12
2，NULL，NULL，NULL，20
3，NULL，9 ，82,25
4，BLUE，NULL，67，NULL
5，GRAY，NULL NULL，NULL
我想写一个查询将返回从自下而上的折叠视图按级别排序（级别1是顶级，级别5是底部）
所以我想要一个将返回的查询
GRAY，9,67,25
原则是从每一列的底层向上看
我们首先看到灰色为灰色，长度为9，宽度为67，高度为25。换句话说，较低级别的任何非NULL行都会覆盖更高级别设置的值。
在不使用存储过程的情况下，SQL中是否可以这样做？
，T为（
选择1 id，2 a，3 b，0 c，4 d来自双
union all
选择2,6,2,5来自双
联盟所有
选择3,1,7,9,0来自双
联合所有
选择4,0,2,0,0从双
）选择不同的
（选择a来自T，其中id =（从T中选择max（id），其中a！= 0）），
（从T中选择b，其中id =（从T中选择max（id），其中b！= 0）），
（从T中选择c，其中id =（从T中选择max（id），其中c！= 0）），
（从T中选择d） id =（从T中选择max（id），其中d！= 0））
来自T
" Robert Brown" < RO ************* @ yahoo.com>在消息中写道
news：24 ************************** @ posting.google.c om ...假设我有下表：
CREATE TABLE（int level，color varchar，length int，width int，height
int）
<它有以下几行
1，RED，8,10,12
2，NULL，NULL，NULL，20
3，NULL，9 ，82,25
4，BLUE，NULL，67，NULL
5，GRAY，NULL NULL，NULL
我想写一个查询将返回从自下而上的折叠视图按级别排序（级别1是顶级，级别5是底部）
所以我想要一个将返回的查询
GRAY，9,67,25
原则是从每一列的底层向上看
我们首先看到灰色为灰色，长度为9，宽度为67，高度为25。换句话说，较低级别的任何非NULL行都会覆盖更高级别设置的值。
在不使用存储过程的情况下，SQL中是否可以这样做？
谢谢！
- Robert
创建表T
（
level INT NOT NULL PRIMARY KEY，
color VARCHAR（10）NULL，
length INT NULL，
width INT NULL，
身高INT NULL
）
- 选项1
SELECT（选择颜色来自T WHERE level = M .LC）AS颜色，
（SELECT长度FROM T WHERE level = M.LL）AS长度，
（SELECT width FROM T WHERE level = M.LW）AS宽度，
（选择高度FROM T WHERE等级= M.LH）AS高度
FROM（选择
MAX（颜色不是的情况） NULL THEN级别END）AS LC，
MAX（长度为非NULL，然后为等级END的情况）AS LL，
MAX（当宽度不为空时，等级为END ）AS LW，
MAX（高度不是空的情况N等级END）AS LH
来自T）AS M
- 选项2
SELECT MIN（T形时） level = M.LC THEN T.color END）AS颜色，
MIN（当T.level = M.LW THEN T.width END时的情况）AS宽度，
FROM（选择
MAX（情况下颜色不为空，然后为等级END）AS LC，
MAX（长度为非空时为水平END的情况）AS LL，
MAX（当宽度不为空时，等级为END）的情况AS WW，
MAX（如果高度不为空，则为等级END的情况）AS LH
来自T）AS M
INNER JOIN T
ON T.level IN（M.LC，M.LL，M.LW，M .LH）
-
JAG
DROP TABLE Foobar;
CREATE TABLE Foobar
（等级INTEGER NOT NULL PRIMARY KEY，
颜色VARCHAR（10），
长度INTEGER，
宽度INTEGER，
hgt INTEGER）;
INSERT INTO Foobar VALUES（1，''RED''，8,10,12）;
INSERT INTO Foobar VALUES（2，NULL，NULL，NULL，20）;
INSERT INTO Foobar VALUES（3，NULL，9,82,25）;
插入Foobar值（4，''BLUE''，NULL，67，NULL）;
INSERT INTO Foobar VALUES（5，''GRAY''，NULL，NULL，NULL）;
SELECT
COALESCE（F5.color，F4.color，F3.color，F2.color，F1.color）AS颜色，
COALESCE（ F5.length，F4.length，F3.length，F2.length，F1.length）AS长度，
COALESCE（F5.width，F4.width，F3.width，F2.width，F1 .width）AS宽度，
COALESCE（F5.hgt，F4.hgt，F3.hgt，F2.hgt，F1.hgt）AS hgt
来自Foobar AS F1 ，Foobar AS F2，Foobar AS F3，
Foobar AS F4，Foobar AS F5
WHERE F1.level = 1
AND F2.level = 2
AND F3.level = 3
AND F4.level = 4
AND F5.level = 5;
suppose I have the following table:
CREATE TABLE (int level, color varchar, length int, width int, height
int)
It has the following rows
1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL
I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)
So I want a query that will return
GRAY, 9, 67, 25
The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.
Is this possible in SQL without using stored procedures?
Thanks!
- Robert
"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...suppose I have the following table:
CREATE TABLE (int level, color varchar, length int, width int, height
int)
It has the following rows
1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL
I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)
So I want a query that will return
GRAY, 9, 67, 25
The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.
Is this possible in SQL without using stored procedures?
with T as (
select 1 id,2 a,3 b,0 c,4 d from dual
union all
select 2, 6,2,0,5 from dual
union all
select 3, 1,7,9,0 from dual
union all
select 4, 0,2,0,0 from dual
) select distinct
(select a from T where id=(select max(id) from T where a!=0) ),
(select b from T where id=(select max(id) from T where b!=0) ),
(select c from T where id=(select max(id) from T where c!=0) ),
(select d from T where id=(select max(id) from T where d!=0) )
from T
"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...suppose I have the following table:
CREATE TABLE (int level, color varchar, length int, width int, height
int)
It has the following rows
1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
4, "BLUE", NULL, 67, NULL
5, "GRAY", NULL NULL, NULL
I want to write a query that will return me a view collapsed from
"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)
So I want a query that will return
GRAY, 9, 67, 25
The principle is that looking from the bottom level up in each column
we first see GRAY for color, 9 for length, 67 for width, 25 for
height. In other words, any non-NULL row in a lower level overrides
the value set at a higher level.
Is this possible in SQL without using stored procedures?
Thanks!
- Robert
CREATE TABLE T
(
level INT NOT NULL PRIMARY KEY,
color VARCHAR(10) NULL,
length INT NULL,
width INT NULL,
height INT NULL
)
-- Option 1
SELECT (SELECT color FROM T WHERE level = M.LC) AS color,
(SELECT length FROM T WHERE level = M.LL) AS length,
(SELECT width FROM T WHERE level = M.LW) AS width,
(SELECT height FROM T WHERE level = M.LH) AS height
FROM (SELECT
MAX(CASE WHEN color IS NOT NULL THEN level END) AS LC,
MAX(CASE WHEN length IS NOT NULL THEN level END) AS LL,
MAX(CASE WHEN width IS NOT NULL THEN level END) AS LW,
MAX(CASE WHEN height IS NOT NULL THEN level END) AS LH
FROM T) AS M
-- Option 2
SELECT MIN(CASE WHEN T.level = M.LC THEN T.color END) AS color,
MIN(CASE WHEN T.level = M.LL THEN T.length END) AS length,
MIN(CASE WHEN T.level = M.LW THEN T.width END) AS width,
MIN(CASE WHEN T.level = M.LH THEN T.height END) AS height
FROM (SELECT
MAX(CASE WHEN color IS NOT NULL THEN level END) AS LC,
MAX(CASE WHEN length IS NOT NULL THEN level END) AS LL,
MAX(CASE WHEN width IS NOT NULL THEN level END) AS LW,
MAX(CASE WHEN height IS NOT NULL THEN level END) AS LH
FROM T) AS M
INNER JOIN T
ON T.level IN (M.LC, M.LL, M.LW, M.LH)
--
JAG
DROP TABLE Foobar;
CREATE TABLE Foobar
(level INTEGER NOT NULL PRIMARY KEY,
color VARCHAR(10),
length INTEGER,
width INTEGER,
hgt INTEGER);
INSERT INTO Foobar VALUES (1, ''RED'', 8, 10, 12);
INSERT INTO Foobar VALUES (2, NULL, NULL, NULL, 20);
INSERT INTO Foobar VALUES (3, NULL, 9, 82, 25);
INSERT INTO Foobar VALUES (4, ''BLUE'', NULL, 67, NULL);
INSERT INTO Foobar VALUES (5, ''GRAY'', NULL, NULL, NULL);
SELECT
COALESCE (F5.color, F4.color, F3.color, F2.color, F1.color) AS color,
COALESCE (F5.length, F4.length, F3.length, F2.length, F1.length) AS length,
COALESCE (F5.width, F4.width, F3.width, F2.width, F1.width) AS width,
COALESCE (F5.hgt, F4.hgt, F3.hgt, F2.hgt, F1.hgt) AS hgt
FROM Foobar AS F1, Foobar AS F2, Foobar AS F3,
Foobar AS F4, Foobar AS F5
WHERE F1.level = 1
AND F2.level = 2
AND F3.level = 3
AND F4.level = 4
AND F5.level = 5;
这篇关于困难的查询：这在SQL中可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
