我有 2 个表,分别是 db_carts
和 db_carts_items
。
问题是他们可能有不同的sc_id
但相同的seller_id
.
正在尝试获取具有重复项的那些(我通过下面的查询实现了(,然后我需要更新重复的,以便对于每个不同的seller_id
,sc_id
是相同的(匹配的最高值,如果可能的话(。
我将如何实现这一点,最好是在mySQL或PHP中?
下面是查询:
SELECT * FROM db_carts
LEFT JOIN db_carts_items ON db_carts.sc_id=db_carts_items.sc_id
WHERE buyer_id="123456"
AND seller_id IN
( SELECT seller_id
FROM db_carts
WHERE buyer_id="123456"
GROUP BY seller_id having (count(*) > 1)
)
这给了我一个输出,如下所示:
sc_id | seller_id | buyer_id |
=============================================
15 50 123456
18 50 123456
23 70 123456
45 70 123456
我需要它更新db_carts_items
,使其看起来像:
sc_id | seller_id | buyer_id |
=============================================
18 50 123456
18 50 123456
45 70 123456
45 70 123456
从mySQL中可以做到这一点吗?如果没有,在 PHP 中?
对于每个重复项,我基本上需要根据该输出更新db_carts_items
表中的sc_id
值。
这样的事情应该可以解决问题:
with duplicates as (SELECT seller_id, max(sc_id) as maximum
FROM db_carts
WHERE buyer_id='123456'
GROUP BY seller_id
HAVING count(*)>1)
UPDATE db_carts, db_carts_items
set db_carts.sc_id=duplicates.maximum,
db_carts_items.sc_id=duplicates.maximum
FROM db_carts, db_carts_items, duplicates
WHERE db_carts.buyer_id='123456'
AND db_carts.seller_id=db_carts_items.seller_id
AND db_carts.seller_id=duplicates.seller_id;
编辑:似乎您无法在更新语句上使用。在这种情况下,我只会使用 PHP 来做。首先获取seller_id及其最大值
"SELECT seller_id, max(sc_id) as maximum
FROM db_carts
WHERE buyer_id='123456'
GROUP BY seller_id
HAVING count(*)>1"
然后对于每个返回的行进行这样的更新
"UPDATE db_carts, db_carts_items
set db_carts.sc_id=".$row['maximum'].",
db_carts_items.sc_id=".$row['maximum'].
"FROM db_carts, db_carts_items
WHERE db_carts.buyer_id='123456'
AND db_carts.seller_id=db_carts_items.seller_id
AND db_carts.seller_id=".$row['seller_id']