Taras's questions

我正在使用 FastAPI 开发应用程序。它包含一个函数，该函数使用此处sqlalchemy.sql.text所示的 将两个变量带入 SQL 查询。

SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER(:var1)
  AND (CASE
          WHEN TO_NUMBER(:var1) NOT IN (1460, 1478, 1481) THEN 'ATTR2 IS NULL'
          ELSE ATTR2 = TO_NUMBER(:var2)
      END);

因此，粘贴变量后，SQL 查询将如下所示

SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER('1460')
  AND (CASE
           WHEN TO_NUMBER('1460') NOT IN (1460, 1478, 1481) THEN 'ATTR2 IS NULL'
           ELSE ATTR2 = TO_NUMBER('000')
       END);

但是，我收到以下错误：

ORA-00905: 缺少关键字

所以，现在我想知道如何解决这个问题？以及是否可以将 CASE 语句嵌套到 WHERE 子句中？

m条SQL语句的逻辑如下：

我看过一些相关的主题，但它们没有帮助。

• SQL Server 2008 中“WHERE”子句中的“CASE”语句
• 嵌套案例陈述与多条件案例陈述
• SQL Server 中 WHERE 子句中的 case 语句

在 Oracle DB 中有一个包含一些数据的表：

请在此处找到一个最小的可重现示例：http ://www.sqlfiddle.com/#!4/19d625/1

现在我正在尝试通过"HNR"按顺序对这些数据进行排序25 > 25A > 25B > 25/3 。因此，最高优先级有一个简单的数字，然后是带有字母的相同数字（也已排序），最后是带有子编号的相同数字/（已排序）。

使用此代码

SELECT F.LAG,
       F.HNR,
       F.STREET
FROM DB.F F
ORDER BY F.LAG,
         F.STREET,
         TO_NUMBER(regexp_substr(F.HNR, '[[:digit:]]{1,4}')),
         TO_CHAR(regexp_substr(F.HNR, '[[:alpha:]]{1}')),
         TO_NUMBER(regexp_substr(F.HNR, '[^/]+', 1, 2));

我得到这个：

而不是这个：

我究竟做错了什么？

我见过SQL: ORDER BY 在特定列中使用子字符串……可能吗？并按字母排序，然后按数字排序，但我没有发现任何相关的东西。

在 Oracle DB 中有一个包含数据的表"YEARS"，请参见下面的表/图像

+-----+-----------+------------------+
| FID |    I0     |       BJA        |
+-----+-----------+------------------+
|   1 |     0     |       1949       |
|   2 |     0     |       1996       |
|   3 |     0     |       1970       |
|   4 |     1     |       1871       |  
|   4 |     0     |       1975       |
|   5 |     0     |       1967       |
|   6 |     0     |       1968       |
|   7 |     0     |       1926       |
|   7 |     1     |       2009       |
|   7 |     2     |       2012       |
|   7 |     3     |       2018       |
|   8 |     0     |       1956       |
|   9 |     0     |       1990       |
|  10 |     0     |       1953       |
|  10 |     1     |       1904       |
| ... |    ...    |       ...        |
+-----+-----------+------------------+

我试图创建一个小提琴（在这里），但它没有用......

CREATE TABLE YEARS (
    "FID" NUMBER(10,0) NOT NULL, 
    "I0" NUMBER(10,0) NOT NULL, 
    "BJA" NUMBER(10,0)
);

INSERT ALL
    INTO YEARS ("FID","I0","BJA") VALUES (1,0,1949)
    INTO YEARS ("FID","I0","BJA") VALUES (2,0,1996)
    INTO YEARS ("FID","I0","BJA") VALUES (3,0,1970)
    INTO YEARS ("FID","I0","BJA") VALUES (4,1,1871)
    INTO YEARS ("FID","I0","BJA") VALUES (4,0,1975)
    INTO YEARS ("FID","I0","BJA") VALUES (5,0,1967)
    INTO YEARS ("FID","I0","BJA") VALUES (6,0,1968)
    INTO YEARS ("FID","I0","BJA") VALUES (7,0,1926)
    INTO YEARS ("FID","I0","BJA") VALUES (7,1,2009)
    INTO YEARS ("FID","I0","BJA") VALUES (7,2,2012)
    INTO YEARS ("FID","I0","BJA") VALUES (7,3,2018)
    INTO YEARS ("FID","I0","BJA") VALUES (8,0,1956)
    INTO YEARS ("FID","I0","BJA") VALUES (9,0,1990)
    INTO YEARS ("FID","I0","BJA") VALUES (10,0,1953)
    INTO YEARS ("FID","I0","BJA") VALUES (10,1,1904)
    
