我使用mysql和php與phpmyadmin。我在基於分區(qū)的計(jì)數(shù)器上遇到了一個(gè)主要問題，我想要改進(jìn)，但是我的sql知識(shí)阻止了我這樣做。我對(duì)此非常苦惱。
我希望我的表中的重複資料有一個(gè)計(jì)數(shù)器，如果這個(gè)值得到一個(gè)重複值，它會(huì)在值後面加上一個(gè)數(shù)字，然後從1重新開始，直到遇到一個(gè)新值，依此類推。以下是最終結(jié)果應(yīng)該如何顯示：
--------------------------- 1 | Josh-1 --------------------------- 2 | Josh-2 -------------------------- 3 | Josh-3 -------------------------- 4 | Josh-4 -------------------------- 5 | Fred-1 -------------------------- 6 | Fred-2 -------------------------- 7 | Fred-3 -------------------------
我以前在這裡得到過關(guān)於這個(gè)計(jì)數(shù)器的幫助，但它並沒有按照我希望的方式工作。而且，當(dāng)我按下表單中的插入按鈕後，重新載入phpmyadmin後，表格看起來像這樣：
--------------------------- 1 | Josh-1-1-1 --------------------------- 2 | Josh-2 -------------------------- 3 | Josh-3 -------------------------- 4 | Josh-4 -------------------------- 5 | Fred-1 -------------------------- 6 | Fred-2 -------------------------- 7 | Fred -------------------------
這裡發(fā)生了什麼事？我尋求幫助重寫的程式碼是這樣的：
UPDATE usermeta u1, (SELECT u1.`id`, CONCAT(u1.`name`,'-',ROW_NUMBER() OVER(PARTITION BY u1.`name` ORDER BY u1.`id`)) newname FROM usermeta u1 JOIN (SELECT `name` , COUNT(*) FROM usermeta GROUP BY `name` HAVING COUNT(*) > 1) u2 ON u1.`name` = u2.`name` ) u3 SET u1.`name` = u3.`newname` WHERE u1.`id` = u3.`id`
這段程式碼能被重寫成創(chuàng)建一個(gè)看起來像第一個(gè)表格範(fàn)例並且在phpmyadmin中正常工作的帶有編號(hào)名稱和重複項(xiàng)的表格嗎？非常感謝所有的幫助。請(qǐng)記住，我是一個(gè)困擾的中階sql用戶。
1
0
0
Possible solution - BEFORE INSERT trigger and additional MyISAM table with secondary autoincrement:
Working table
Additional table
Trigger
Insert rows - the AI index is added to the name. Check the result.
Look what is stored in additional table now.
db<>fiddle here
If your working table user exists already, and it contains some data, then you'd create additional table and fill it with data using, for example,
CREATE TABLE user_index (id INT AUTO_INCREMENT,
name VARCHAR(127),
PRIMARY KEY (name, id)) ENGINE=MyISAM
SELECT MAX(SUBSTRING_INDEX(name, '-', -1) + 0) id,
SUBSTRING_INDEX(name, '-', 1) name
FROM user
GROUP BY 2;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=38f028cfe1c9e85188ab0454463dcd78
