行号为MySQL的交换项目 [英] mysql swap items with row number
问题描述
这是我的表结构,在这里,我需要交换的物品。其中的手段,你可以看到3型永远是配对的(类型3项总是成对
)。
我刚刚任命配对项目中对理解第一个1是主第二个是子。 所以,对主人不应该5,10和15的位置
如果说到那个地方,我需要到下一个项目交换到那个地方(neaxt项目将子它不应该被视为下一个项目)
例如:
PID 10(来自10个位置)我需要调换像这样
PID类型名称
.. .. ..
10 2 B211 3 E1(主)
12 3 A2(子).. .. ..
表
PID方式PNAME
1 1的
2 1B中
3 2 C
4 3 D(母校)
5 3 E(子) 6 1 A1
7 2 B1
8 1 C1
9 2 D1
10 3 E1(主) 11月3日A2(子)
12 2 B2
13 1 C2
14 2 D2
15 1 E3
屏幕截图
以获得更多帮助。
我给你表结构和测试数据,如果您有任何IDEA跟我分享一下!
CREATE QUERY
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数据默认字符集= latin1的AUTO_INCREMENT = 17;
插入查询
INSERT INTO`table_swap1`(`pid`,`type`,`name`)VALUES
(1,1,A),(2,1,B),(3,2,D),(4,3,E(主)),
(5,3,f中的(子)),(6,1,A1),(7,2,B1),(8,1,'C1'),
(9,2,D1),(10,3,'E1(主)),(11,3,'A2(分)'),(12,2,B2),
(13,1,'C 2'),(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
结束
)
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
结束
)名称
FROM( 选择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
从table_swap1一个,table_swap1 B,table_swap1Ç
WHERE MOD(a.pid,5)= 0
和a.pid + 1 = b.pid
和a.type = 3
和a.type = b.type
和a.pid + 2 = c.pid
)BB,table_swap1 AA
GROUP BY PID
该查询做了什么,我究竟需要
......但对我来说pid是主键。
所以我不能得到的结果1至15
顺序。所以,我可以做到这一点的行号...
我该怎么办
块引用>都挺建议,欢迎甚至在PHP的解决方案
让我这是可能在MySQL或任何其他方式做到这一点..解决方案所以,基本上你的问题可以表示为:
3型的两个相邻产品的第一款产品不能放在
其中是5的倍数的位置上。什么复杂的事情是,有您的表没有订单,没有订单,就无法界定为您的产品恒定的位置。一个SELECT返回的行没有ORDER BY的顺序没有规定。
总之,要做到这一点最简单的方法是在应用程序中。抓住你的结果作为一个数组,进行扫描,如果发现是不是在正确的位置两款产品,只是洗牌周围的这些阵列中的
This is my table structure , Here i need to swap items .Which means, you can see type 3 always comes paired(
type 3 items are always paired
). I just named paired items for understanding first 1 in pair is master and second one is sub.So master of the pair should not come 5,10 and 15 positions
if it comes that place i need to swap the next item into that place(neaxt item will be sub it should not considered as next item)for example
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) .. .. ..
Table
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
screenshot
FOR FURTHER HELP
I GIVING YOU TABLE STRUCTURE AND TEST DATA, PLEASE IF YOU HAVE IDEA SHARE WITH ME !
CREATE QUERY
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 QUERY
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');
My work and Result
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
all kind of suggestions are welcome even solution in php let me is this possible in mysql or any other way to do this ..
解决方案So, basically your problem can be formulated as :
The first product of two adjacent products of type 3 cannot be placed in a position which is a multiple of 5.
What complicates things is that there is no order in your table, and without an order, it is impossible to define a constant "position" for your products. The order of returned rows for a SELECT without ORDER BY is not specified.
Anyway, the simplest way to do this is in the application. Grab your results as an array, scan it, and if you find two products that are not in the right position, simply shuffle them around in the array.
这篇关于行号为MySQL的交换项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!