25.02.2015, 09:32
(
Последний раз редактировалось PowerPC603; 25.02.2015 в 15:11.
)
I found something nice:
On this table (it's much longer than this, it's actually 1750 rows, but you get the idea):
Gives me this result:
Then I only need to use 1 query to read the entire table.
Then I can still loop through all rows, use the "model" as the index for my array and use sscanf to split up the results again to put them in my enum structure just like before.
Then my table vehicleinfo can dump the ValidComponents column, which had up to 30 values in one column, and the table should be normalized.
Still, I need 2 queries now, and 2 loading functions to load the same data as before.
I'm still looking for a way to merge both queries together, to have the same result returned as before, so I can keep my loading function exactly the same, just another query that loads one table entirely, and uses group_concat on the second, where it merged each grouped entry to the correct row from the first table.
Код:
SELECT model, GROUP_CONCAT(component SEPARATOR '-') AS components FROM vehicleinfocomponents GROUP BY model;
pawn Код:
+-------+-----------+
| Model | Component |
+-------+-----------+
| 400 | 1008 |
| 400 | 1009 |
| 400 | 1010 |
| 400 | 1013 |
| 400 | 1018 |
| 400 | 1019 |
| 400 | 1020 |
| 400 | 1021 |
| 400 | 1024 |
| 400 | 1025 |
| 400 | 1074 |
| 400 | 1076 |
| 400 | 1078 |
| 400 | 1081 |
| 400 | 1082 |
| 400 | 1085 |
| 400 | 1086 |
| 400 | 1087 |
| 400 | 1096 |
| 400 | 1097 |
| 400 | 1098 |
| 401 | 1001 |
| 401 | 1003 |
| 401 | 1004 |
| 401 | 1005 |
| 401 | 1006 |
| 401 | 1007 |
| 401 | 1008 |
| 401 | 1009 |
| 401 | 1010 |
| 401 | 1013 |
| 401 | 1019 |
| 401 | 1020 |
| 401 | 1025 |
| 401 | 1074 |
| 401 | 1076 |
| 401 | 1078 |
| 401 | 1081 |
| 401 | 1082 |
| 401 | 1085 |
| 401 | 1086 |
| 401 | 1087 |
| 401 | 1096 |
| 401 | 1097 |
| 401 | 1098 |
| 401 | 1143 |
| 401 | 1145 |
| 402 | 1008 |
| 402 | 1009 |
| 402 | 1010 |
| 402 | 1025 |
| 402 | 1074 |
| 402 | 1076 |
| 402 | 1078 |
| 402 | 1081 |
| 402 | 1082 |
| 402 | 1085 |
| 402 | 1087 |
| 402 | 1096 |
| 402 | 1097 |
| 402 | 1098 |
| 403 | 1008 |
| 403 | 1009 |
| 403 | 1010 |
| 404 | 1000 |
| 404 | 1002 |
| 404 | 1007 |
| 404 | 1008 |
| 404 | 1009 |
| 404 | 1010 |
| 404 | 1013 |
| 404 | 1016 |
| 404 | 1019 |
| 404 | 1020 |
| 404 | 1021 |
| 404 | 1025 |
| 404 | 1074 |
| 404 | 1076 |
| 404 | 1078 |
| 404 | 1081 |
| 404 | 1082 |
| 404 | 1085 |
| 404 | 1086 |
| 404 | 1087 |
| 404 | 1096 |
| 404 | 1097 |
| 404 | 1098 |
| 405 | 1000 |
| 405 | 1001 |
| 405 | 1008 |
| 405 | 1009 |
| 405 | 1010 |
| 405 | 1014 |
| 405 | 1018 |
| 405 | 1019 |
| 405 | 1020 |
| 405 | 1021 |
| 405 | 1023 |
| 405 | 1025 |
| 405 | 1074 |
| 405 | 1076 |
| 405 | 1078 |
| 405 | 1081 |
| 405 | 1082 |
| 405 | 1085 |
| 405 | 1086 |
| 405 | 1087 |
| 405 | 1096 |
| 405 | 1097 |
| 405 | 1098 |
| 407 | 1008 |
| 407 | 1009 |
| 407 | 1010 |
| 408 | 1008 |
| 408 | 1009 |
| 408 | 1010 |
| 409 | 1008 |
| 409 | 1009 |
| 409 | 1010 |
| 409 | 1025 |
| 409 | 1074 |
| 409 | 1076 |
| 409 | 1078 |
| 409 | 1081 |
| 409 | 1082 |
| 409 | 1085 |
| 409 | 1086 |
| 409 | 1087 |
| 409 | 1096 |
| 409 | 1097 |
| 409 | 1098 |
| 410 | 1001 |
| 410 | 1003 |
| 410 | 1007 |
| 410 | 1008 |
| 410 | 1009 |
| 410 | 1010 |
| 410 | 1013 |
| 410 | 1019 |
| 410 | 1020 |
| 410 | 1021 |
| 410 | 1023 |
| 410 | 1024 |
| 410 | 1025 |
| 410 | 1074 |
| 410 | 1076 |
| 410 | 1078 |
| 410 | 1081 |
| 410 | 1082 |
| 410 | 1085 |
+-------+-----------+
pawn Код:
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| model | components |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| 400 | 1008-1009-1010-1013-1018-1019-1020-1021-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 401 | 1001-1003-1004-1005-1006-1007-1008-1009-1010-1013-1019-1020-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 402 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 403 | 1008-1009-1010 |
| 404 | 1000-1002-1007-1008-1009-1010-1013-1016-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 405 | 1000-1001-1008-1009-1010-1014-1018-1019-1020-1021-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 407 | 1008-1009-1010 |
| 408 | 1008-1009-1010 |
| 409 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 410 | 1001-1003-1007-1008-1009-1010-1013-1019-1020-1021-1023-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 411 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 412 | 1008-1009-1010-1076-1078-1086-1087-1097-1098 |
| 413 | 1008-1009-1010 |
| 414 | 1008-1009-1010 |
| 415 | 1001-1003-1007-1008-1009-1010-1018-1019-1023-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 416 | 1008-1009-1010 |
| 418 | 1002-1006-1008-1009-1010-1016-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 419 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 420 | 1001-1003-1004-1005-1008-1009-1010-1019-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 421 | 1000-1008-1009-1010-1014-1016-1018-1019-1020-1021-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 422 | 1007-1008-1009-1010-1013-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 423 | 1008-1009-1010 |
| 424 | 1008-1009-1010 |
| 426 | 1001-1003-1004-1005-1006-1008-1009-1010-1019-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 427 | 1008-1009-1010 |
| 428 | 1008-1009-1010 |
| 429 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 431 | 1008-1009-1010 |
| 433 | 1008-1009-1010 |
| 434 | 1008-1009-1010 |
| 436 | 1001-1003-1006-1007-1008-1009-1010-1013-1019-1020-1021-1022-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 437 | 1008-1009-1010 |
| 438 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 439 | 1001-1003-1007-1008-1009-1010-1013-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 440 | 1008-1009-1010 |
| 442 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 443 | 1008-1009-1010 |
| 445 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 451 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 455 | 1008-1009-1010 |
| 456 | 1008-1009-1010 |
| 457 | 1008-1009-1010 |
| 458 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 459 | 1008-1009-1010 |
| 466 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 467 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 470 | 1008-1009-1010 |
| 474 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 475 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 477 | 1006-1007-1008-1009-1010-1018-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 478 | 1004-1005-1008-1009-1010-1012-1013-1020-1021-1022-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 479 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 480 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 482 | 1008-1009-1010 |
| 483 | 1008-1009-1010 |
| 485 | 1008-1009-1010 |
| 486 | 1008-1009-1010 |
| 489 | 1000-1002-1004-1005-1006-1008-1009-1010-1013-1016-1018-1019-1020-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 490 | 1008-1009-1010 |
| 491 | 1003-1007-1008-1009-1010-1014-1018-1019-1020-1021-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 492 | 1000-1004-1005-1006-1008-1009-1010-1016-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 494 | 1008-1009-1010 |
| 495 | 1008-1009-1010 |
| 496 | 1001-1002-1003-1006-1007-1008-1009-1010-1011-1019-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143 |
| 498 | 1008-1009-1010 |
| 499 | 1008-1009-1010 |
| 500 | 1008-1009-1010-1013-1019-1020-1021-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 502 | 1008-1009-1010 |
| 503 | 1008-1009-1010 |
| 504 | 1008-1009-1010 |
| 505 | 1008-1009-1010 |
| 506 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 507 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 508 | 1008-1009-1010 |
| 514 | 1008-1009-1010 |
| 515 | 1008-1009-1010 |
| 516 | 1000-1002-1004-1007-1008-1009-1010-1015-1016-1018-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 517 | 1002-1003-1007-1008-1009-1010-1016-1018-1019-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 518 | 1001-1003-1005-1006-1007-1008-1009-1010-1013-1018-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 524 | 1008-1009-1010 |
| 525 | 1008-1009-1010 |
| 526 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 527 | 1001-1007-1008-1009-1010-1014-1015-1018-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 528 | 1008-1009-1010 |
| 529 | 1001-1003-1006-1007-1008-1009-1010-1011-1012-1018-1019-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 530 | 1008-1009-1010 |
| 531 | 1008-1009-1010 |
| 532 | 1008-1009-1010 |
| 533 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 534 | 1008-1009-1010-1075-1076-1077-1078-1079-1083-1084-1086-1087-1097-1098-1100-1106-1122-1123-1125-1126-1127-1178-1179-1180-1185 |
| 535 | 1008-1009-1010-1075-1076-1077-1078-1079-1083-1084-1086-1087-1097-1098-1109-1110-1113-1114-1115-1116-1117-1118-1119 |
| 536 | 1008-1009-1010-1075-1076-1077-1078-1079-1083-1084-1086-1097-1098-1103-1104-1105-1108-1128-1181-1182-1183-1184 |
| 540 | 1001-1004-1006-1007-1008-1009-1010-1018-1019-1020-1023-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 541 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 542 | 1008-1009-1010-1014-1015-1018-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1145 |
| 543 | 1008-1009-1010 |
| 544 | 1008-1009-1010 |
| 545 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 546 | 1001-1002-1004-1006-1007-1008-1009-1010-1018-1019-1023-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 547 | 1000-1003-1008-1009-1010-1016-1018-1019-1020-1021-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143 |
| 549 | 1001-1003-1007-1008-1009-1010-1011-1012-1018-1019-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 550 | 1001-1003-1004-1005-1006-1008-1009-1010-1018-1019-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 551 | 1002-1003-1005-1006-1008-1009-1010-1016-1018-1019-1020-1021-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 552 | 1008-1009-1010 |
| 554 | 1008-1009-1010 |
| 555 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 558 | 1008-1009-1010-1073-1074-1075-1077-1079-1080-1081-1082-1083-1085-1086-1087-1088-1089-1090-1091-1092-1093-1163-1164-1165-1166-1167-1168 |
| 559 | 1008-1009-1010-1065-1066-1067-1068-1069-1070-1073-1074-1075-1077-1079-1080-1081-1082-1083-1085-1087-1158-1159-1160-1161-1162-1173 |
| 560 | 1008-1009-1010-1026-1028-1029-1031-1032-1033-1073-1074-1075-1077-1079-1080-1081-1082-1083-1085-1086-1087-1138-1139-1140-1141-1169-1170 |
| 561 | 1008-1009-1010-1055-1056-1057-1058-1059-1060-1061-1064-1073-1074-1075-1077-1079-1080-1081-1082-1083-1085-1086-1087-1154-1155-1156-1157 |
| 562 | 1008-1009-1010-1034-1035-1036-1037-1038-1039-1073-1074-1075-1077-1079-1080-1081-1082-1083-1085-1086-1087-1146-1147-1148-1149-1171-1172 |
| 565 | 1008-1009-1010-1045-1046-1047-1048-1049-1050-1053-1054-1073-1074-1075-1077-1079-1080-1081-1082-1083-1085-1086-1087-1150-1151-1152-1153 |
| 566 | 1008-1009-1010-1076-1078-1086-1087-1097-1098 |
| 567 | 1008-1009-1010-1075-1076-1077-1078-1079-1083-1084-1086-1087-1097-1098-1129-1130-1131-1133-1186-1187-1188-1189 |
| 568 | 1008-1009-1010 |
| 571 | 1008-1009-1010 |
| 572 | 1008-1009-1010 |
| 574 | 1008-1009-1010 |
| 575 | 1008-1009-1010-1042-1043-1044-1075-1076-1077-1078-1079-1083-1084-1086-1087-1097-1098-1174-1175-1176-1177 |
| 576 | 1008-1009-1010-1075-1076-1077-1078-1079-1083-1084-1086-1087-1097-1098-1134-1135-1136-1190-1191-1192-1193 |
| 578 | 1008-1009-1010 |
| 579 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 580 | 1001-1006-1007-1008-1009-1010-1018-1020-1023-1025-1074-1076-1078-1081-1082-1085-1087-1096-1097-1098 |
| 582 | 1008-1009-1010 |
| 583 | 1008-1009-1010 |
| 585 | 1001-1003-1006-1007-1008-1009-1010-1013-1018-1019-1020-1023-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 587 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 588 | 1008-1009-1010 |
| 589 | 1000-1004-1005-1006-1007-1008-1009-1010-1013-1016-1018-1020-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1145 |
| 596 | 1008-1009-1010 |
| 597 | 1008-1009-1010 |
| 598 | 1008-1009-1010 |
| 599 | 1008-1009-1010 |
| 600 | 1004-1005-1006-1007-1008-1009-1010-1013-1018-1020-1022-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 601 | 1008-1009-1010 |
| 602 | 1008-1009-1010-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098 |
| 603 | 1001-1006-1007-1008-1009-1010-1018-1019-1020-1023-1024-1025-1074-1076-1078-1081-1082-1085-1086-1087-1096-1097-1098-1143-1145 |
| 604 | 1008-1009-1010 |
| 605 | 1008-1009-1010 |
| 609 | 1008-1009-1010 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
Then I can still loop through all rows, use the "model" as the index for my array and use sscanf to split up the results again to put them in my enum structure just like before.
Then my table vehicleinfo can dump the ValidComponents column, which had up to 30 values in one column, and the table should be normalized.
Still, I need 2 queries now, and 2 loading functions to load the same data as before.
I'm still looking for a way to merge both queries together, to have the same result returned as before, so I can keep my loading function exactly the same, just another query that loads one table entirely, and uses group_concat on the second, where it merged each grouped entry to the correct row from the first table.