选择性地忽略表中的元组
选择性地忽略表中的元组
提问于 2021-03-01 06:18:22
我们使用数据挖掘应用程序解决的问题最好用一个说明性的例子来描述。
这里有一个示例表
，其定义如下：
CREATE TABLE myTable
(
id INT UNSIGNED AUTO_INCREMENT,
colA VARCHAR(8),
colB VARCHAR(12),
revFlag CHAR(8), -- 'REVISED' or any other value, including NULL
PRIMARY KEY(id)
);任何具有
的价值
对象具有相同值的任何其他元组优先于
，只要
后一个元组的值不是
..。换句话说，当我们从表中选择行时，我们将跳过
值不是
，并且存在具有相同值的行。
对于它，
值为
..。
我们按如下方式填充该表：
INSERT INTO myTable(colA, colB) VALUES ('XSR0KA3V', 'OLD-O7RAR81X'),
('4F2JG71O', 'OLD-E71BE63L'), ('MML3HN48', 'OLD-B02PFB63'),
('5H0MWVSB', 'OLD-V70XLGHT'), ('JW73ZX0J', 'OLD-KME1GXQF'),
('XZV0EY0G', 'OLD-N06BURDF'), ('9HBQZ88V', 'OLD-76HSPUAL'),
('YI5AT6G4', 'OLD-X8KAWD7Z');
INSERT INTO myTable(colA, colB, revFlag) VALUES
('XSR0KA3V', 'NEW-O7RAR81X', 'REVISED'),
('MML3HN48', 'NEW-B02PFB63', 'REVISED'),
('9HBQZ88V', 'NEW-76HSPUAL', 'REVISED'),
('YI5AT6G4', 'NEW-X8KAWD7Z', 'XYZ'),
('Z8H2B5KY', '3RINJV0K', 'REVISED');自然
产生以下结果：
+----+----------+--------------+---------+
| id | colA | colB | revFlag |
+----+----------+--------------+---------+
| 1 | XSR0KA3V | OLD-O7RAR81X | NULL |
| 2 | 4F2JG71O | OLD-E71BE63L | NULL |
| 3 | MML3HN48 | OLD-B02PFB63 | NULL |
| 4 | 5H0MWVSB | OLD-V70XLGHT | NULL |
| 5 | JW73ZX0J | OLD-KME1GXQF | NULL |
| 6 | XZV0EY0G | OLD-N06BURDF | NULL |
| 7 | 9HBQZ88V | OLD-76HSPUAL | NULL |
| 8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL |
| 9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ |
| 13 | Z8H2B5KY | 3RINJV0K | REVISED |
+----+----------+--------------+---------+我们想要设计一个查询
注释
返回符合以下条件的任何元组：
修订
通过其他元组。在我们的示例中，输出应如下所示：
+----+----------+--------------+---------+
| id | colA | colB | revFlag |
+----+----------+--------------+---------+
| 2 | 4F2JG71O | OLD-E71BE63L | NULL |
| 4 | 5H0MWVSB | OLD-V70XLGHT | NULL |
| 5 | JW73ZX0J | OLD-KME1GXQF | NULL |
| 6 | XZV0EY0G | OLD-N06BURDF | NULL |
| 8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL |
| 9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ |
| 13 | Z8H2B5KY | 3RINJV0K | REVISED |
+----+----------+--------------+---------+回答 3
Stack Overflow用户
回答已采纳
发布于 2021-03-01 09:26:46
您可以使用
子句筛选出存在具有相同
值和
SELECT *
FROM myTable t1
WHERE NOT EXISTS (
SELECT *
FROM myTable t2
WHERE t2.id != t1.id AND t2.colA = t1.colA AND t2.revFlag = 'REVISED'
)输出：
id colA colB revFlag
2 4F2JG71O OLD-E71BE63L (null)
4 5H0MWVSB OLD-V70XLGHT (null)
5 JW73ZX0J OLD-KME1GXQF (null)
6 XZV0EY0G OLD-N06BURDF (null)
8 YI5AT6G4 OLD-X8KAWD7Z (null)
9 XSR0KA3V NEW-O7RAR81X REVISED
10 MML3HN48 NEW-B02PFB63 REVISED
11 9HBQZ88V NEW-76HSPUAL REVISED
12 YI5AT6G4 NEW-X8KAWD7Z XYZ
13 Z8H2B5KY 3RINJV0K REVISED关于dbfiddle的演示
Stack Overflow用户
发布于 2021-03-01 07:19:09
SELECT stuff
FROM somewhere x
LEFT
JOIN somewhere y
ON y.thing = x.thing
AND y.otherthing = x.otherthing
AND y.anotherthing > x.anotherthing
AND y.whatever = 'some value'
WHERE y.anotherthing .... ;Stack Overflow用户
发布于 2021-03-01 07:25:51
您可以使用IN子句
模式(MySQL v8.0)
CREATE TABLE table1 (
`id` INTEGER,
`colA` VARCHAR(8),
`colB` VARCHAR(12),
`revFlag` VARCHAR(7)
);
INSERT INTO table1
(`id`, `colA`, `colB`, `revFlag`)
VALUES
('1', 'XSR0KA3V', 'OLD-O7RAR81X', NULL),
('2', '4F2JG71O', 'OLD-E71BE63L', NULL),
('3', 'MML3HN48', 'OLD-B02PFB63', NULL),
('4', '5H0MWVSB', 'OLD-V70XLGHT', NULL),
('5', 'JW73ZX0J', 'OLD-KME1GXQF', NULL),
('6', 'XZV0EY0G', 'OLD-N06BURDF', NULL),
('7', '9HBQZ88V', 'OLD-76HSPUAL', NULL),
('8', 'YI5AT6G4', 'OLD-X8KAWD7Z', NULL),
('9', 'XSR0KA3V', 'NEW-O7RAR81X', 'REVISED'),
('18', 'XSR0KA3V', 'NEW-O7RAR81X', 'ZRNTR'),
('10', 'MML3HN48', 'NEW-B02PFB63', 'REVISED'),
('11', '9HBQZ88V', 'NEW-76HSPUAL', 'REVISED'),
('12', 'YI5AT6G4', 'NEW-X8KAWD7Z', 'XYZ'),
('13', 'Z8H2B5KY', '3RINJV0K', 'REVISED');查询#1
SELECT
`id`, `colA`, `colB`, `revFlag`
FROM
table1 t1
WHERE
(`colA` , IFNULL(`revFlag`,0)) IN
(SELECT
`colA`, `revFlag`
FROM
table1
WHERE
`revFlag` = 'REVISED' UNION SELECT
`colA`, IFNULL(MAX(`revFlag`),0)
FROM
table1
WHERE
`colA` NOT IN (SELECT
`colA`
FROM
table1
WHERE
`revFlag` = 'REVISED')
GROUP BY `colA`)
ORDER BY id;id | colA | colB | revFlag |
|---|---|---|---|
2 | 4F2JG71O | OLD-E71BE63L | |
4 | 5H0MWVSB | OLD-V70XLGHT | |
5 | JW73ZX0J | OLD-KME1GXQF | |
6 | XZV0EY0G | OLD-N06BURDF | |
9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
10 | MML3HN48 | NEW-B02PFB63 | REVISED |
11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ |
13 | Z8H2B5KY | 3RINJV0K | REVISED |
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66414426
复制相关文章
相似问题