PHP mysql指定行号_php – mysql交换带行号的项目

这是我的表格结构,在这里我需要交换项目.这意味着,你可以看到类型3总是配对(类型3项目总是配对).

我刚刚命名配对项目,以便理解对中的第一个是主,第二个是子.因此,该对的主人不应该来5,10和15个位置

如果到了那个地方我需要将下一个项目交换到那个地方(neaxt项目将是sub它不应被视为下一个项目)

例如

pid 10 (comes in 10 position) i need to swap it like this

pid type name

.. .. ..

10 2 B2

11 3 E1(master)

12 3 A2(sub)

.. .. ..

表

pid type pname

1 1 A

2 1 B

3 2 C

4 3 D(mater)

5 3 E(sub)

6 1 A1

7 2 B1

8 1 C1

9 2 D1

10 3 E1(master)

11 3 A2(sub)

12 2 B2

13 1 C2

14 2 D2

15 1 E3

截图

为了进一步的帮助

我给你表结构和测试数据,如果你有我的想法与我分享！

创建查询

CREATE TABLE IF NOT EXISTS `table_swap1` (

`pid` int(11) NOT NULL AUTO_INCREMENT,

`type` int(11) NOT NULL,

`name` varchar(50) NOT NULL,

PRIMARY KEY (`pid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17;

插入查询

INSERT INTO `table_swap1` (`pid`, `type`, `name`) VALUES

(1, 1, 'A'),(2, 1, 'B'),(3, 2, 'D'),(4, 3, 'E(master)'),

(5, 3, 'f(sub)'),(6, 1, 'A1'),(7, 2, 'B1'),(8, 1, 'C1'),

(9, 2, 'D1'),(10, 3, 'E1(master)'),(11, 3, 'A2(sub)'),(12, 2, 'B2'),

(13, 1, 'C2'),(14, 2, 'D2'), (15, 1, 'E2');

我的工作和结果

SELECT aa.pid, (

CASE aa.pid

WHEN bb.apid

THEN bb.atype

WHEN bb.bpid

THEN bb.btype

WHEN bb.cpid

THEN bb.ctype

ELSE aa.type

END

)

TYPE , (

CASE aa.pid

WHEN bb.apid

THEN bb.aname

WHEN bb.bpid

THEN bb.bname

WHEN bb.cpid

THEN bb.cname

ELSE aa.name

END

)name

FROM (

SELECT a.pid +1 apid, a.TYPE atype, a.NAME aname,

b.pid +1 bpid, b.type btype, b.name bname,

c.pid -2 cpid, c.type ctype, c.name cname

FROM table_swap1 a, table_swap1 b, table_swap1 c

WHERE MOD( a.pid, 5 ) =0

AND a.pid +1 = b.pid

AND a.type =3

AND a.type = b.type

AND a.pid +2 = c.pid

)bb, table_swap1 aa

GROUP BY pid

This Query did what i exactly need

… But In my case pid is Primary key.

so i can’t get the results in 1 to 15

order . So can i do this row number…

how can i do

所有的建议都欢迎甚至解决方案在PHP中

让我可以用mysql或任何其他方式来做到这一点..