我正在使用 Mysql 10.1.29-MariaDB 數據庫創建一個新表。

我要做的是為另一列中 company_name 值的每個重複出現增加一個數字。例如，對於表：

提供的兩個表的 order_placed 列應為空

# req 


+--------------------------------------------------+
|                        req                       |
+--------------------------------------------------+
| req_id | order_placed | contact_id | seq_records |
+--------+--------------+------------+-------------+
| 1      |         null |       1000 |        null |
+--------+--------------+------------+-------------+
| 2      |         null |       1002 |        null |
+--------+--------------+------------+-------------+
| 3      |         null |       1003 |        null |
+--------+--------------+------------+-------------+



+-------------------------------------------------------+
|                               contact                 |
+-------------------------------------------------------+
| contact_id | first_name | order_placed | company_name |  
+------------+------------+--------------+--------------+
| 1000       | dirt       |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1002       | dammy      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1003       | samii      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1004       | xenon      |         null |       Lenova | 
+------------+------------+--------------+--------------+


CREATE TABLE `req` (
 `req_id` bigint(20) NOT NULL,
  `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
  `contact_id` bigint(20) DEFAULT NULL,
  `seq_records` bigint(2) DEFAULT NULL,
 PRIMARY KEY (`req_id`),
 KEY `contact_id` (`contact_id`),
 CONSTRAINT `req_ibfk_10` FOREIGN KEY (`contact_id`) REFERENCES 
 `contact` (`contact_id`)
) 
/*!40101 SET character_set_client = @saved_cs_client */;

# contact

CREATE TABLE contact (
 contact_id bigint(20) NOT NULL,
 `first_name` varchar(100) COLLATE utf8_bin NOT NULL,
 `company_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
 `company_id` varchar(100) COLLATE utf8_bin DEFAULT NULL,
 `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
 PRIMARY KEY (`contact_id`),
 KEY `index_name` (`contact_id`),
) 

使用的查詢

DELIMITER $$
DROP procedure IF EXISTS `recordsequence` $$
CREATE procedure `recordsequence` ()
BEGIN

declare companyname varchar(250);
declare recordcount integer default 0;
declare duplcount integer default 0;
DECLARE vfinished INTEGER DEFAULT 0;
declare icount int default 0;
DEClARE records_cursor CURSOR FOR
select c.company_name,count(c.company_name),r.opr_id from contact c, request r where c.contact_id=r.contact_id and r.order_placed is null  group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor;
transfer_records: LOOP
FETCH records_cursor INTO companyname,duplcount;
IF vfinished = 1 THEN
LEAVE transfer_records;
END IF;

begin
set recordcount := duplcount;
set icount := 1;
DEClARE records_cursor1 CURSOR FOR
select c.contact_id,c.company_name from contact c, request r where c.company_name = companyname and c.contact_id=r.contact_id and r.order_placed is null group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor1;
transfer_records1: LOOP
FETCH records_cursor INTO contactid,companyname;
IF vfinished = 1 THEN
LEAVE transfer_records1;
END IF;

begin

UPDATE contact set reorder_sequence = icount where contact_id = contactid;
set icount := icount + 1;
end;

END LOOP transfer_records1;

CLOSE records_cursor1;

if(recordcount == icount) THEN

select concat('company_name Updated successfully', companyname);

else
select concat('company_name count mismatches please check', companyname);
end if

end

END LOOP transfer_records;

CLOSE records_cursor;

End$$
DELIMITER ;

上面的查詢是為下面的步驟創建一個過程

1. 使用游標獲取公司名稱的記錄和公司名稱的duplcount。
2. 獲取每個公司名稱的聯繫人 ID 並啟動更新語句的循環。
3. 使用下面給出的範例更新reorder_sequence表

預期結果

Eg:  contact table

+--------------------------------------------------------+
|                         contact                        |
+--------------------------------------------------------+
| order_placed | contact_id | company_name | seq_records |
+--------------+------------+--------------+-------------+
| null         |       1002 |         Asus | 1           |
+--------------+------------+--------------+-------------+
| null         |       1003 |         Asus | 2           |
+--------------+------------+--------------+-------------+
| null         |       1005 |         Asus | 3           |
+--------------+------------+--------------+-------------+
| null         |       1006 |       Lenova | 1           |
+--------------+------------+--------------+-------------+

像上面的例子一樣，我已經根據 company_name 列更新了 seq_records 列的值，前提是 order_placed 列都為空

錯誤

第二個選擇語句附近出現程式碼 1064 的語法錯誤。

==未使用; 只使用一個=.

一切都DECLAREs必須在程式碼之前。

當詢問 1064 時，請始終包含整個錯誤消息。“near …”通常是錯誤發生位置的絕佳線索。

分析/視窗函式:)請不要重新發明輪子:)

CREATE TABLE `req` (
 `req_id` bigint(20) NOT NULL,
  `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
  `contact_id` bigint(20) DEFAULT NULL,
  `seq_records` bigint(2) DEFAULT NULL
) 
;


CREATE TABLE contact (
 contact_id bigint(20) NOT NULL,
 `first_name` varchar(100) COLLATE utf8_bin NOT NULL,
 `company_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
 `company_id` varchar(100) COLLATE utf8_bin DEFAULT NULL,
 `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL
) ;

INSERT INTO req VALUES (1, NULL, 1000, NULL);
INSERT INTO req VALUES (2, NULL, 1002, NULL);
INSERT INTO req VALUES (3, NULL, 1003, NULL);

INSERT INTO contact VALUES (1000, 'dirt','Asus', 12, NULL);
INSERT INTO contact VALUES (1002, 'dammy', 'Asus', 12, NULL);
INSERT INTO contact VALUES (1003, 'samii', 'Asus', 12, NULL);
INSERT INTO contact VALUES (1004, 'xenon', 'Lenova', 1, NULL);

選擇：

SELECT order_placed
, contact_id
, company_name
, row_number() over (partition by company_name order by contact_id) as seq_records
FROM contact;

結果：

| order_placed | contact_id | company_name | seq_records |
| ------------ | ---------- | ------------ | ----------- |
|              | 1000       | Asus         | 1           |
|              | 1002       | Asus         | 2           |
|              | 1003       | Asus         | 3           |
|              | 1004       | Lenova       | 1           |

---

在 DB Fiddle 上查看（確保選擇 MySQL 8 .. v5 不支持視窗函式

引用自：https://dba.stackexchange.com/questions/241695