MYSQL SQL(自)加入? [英] MYSQL SQL (self) join?
本文介绍了MYSQL SQL(自)加入?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用此示例数据集:
CREATE TABLE test. test2 (id VARCHAR(7), AA INT, BBB INT, CCC VARCHAR (12));
INSERT INTO test.test2 (id, AA, BBB,CCC) VALUES ( 'A123', 45, 123, '2011-03' );
INSERT INTO test.test2 (id, AA, BBB,CCC) VALUES ( 'A120', 52, 120, '2011-03' );
INSERT INTO test.test2 (id, AA, BBB,CCC) VALUES ( 'A133', 63, 133, '2011-03' );
INSERT INTO test.test2 (id, AA, BBB,CCC) VALUES ( 'D123', 34, 123, '2011-04' );
INSERT INTO test.test2 (id, AA, BBB,CCC) VALUES ( 'D120' ,32, 120, '2011-04' );
INSERT INTO test.test2 (id, AA, BBB,CCC) VALUES ( 'D140', 12, 140, '2011-04' );
我在找 3 列的表格.
Im look for table with 3 column.
Col A "Id" order as Desc from original.
Col A "Id" order as Desc from orginal.
Col B "Id2" 作为 col A 中的前一行或下一行 Id,其中 CCC 相同.
Col B "Id2" as previous or next row Id in col A where CCC are the same.
Id, Id2 CCC
A120 A123 '2011-03'
A123 A133 '2011-03'
A133 '2011-03'
D120 D123 '2011-04'
D123 D140 '2011-04'
D140 '2011-04'
或
Id, Id2 CCC
A120 '2011-03'
A123 A120 '2011-03'
A133 A123 '2011-03'
D120 '2011-04'
D123 D120 '2011-04'
D140 D123 '2011-04'
或
Id, Id2 CCC
A123 A120 '2011-03'
A133 A123 '2011-03'
D123 D120 '2011-04'
D140 D123 '2011-04'
我可以在连接表中添加一个 autocrement col,然后向上或向下使用 1、2、3 行吗?然后 id2 将基于此自动递增行?
Could I add an autocrement col to joined table, then use 1, 2, 3 rows up or down? The id2 would then be based on this autocrement row?
推荐答案
在 Marc 的基础上操作:
Operating on Marc's basis:
SELECT test.id, child.id, test.CCC
FROM test
LEFT JOIN test AS child
ON (test.CCC = child.CCC)
AND (test.id < child.id)
WHERE NOT EXISTS
( SELECT 1
FROM test AS middle
WHERE (test.CCC = middle.CCC)
AND (test.id < middle.id)
AND (middle.id < child.id)
)
OR child.id IS NULL
ORDER BY test.id
这可能对更复杂的查询有所帮助:
This might be helpful with more complex queries:
CREATE VIEW testWithRowId AS
( SELECT test.id
, COUNT(test.id) AS rownum
, test.CCC
FROM test
JOIN test AS child
ON (test.CCC = child.CCC)
AND (test.id >= child.id)
GROUP BY test.CCC
, test.id
)
然后使用它:
SELECT t1.id
, t2.id AS idShifted
, t1.CCC
FROM testWithRowId t1
LEFT JOIN testWithRowId t2
ON (t2.CCC = t1.CCC)
AND (t2.rownum = t1.rownum + 1) ---- replace this 1 with 2 or 3, etc
ORDER BY t1.CCC ---- for a shift 2 or shift 3, etc
, t1.rownum
这篇关于MYSQL SQL(自)加入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文