sql - 只打印列中的最大值

Question

我有一个脚本，我在其中输入了数据，但有一个问题我无法解决。我有重复的值。都是因为 INFO Hook 。在 INFO 列中，我可能什么也没有，也可能有一些值。然后我只打印意思(这是我决定的)。但是，当有两门类(class)时，一门少一门，这可能是最大的。感谢您的帮助) https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6edb8b040779522cca52802748bc3918现在答案是这个

+ --------+---------------+--------------------------------------+-------------------+| ID      | VALUE_NUMBER  | VALUE_STRING                         | INFO              |+ --------+---------------+--------------------------------------+-------------------+| 4680828 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | 95888137 MY_IDENT || 4680704 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | MY_IDENT          || 4680828 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | MY_IDENT          || 4680704 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | 95888137 MY_IDEN  |+ --------+---------------+--------------------------------------+-------------------+

我想这样走

+ --------+---------------+--------------------------------------+-------------------+| ID      | VALUE_NUMBER  | VALUE_STRING                         | INFO              |+ --------+---------------+--------------------------------------+-------------------+| 4680828 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | 95888137 MY_IDENT || 4680704 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | 95888137 MY_IDENT |+ --------+---------------+--------------------------------------+-------------------+

Answer 1

聚合(例如max)通常有助于:

select id, value_number, value_string, 
       max(info) as info                     --> this
from your_table
group by id, value_number, value_string;

您的查询(来自 dBFiddle)，已修复:

WITH txn
     AS (  SELECT TRANSACTION_VALUE_NUMBER,
                  READER_VALUE_STRING,
                  ID,
                  MAX (info) AS info
             FROM (  SELECT o.VALUE_NUMBER AS transaction_value_number,
                            i.VALUE_STRING AS reader_value_string,
                            o.id,
                            TRIM (
                                  v.VALUE_STRING
                               || ' '
                               || v.VALUE_NUMBER
                               || ' '
                               || x.VALUE_STRING
                               || ' '
                               || c.VALUE_STRING)
                               AS INFO
                       FROM IOT_STREAM_ANALYTICS_LOG_REPRESENTATION  o
                            LEFT JOIN IOT_STREAM_ANALYTICS_LOG_REPRESENTATION  i ON o.parent = i.parent
                            LEFT JOIN IOT_STREAM_ANALYTICS_LOG_REPRESENTATION  v
                               ON     i.parent = v.parent
                                  AND v.KEY = 'truck1'
                            LEFT JOIN IOT_STREAM_ANALYTICS_LOG_REPRESENTATION  x
                               ON     i.parent = x.parent
                                  AND x.KEY = 'item_name'
                            LEFT JOIN IOT_STREAM_ANALYTICS_LOG_REPRESENTATION  c
                               ON     x.parent = c.parent
                                  AND c.KEY = 'truck2'
                      WHERE     i.key = 'reader'
                            AND i.VALUE_STRING =
                                   'aedef9f9-2e82-11ea-80cb-e03f49835a25'
                            AND o.key = 'transaction'
                            AND o.value_number <> 0
                            AND TRIM (
                                      v.VALUE_STRING
                                   || ' '
                                   || v.VALUE_NUMBER
                                   || ' '
                                   || x.VALUE_STRING
                                   || ' '
                                   || c.VALUE_STRING)
                                   IS NOT NULL
                   GROUP BY o.id,
                            o.VALUE_NUMBER,
                            i.VALUE_STRING,
                            TRIM (
                                  v.VALUE_STRING
                               || ' '
                               || v.VALUE_NUMBER
                               || ' '
                               || x.VALUE_STRING
                               || ' '
                               || c.VALUE_STRING))
         GROUP BY TRANSACTION_VALUE_NUMBER, READER_VALUE_STRING, ID)
  SELECT rep.id,
         rep.VALUE_NUMBER,
         txn.reader_value_string AS VALUE_STRING,
         min(txn.INFO) info
    FROM IOT_STREAM_ANALYTICS_LOG_REPRESENTATION  rep JOIN txn ON rep.VALUE_NUMBER = txn.transaction_value_number
   WHERE     LOWER (rep.key) = 'transaction'
         AND rep.value_number <> 0
GROUP BY rep.id,
         rep.value_number,
         txn.reader_value_string;

[编辑]

关于您发表的评论:

VALUE NUMBER may be different but the INFO must be the same

如果您在其分析 形式中应用MIN(并且 - 因此 - 删除整个 GROUP BY 子句)，最后几行 (以及结果)在您的查询中将如下所示:

 <snip>
 55    SELECT rep.id,
 56           rep.VALUE_NUMBER,
 57           txn.reader_value_string AS VALUE_STRING,
 58           min(txn.INFO) over (partition by txn.reader_value_string) info
 59      FROM iot  rep JOIN txn ON rep.VALUE_NUMBER = txn.transaction_value_number
 60     WHERE     LOWER (rep.key) = 'transaction'
 61           AND rep.value_number <> 0;

        ID VALUE_NUMBER    VALUE_STRING                             INFO
---------- --------------- ---------------------------------------- --------------------
   4680828 1578109515971   aedef9f9-2e82-11ea-80cb-e03f49835a25     95888137 MY_IDENT
   4680704 1578109515974   aedef9f9-2e82-11ea-80cb-e03f49835a25     95888137 MY_IDENT

SQL>

[编辑#2]

有了 dbFiddle 上的新数据，这会有帮助吗？删除解析函数，包括两个MIN(一个用于REP.ID，另一个用于TXN.INFO):

 <snip>
 54    SELECT MIN (rep.id) id,
 55           rep.VALUE_NUMBER,
 56           txn.reader_value_string AS VALUE_STRING,
 57           MIN (txn.INFO) info
 58      FROM IOT_STREAM_ANALYTICS_LOG_REPRESENTATION rep
 59           LEFT JOIN txn ON rep.VALUE_NUMBER = txn.transaction_value_number
 60     WHERE     LOWER (rep.key) = 'transaction'
 61           AND rep.value_number <> 0
 62           AND rep.id NOT IN (SELECT o.id
 63                                FROM IOT_STREAM_ANALYTICS_LOG_REPRESENTATION o,
 64                                     IOT_STREAM_ANALYTICS_LOG_REPRESENTATION parent
 65                               WHERE     o.parent = parent.parent
 66                                     AND parent.key = 'reader'
 67                                     AND parent.VALUE_STRING !=
 68                                            txn.reader_value_string)
 69  GROUP BY rep.value_number, txn.reader_value_string;

        ID VALUE_NUMBER    VALUE_STRING                             INFO
---------- --------------- ---------------------------------------- --------------------
   4680704 1578109515974   aedef9f9-2e82-11ea-80cb-e03f49835a25     95888137 MY_IDENT
   4680501 1578109515974   aedef9f9-2e82-11ea-80cb-e03f49835a26     95888138

SQL>