我根据一个表中的id收集相同的、具有不同类型值的相似数据:
+------+---------------+-----------------+----------------+
| id | TransID | Amount | InsertDate |
+------+---------------+-----------------+----------------+
| 1 | 1 | 12 | 19-03-2004 |
| 2 | 2 | 9 | 20-03-2004 |
| 3 | 3 | 4 | 21-03-2004 |
| 4 | 1 | 12 | 22-03-2004 |
| 5 | 2 | 9 | 23-03-2004 |
| 6 | 3 | 4 | 24-03-2004 |
| 7 | 1 | 12 | 25-03-2004 |
+------+---------------+-----------------+----------------+
当我根据TransID 1选择表时,我希望根据表的ID为记录提供一个唯一的自动递增ID。
如何做到这一点?所以结果是
+------+---------------+-----------------+----------------+---------------
| id | TransID | Amount | InsertDate | NewGeneratedID
+------+---------------+-----------------+----------------+-----------------
| 1 | 1 | 12 | 19-03-2004 | 1
| 4 | 1 | 12 | 22-03-2004 | 2
| 7 | 1 | 12 | 25-03-2004 | 3
+------+---------------+-----------------+----------------+ ---------------
当我只选择表的一个特定id,例如id为4时,它会给我2的NewGeneratedID,而不是1。
+------+---------------+-----------------+----------------+---------------
| 4 | 1 | 12 | 22-03-2004 | 2
+------+---------------+-----------------+----------------+
您可以使用以下查询来满足您的需求
SELECT t.id,t.TransID ,t.Amount,t.InsertDate ,(@num:=@num+1) AS
NewGeneratedID FROM table1 t cross join (SELECT @num:=0) AS dummy
where t.TransID=1 ORDER BY id;
我必须警告您,下一个查询效率低下,但它可以满足您的需求。
SELECT t.id, t.TransID ,t.Amount, t.InsertDate,
(SELECT COUNT(id) FROM table1 AS aux
WHERE t.TransID = aux.TransID and aux.id <= t.id)
FROM table1 t
WHERE t.TransID = 1 ORDER BY id;
如果您需要的流程在时间上至关重要,则不应该使用此查询。但是,如果您只对获取一条记录感兴趣,那么最好使用下面的查询,这样效率足够高。
SELECT t.id, t.TransID , t.Amount, t.InsertDate, COUNT(*)
FROM table1 t inner join table1 aux where t.TransID = aux.TransID
WHERE aux.id <= t.id and t.id = 4
GROUP BY t.id, t.TransID , t.Amount, t.InsertDate;
UPDATE:限制aux.id <= t.id
通过计算存在多少id较小的元素来在元素之间建立顺序。例如,对于同一事务,id4的行有一个id较小的行(1),id7
根据TransactionID 进行选择时,请使用以下查询
SELECT t.id,t.TransID ,t.Amount,t.InsertDate ,(@num:=@num+1) AS
NewGeneratedID FROM MyTable t
cross join (SELECT @num:=0) AS dummy where t.TransID=1 ORDER BY id;
对于id条件检查,请使用以下查询
select t1.* from
(SELECT t.id,t.TransID ,t.Amount,t.InsertDate,
(@num:=@num+1) AS NewGeneratedID
FROM MyTable t
cross join (SELECT @num:=0) AS dummy
where t.TransID=1 ORDER BY id) t1
where t1.id=4 ;
如果您计划为此添加一列,您可以使用MySQL INSERT TRIGGER,如下所示:
CREATE TRIGGER INSERT_Trigger BEFORE INSERT ON Your_Table
FOR EACH ROW BEGIN
SET NEW.NewGeneratedID = (SELECT COUNT(*)+1 FROM Your_Table WHERE TransID = NEW.TransID);
END
EDIT:策略是这样的,如果您为NewGeneratedID
使用物理列,那么对于表中的每个插入,计算TransID
(新行)已经存在的row数量,并将NewGeneratedID
(新行)设置为count+1。请记住,若您需要从表中删除,那个么必须有一个AFTER DELETE触发器才能使NewGeneratedID
一致。
这是我对你的问题的理解的建议:
如果为NewGeneratedID
生成的增量ID将是永久的:
- 更新表并插入那些新生成的id
- 之后,您现在可以使用给定的
id
和NewGeneratedID
如果为NewGeneratedID
生成的新id只是临时的:
- 选择并生成增量id后,将它们保存在临时表(请参阅CREATE table)
- 当数据保存在临时表中时,您现在可以使用给定
id
和NewGeneratedID
- 会话结束后清除或删除临时表中的数据
id字段是否已经足够唯一了?你为什么不用那个?如果你绝对需要一个新字段,你可以在id字段中添加一个或任何数字,并在每次插入时生成另一个值。即新字段=ID+常量。