转换选择查询以删除以删除额外的分类法条目

Question

我试着用这个查询显示已经复制的产品，term_taxonomy_id在我的web上对应于产品的库存(无、中、高、低)

我想把它转换成一个查询删除..。这将删除那些重复和只留下最新的term_order，因为如果产品有两个或更多的股票标签没有显示在网络上。

他们只需要有一个标签

• object_id=1很高，没有
• object_id=3没有，中等*

表: wp_term_relationships

+-----------+------------------+-----------+
| object_id | term_taxonomy_id | term_order|
+-----------+------------------+-----------+
| 1         | high             | 0         |
| 1         | none             | 1         |<---delete none
| 1         | bbb              | 10        |
| 1         | ccc              | 10        |
| 2         | high             | 0         |
| 2         | aaa              | 11        |
| 2         | bbb              | 11        |
| 2         | ccc              | 11        |
| 3         | none             | 0         |
| 3         | medium           | 1         |<---delete medium
| 3         | high             | 12        |<---delete high
| 4         | jjj              | 12        |
| 5         | kkk              | 12        |
| 5         | lll              | 12        |
| 5         | high             | 12        |
| *         | *                | *         |
+-----------+------------------+-----------+

SELECT object_id,term_taxonomy_id,term_order 
FROM wp_term_relationships 
WHERE (    term_taxonomy_id = none
        OR term_taxonomy_id = medium
        OR term_taxonomy_id = high
        OR term_taxonomy_id = low)
GROUP BY object_id
HAVING COUNT(*)>1

它只能有一种与库存相关的分类法，但是可以有更多的分类。这就是为什么我把aaa，bbb，ccc，zzz的唯一独家的是那些相关的高，中，低，没有股票。

我想删除所有与库存相关的记录(高、中、低、无)，只留下一个到最低的完成顺序，并留下所有与库存无关的项值。

我的平台是MySQL 5.7 (只能使用)

Answer 1

如果我正确地解除了任务：

1. 删除“错误”记录

DELETE t1.*
FROM wp_term_relationships t1
WHERE t1.term_taxonomy_id IN (`none`,'low','medium','high')
  AND EXISTS ( SELECT NULL
               FROM wp_term_relationships t2
               WHERE t2.term_taxonomy_id IN ('none','low','medium','high')
                 AND t1.object_id = t2.object_id 
                 AND t1.term_order > t2.term_order )

1. 禁止“错误”记录插入：

ALTER TABLE wp_term_relationships 
ADD COLUMN term_taxonomy_check VARCHAR(8) 
           AS (CASE WHEN term_taxonomy_id IN (`none`,'low','medium','high')
                    THEN term_taxonomy_id END),
UNIQUE INDEX idx_term_taxonomy_check (object_id, term_taxonomy_check);