我有一个脚本，在其中输入了数据，但是有一个我无法解决的问题。我有重复的值。都是因为INFO钉。在“信息”列中，我可能什么都没有，也没有值。然后，我仅打印含义(这就是我所决定的)。但是，可能有两个路线，一个路线少一点，另一条路线多一点，只花最大的钱。感谢您的帮助)
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=6edb8b040779522cca52802748bc3918
现在的答案是这样
+ -------- + --------------- + ------------------------ -------------- + ------------------- +
| ID | VALUE_NUMBER | VALUE_STRING |信息|
+ -------- + --------------- + ------------------------ -------------- + ------------------- +
| 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 |信息|
+ -------- + --------------- + ------------------------ -------------- + ------------------- +
| 4680828 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | 95888137 MY_IDENT |
| 4680704 | 1578109515974 | aedef9f9-2e82-11ea-80cb-e03f49835a25 | 95888137 MY_IDENT |
+ -------- + --------------- + ------------------------ -------------- + ------------------- +
最佳答案
聚合(例如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;
[编辑]
关于您发表的评论:
如果您以分析形式应用
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>
[EDIT＃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>
关于sql - 仅打印列中的最大值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59875176/