NBS Posted March 3, 2011 Report Share Posted March 3, 2011 (edited) Sveiki, man ir šāda problēma. Ir vairāki mainīgie: 1110, 1121, 1122, 1130. Gribās no šiem mainīgajiem permutāciju. Lai noselektējās šādi: 1110, 1121 1110, 1122 1110, 1130 1121, 1122 1121, 1130 1110, 1121, 1122 1110, 1121, 1130 utt. MS SQL izmantojot. Edited March 3, 2011 by NBS Quote Link to comment Share on other sites More sharing options...
Aleksejs Posted March 3, 2011 Report Share Posted March 3, 2011 permutāciju, variāciju vai kombināciju? Quote Link to comment Share on other sites More sharing options...
Gints Plivna Posted March 3, 2011 Report Share Posted March 3, 2011 Pieliec vēl NULL (tipa tukšā vērtība šai gadījumā) un paņem cross join. Nupat te jau bija ļoti līdzīga tēma, kurai izrādās pats vien esi autors :) Gints Plivna http://datubazes.wordpress.com Quote Link to comment Share on other sites More sharing options...
NBS Posted March 3, 2011 Author Report Share Posted March 3, 2011 Nu jā, pirmstam bija kombinācija. Tagad vajadzīga permutācija. Es īsti nesapratu kā tas ir pielikt klāt null ? Quote Link to comment Share on other sites More sharing options...
Gints Plivna Posted March 3, 2011 Report Share Posted March 3, 2011 Es īsti nesapratu kā tas ir pielikt klāt null ? Nu uzskati, ka NULL arī ir mainīgais. Quote Link to comment Share on other sites More sharing options...
NBS Posted March 3, 2011 Author Report Share Posted March 3, 2011 Nu es meiģinu kaut kā šādi: http://paste.php.lv/1e792f7ac5d363ca5239b54b8288c278?lang=sql Bet nu rezultāts nav pareizs. Quote Link to comment Share on other sites More sharing options...
Gints Plivna Posted March 3, 2011 Report Share Posted March 3, 2011 Diezgan patizli izskatās, bet kaut kas uz to pusi ir: WITH v AS ( SELECT '1110' AS n union ALL SELECT '1121' union ALL SELECT '1122' union ALL SELECT '1130' union ALL SELECT '' ) SELECT DISTINCT (CASE WHEN v1.n = '' THEN '' WHEN LEN(v2.n + v3.n + v4.n + v5.n) > 0 THEN v1.n + ',' ELSE v1.n END + CASE WHEN v2.n = '' THEN '' WHEN LEN(v3.n + v4.n + v5.n) > 0 THEN v2.n + ',' ELSE v2.n END + CASE WHEN v3.n = '' THEN '' WHEN LEN(v4.n + v5.n) > 0 THEN v3.n + ',' ELSE v3.n END + CASE WHEN v4.n = '' THEN '' WHEN LEN(v5.n) > 0 THEN v4.n + ',' ELSE v4.n END + CASE WHEN v5.n = '' THEN '' ELSE v5.n END) AS t FROM v v1 CROSS JOIN v v2 CROSS JOIN v v3 CROSS JOIN v v4 CROSS JOIN v v5 order by t Gints Plivna http://datubazes.wordpress.com Quote Link to comment Share on other sites More sharing options...
NBS Posted March 4, 2011 Author Report Share Posted March 4, 2011 Kaut kā galīgi ne tā. Pašreiz sanāk šādi ar tavu kodu: 1110 1110,1110 1110,1110,1110 1110,1110,1110,1110 1110,1110,1110,1110,1110 1110,1110,1110,1110,1121 1110,1110,1110,1110,1122 1110,1110,1110,1110,1130 1110,1110,1110,1121 ... Bet vajag šādi: 1110, 1121 1110, 1122 1110, 1130 1121, 1122 1121, 1130 1122, 1130 1110, 1121, 1122 1110, 1121, 1130 1110, 1122, 1130 1110, 1121, 1122, 1130 Quote Link to comment Share on other sites More sharing options...
rATRIJS Posted March 4, 2011 Report Share Posted March 4, 2011 Kapēc to vispār vajag darīt iekš SQL? Quote Link to comment Share on other sites More sharing options...
NBS Posted March 4, 2011 Author Report Share Posted March 4, 2011 (edited) Jo pēc tam būs vaicājumi ar šīm permutācijām. Varbūt ir variants kā savādāk atrisināt šo problēmu ? Edited March 4, 2011 by NBS Quote Link to comment Share on other sites More sharing options...
marrtins Posted March 4, 2011 Report Share Posted March 4, 2011 Nafig ar dakšiņu jāsmeļ ūdens? :O Quote Link to comment Share on other sites More sharing options...
Gints Plivna Posted March 4, 2011 Report Share Posted March 4, 2011 Nu, ja godīgi esmu gatavs piekrist, ka SQLs nav varbūt labākais risinājums šai problēmai :) BET cross join ka zināms ir Dekarta reizinājums. Tātad katrs ar katru. Ja no tā kaut ko vajag ierobežot, tad to var izdarīt gluži tāpat kā, ja laistu cauri kaut kādus ciklus ciklā vai kā citādi ģenerētu šādus variantus. Piemēram šādi: WITH v AS ( SELECT '1110' AS n union ALL SELECT '1121' union ALL SELECT '1122' union ALL SELECT '1130' union ALL SELECT '' ) SELECT DISTINCT (CASE WHEN v1.n = '' THEN '' WHEN LEN(v2.n + v3.n + v4.n + v5.n) > 0 THEN v1.n + ',' ELSE v1.n END + CASE WHEN v2.n = '' THEN '' WHEN LEN(v3.n + v4.n + v5.n) > 0 THEN v2.n + ',' ELSE v2.n END + CASE WHEN v3.n = '' THEN '' WHEN LEN(v4.n + v5.n) > 0 THEN v3.n + ',' ELSE v3.n END + CASE WHEN v4.n = '' THEN '' WHEN LEN(v5.n) > 0 THEN v4.n + ',' ELSE v4.n END + CASE WHEN v5.n = '' THEN '' ELSE v5.n END) AS t FROM v v1 CROSS JOIN v v2 CROSS JOIN v v3 CROSS JOIN v v4 CROSS JOIN v v5 WHERE v1.n <> v2.n AND v1.n <> v3.n AND v1.n <> v4.n AND v1.n <> v5.n AND (v2.n <> v3.n AND v2.n <> v4.n AND v2.n <> v5.n OR len(v2.n + v3.n + v4.n + v5.n) = 0) AND (v3.n <> v4.n AND v3.n <> v5.n OR len(v3.n + v4.n + v5.n) = 0) AND (v4.n <> v5.n OR len(v4.n + v5.n) = 0) order by t t ------------------------ 1110 1110,1121 1110,1121,1122 1110,1121,1122,1130 1110,1121,1130 1110,1121,1130,1122 1110,1122 1110,1122,1121 1110,1122,1121,1130 1110,1122,1130 1110,1122,1130,1121 1110,1130 1110,1130,1121 1110,1130,1121,1122 1110,1130,1122 1110,1130,1122,1121 1121 Nezinu, vai tas ir tas ko vajag, jo īsti jau tā skaidri neaprakstīji, kas tas ir, bet nu pielabojot WHERE klauzas teorētiski noteikti var dabūt to, kas nepieciešams. Vai tas ir arī praktiski labs risinājums - nu tas jau ir cits jautājums ;) Galu galā mēs zinām, ka SQLā mierīgi var zīmēt arī pulksteni, piemēram Gints Plivna http://datubazes.wordpress.com Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.