我想从下面的多个表中选择数据。我想为每组“dID”设置“gID”自动递增。我正在查询的多个表没有自动增量值。所以必须在飞行中创建gID字段。
dID gID bID data
=== === === ====
400 1 20 data1
400 2 21 data2
401 1 28 data3
402 1 52 data4
402 2 74 data5
402 3 75 data6
402 4 89 data7
我不知道怎样才能做到这一点。
我试过这么做,但似乎没用:
SELECT t1.dID, @i:=@i+1 AS gID, t2.bID, t2.data
FROM table1 t1, table2 t2, (SELECT @i:=0) m
WHERE t1.mID = t2.mID
ORDER BY t1.dID
最佳答案
不需要“@”变量。。。试试这个:
SELECT
a.dID,
COUNT(*) AS gID,
b.bID,
b.data
FROM
table1 a
INNER JOIN
table2 b ON a.mID = b.mID
INNER JOIN
(
SELECT a.dID, b.bID
FROM table1 a
INNER JOIN table2 b ON a.mID = b.mID
) c ON a.dID = c.dID AND b.bID >= c.bID
GROUP BY
a.dID,
b.bID
ORDER BY
a.dID,
gID
此解决方案还允许
bID
具有重复值(每个dID
组唯一)。关于mysql - MySQL SELECT行按组递增,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/11182817/