SELECT 1 FROM dual;

我正在尝试执行以下查询：

SELECT YEARS.FID,
       MIN(YEARS.BJA) AS "CONSTRYEAR",
       LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC) AS "RECONSTRYEAR"
FROM DB.YEARS YEARS
GROUP BY YEARS.FID;

以及上述查询的输出：

但是，这不是我想要的结果……是的，我正在阅读文档source 1、source 2和source 3；并且我也看到了相关的线程如何一起使用 LISTAGG 和 WHERE以及如何使用带有唯一过滤器的 Oracle 的 LISTAGG 函数？.

我怎样才能得到这样的结果：

+-----+-----------+------------------+
| FID | CONSTRUCT |   RECONSTRYEAR   |
+-----+-----------+------------------+
|   1 |      1949 |                  |
|   2 |      1996 |                  |
|   3 |      1970 |                  |
|   4 |      1871 |             1975 |
|   5 |      1967 |                  |
|   6 |      1968 |                  |
|   7 |      1926 | 2009, 2012, 2018 |
|   8 |      1956 |                  |
|   9 |      1990 |                  |
|  10 |      1904 |             1953 |
+-----+-----------+------------------+

您看到"CONSTRUCT"列中的值被排除在"RECONSTRYEAR". 我不明白我需要在哪里放置一个WHERE YEARS.IO != 0子句LISTAGG，所以不会包括最低年份。

我有两张桌子，例如"Locations"和"Connections"

"Locations"有价值观

Id | Dimension
---------------    
 1 |    4
 2 |    8
 3 |    2

"Connections"维护属性

Origin | Destination | Value | Distance_KM
-------------------------------------------
   1   |      2      |  500  |     30
   1   |      3      |  100  |     20
   2   |      1      |  100  |     10
   2   |      3      |  300  |     10
   3   |      1      |  100  |     40

我想使用以下属性表创建输出。相应"In"地对应于"Destination"from"Connections"和"Out"to "Origin"。

Id | Dimension | In_Value | In_Count | In_Dist | Out_Value | Out_Count | Out_Dist
----------------------------------------------------------------------------------  
 1 |     4     |    200   |     2    |    50   |    600    |    2      |     50
 2 |     8     |    500   |     1    |    30   |    400    |    2      |     20 
 3 |     2     |    400   |     2    |    30   |    100    |    1      |     40

我可以用两个查询分别达到我争取的结果。

查询 1

SELECT C.Destination, SUM(C.Value) AS In_Value, COUNT(C.Destination) AS In_Count, SUM(C.Distance_KM) AS In_Dist
FROM Connections AS C
GROUP BY C.Destination

查询 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

尽管如此，应该只有一个查询可以解决我的问题，不是吗？我试过了但没有成功。

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.Out_Dist
FROM   Locations AS L
LEFT   JOIN (
             SELECT C.Destination,
                    SUM(C.Value) AS In_Value,
                    COUNT(C.Destination) AS In_Count,
                    SUM(C.Distance_KM) AS In_Dist
             FROM Connections AS C
             GROUP BY C.Destination
        ) ON L.Id = C.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count, 
                  SUM(C.Origine_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
        ) ON L.Id = C.Origin

基本上，我不知道我是否有资格在LEFT JOIN ON已经存在的查询中添加第二个，对LEFT JOIN ON吗？

参考：

• 两个 SQL LEFT JOINS 产生不正确的结果
• 在一个查询中对多个表进行多个左连接