Ansi SQL谜语
我有一个表(在Oracle 12中，但我只想使用ANSI sql)，定义和填充如下：
CREATE TABLE MYTABLE (GROOM VARCHAR2(50), BRIDE VARCHAR2(50), STATE VARCHAR2(50));
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('ALVIN','CARMEN','NJ');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('ALVIN','CARMEN','VA');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('ALVIN','ELEANOR','NJ');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('CARL','CARMEN','AL');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('CARL','ELEANOR','AL');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('DAVID','DIANA','NE');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('FRANK','DIANA','NV');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('MIKE',NULL,'RI');
INSERT INTO MYTABLE (GROOM,BRIDE,STATE) VALUES ('MIKE',NULL,'WI');我想用这个标准得出一个结果：
- 对于每个新郎(字母最低的姓名)，选择字母最低的“未结婚”新娘，以及字母最低的国家(如果新娘相同的话)。如果没有新娘，那就把她设置为空。
例如:最低的新郎是阿尔文，可以嫁给卡门(在新泽西州和弗吉尼亚州)或埃莉诺。结果是：
Alvin, Carmen, NJ现在最低的是卡尔，它可以嫁给卡门(但她已经和阿尔文结婚了)或埃莉诺。结果是：
Carl, Eleanor, AL最后，我想得到一个结果集：
Alvin, Carmen, NJ
Carl, Eleanor, AL
David, Diana, NE
Frank, NULL, NV
Mike, NULL, RI正如我所说的，我只想使用ANSI SQL (因此我使用的是Oracle这一事实与此无关)，不使用临时表、游标或表自联接。窗口函数没问题。
谢谢
回答 2
Stack Overflow用户
发布于 2019-05-23 15:05:12
嗯，首先，如果你能解释这些限制的性质，那就太好了。
例如，当有人想要在纯SQL中实现逻辑时，这可能是合理的，但是禁止自联接有什么意义呢？另外，您是否认为相关子查询来自与self联接相同的表？标量子查询呢？
看起来你想用分析函数(也称为窗口)来做一些技巧，但这是不可能的，因为在这种特殊情况下，你需要跟踪哪些新娘到目前为止已经被保留了，而解析函数没有任何状态。
在Oracle中，对于像您这样的任务有两种典型的方法(当您在行中“迭代”并维护一些“状态”时)。
- 递归子查询分解(也称为递归CTEs)
- 范本从句
让我从模型开始，尽管它是非常具体的Oracle特性
SQL> with t as
2 (
3 select *
4 from mytable
5 model
6 dimension by (groom, bride, state)
7 measures (0 reserved)
8 (
9 reserved[any,any,any] order by groom, bride, state
10 = case
11 -- current groom already has a bride
12 when max(reserved)[cv(groom), lnnvl(bride > cv(bride)), any] = 1
13 -- current bride already reserved for some groom
14 or max(reserved)[groom < cv(groom), cv(bride), any] = 1
15 then 0 else 1
16 end
17 )
18 )
19 select groom, bride, state
20 from t
21 where reserved = 1
22 union all
23 select groom, null, min(state)
24 from mytable
25 where groom not in (select groom from t where reserved = 1)
26 group by groom
27 order by 1;
GROOM BRIDE STATE
---------- ---------- ----------
ALVIN CARMEN NJ
CARL ELEANOR AL
DAVID DIANA NE
FRANK NV
MIKE RI在此解决方案列中，reserved用于标记新娘被“分配”的每一行。当最初引入model子句时，方法只在Oracle中起作用，从版本10g第1版开始。
第二个解决方案如下
SQL> with rec(groom, bride, state, reserved)
2 as (select min(groom),
3 min(bride) keep (dense_rank first order by groom),
4 min(state) keep (dense_rank first order by groom, bride),
5 min(bride) keep (dense_rank first order by groom)
6 from mytable
7 union all
8 select t.groom,
9 t.bride,
10 t.state,
11 r.reserved || '#' || t.bride
12 from rec r
13 cross apply
14 (select min(groom) groom,
15 min(bride) keep (dense_rank first order by groom) bride,
16 min(state) keep (dense_rank first order by groom, bride) state
17 from mytable
18 where groom > r.groom and instr(r.reserved, bride) = 0) t
19 where t.groom is not null)
20 cycle groom set c to 1 default 0
21 select groom, bride, state
22 from rec
23 union all
24 select groom, null, min(state)
25 from mytable
26 where groom not in (select groom from rec)
27 group by groom
28 order by 1;
GROOM BRIDE STATE
---------- ---------- ----------
ALVIN CARMEN NJ
CARL ELEANOR AL
DAVID DIANA NE
FRANK NV
MIKE RI在这个解决方案中，您可以去掉特定的Oracle keep dense_rank，并避免使用仅在12c中引入的cross apply。此外，您可以使用集合而不是串连字符串来跟踪保留的新娘，但是.同样，这是Oracle特有的解决方案。
但是，可以采用这个(只需稍作修改)来执行SQL server。
PS.
说到性能，递归解决方案在每次执行递归成员时都会扫描整个mytable，这使得它在任何相对较大的数据集中都不可行。
比方说，model可以计算数千行，但仍然可以计算每一行的聚合(max(reserved))，这在非SQL方法中是可以避免的。
Stack Overflow用户
发布于 2019-05-24 09:35:34
我正在使用下面的查询进行一些测试。我对此不太确定，所以请不要将其视为一种拟议的解决方案(如果您发现一些概念性或事实上的错误，将非常感谢)。
这样做的目的是确定一个有序的新郎和新娘名单(通过窗口功能)，然后如果新娘的级别高于耦合新郎的级别，则更改新娘的姓名，以便随后将她排除在新郎的分组之外，并使用一个min操作员。
SELECT GROOM
/*oracle specific string functions, every system has its own equivalent*/
, REPLACE(SUBSTR(COMPOUND, 1, INSTR(COMPOUND, ';', 1, 1) -1), 'ZZZZZZZZZZ', NULL) AS BRIDE
, SUBSTR(COMPOUND, INSTR(COMPOUND, ';', -1, 1) +1) AS STATE
FROM
(
SELECT GROOM
, MIN(CASE WHEN RANK_GROOM < RANK_BRIDE AND RANK_BRIDE <> 1 THEN 'ZZZZZZZZZZ'
ELSE BRIDE END || ';' || STATE) AS COMPOUND
FROM
(
SELECT
GROOM, COALESCE(BRIDE, 'ZZZZZZZZZZ') AS BRIDE, STATE
, DENSE_RANK() OVER (PARTITION BY GROOM ORDER BY BRIDE, STATE) AS RANK_GROOM
, DENSE_RANK() OVER (PARTITION BY BRIDE ORDER BY GROOM, STATE) AS RANK_BRIDE
FROM MYTABLE
) T1
GROUP BY GROOM
) T2附注：使用'ZZZZZZZZZZ‘这个名字显然不是’优雅‘，但在我的实际情况下，我使用的是数字，因此它可以被视为最大数值常量的替代。
编辑:这些天我做了很多测试，上面的查询似乎满足了我的所有需求。
https://stackoverflow.com/questions/56259711
复制相似问题
腾讯云开发者
