انتخاب چند رکورد از هر گروه

[OMID_BNT]

سلام

سه جدول زیر رو در نظر بگیرید




tbl_country

name ____ id

1 _______un
2 ________ir
3 _______uk




tbl_city


name _______ id_country ____ id

1_____________1 __________c1
2_____________1 __________c2
3_____________1 __________c3
4_____________1 __________c4
5_____________1 __________c5
6_____________2 __________c6
7_____________2 __________c7
8_____________2 __________c8
9_____________2 __________c9
10____________2 __________c10
11____________3 __________c11
12____________3 __________c12
13____________3 __________c13
14____________3 __________c14
15____________3 __________c15




tbl_address

این جدول 45 سطر دارد

name ______ id_country _______ id_city ____ id

add_1 _________ 1 _______==_____ 1 ______ 1
add_2 _________ 1 _______________ 1 ______ 2
add_3 _________ 1 _______________ 1 ______ 3
add_4 _________ 1 _______________ 1 ______ 4
add_5 _________ 1 _______________ 1 ______ 5
add_6 _________ 2 _______==_____ 1 ______ 6
add_7 _________ 2 _______________ 1 ______ 7
add_8 _________ 2 _______________ 1 ______ 8
add_9 _________ 2 _______________ 1 ______ 9
add_10 _________ 2 _______________ 1 _____ 10
add_11 _________ 3 _______==_____1 ______ 11
add_12 _________ 3 ______________ 1 ______ 12
add_13 _________ 3 ______________ 1 ______ 13
add_14 _________ 3 ______________ 1 ______ 14
add_15 _________ 3 ______________ 1 ______ 15

add_16 _________ 1 _______________ 2 ______ 16
add_20 _________ 1 _______________ 2 ______ 20

add_21 _________ 1 _______________ 3 ______ 21
add_30 _________ 1 _______________ 3 ______ 25

add_26 _________ 2 _______________ 2 ______ 26
add_30 _________ 2 _______________ 2 ______ 30

add_31 _________ 2 _______________ 3 ______ 31
add_35 _________ 2 _______________ 3 ______ 35

add_36 _________ 3 _______________ 2 ______ 36
add_40 _________ 3 _______________ 2 ______ 40

add_41 _________ 3 _______________ 3 ______ 41
add_45 _________ 3 _______________ 3 ______ 45




سوال : قسمت اول (مهمترین بخش)

میخوایم از حدول ادرس ، از هرگروه کشور که مثلا کد شهرش1 هست ، سه سطر انتخاب کنیم . مثلا به جدول زیر برسیم


id_city احتمالا WHERE میخوره

ولی برای id_country نمیدونم چیکار کنم از group by _ limit 3 هم نتیجه نگرفتم

برای نمایش نام هر شهر و کشور بجای id هم فکر کنم از inner join استفاده کنیم ایا بهینه است






tbl_result





name ______ id_country _______ id_city ____ id

add_1 _________ 1 _______________ 1 ______ 1
add_2 _________ 1 _______________ 1 ______ 2
add_3 _________ 1 _______________ 1 ______ 3
add_6 _________ 2 _______________ 1 ______ 6
add_7 _________ 2 _______________ 1 ______ 7
add_8 _________ 2 _______________ 1 ______ 8
add_11 ________ 3 _______________ 1 ______ 11
add_12 ________ 3 _______________ 1 ______ 12
add_13 ________ 3 _______________ 1 ______ 13




قسمت دوم این انتخاب با توجه به شرط اولیه (از هرگروه سه تا) به صورت رندم باشد یعنی احتمال نمایش ادرسی با نام add_5 هم وجود داشته باشد



ممنون میشم بهینه ترین راه حل رو بفرمایید

با تشکر

[OMID_BNT]

سلام خوشبختانه تونستم یه قسمت رو به جواب برسم:لبخندساده:
کد نهایی در پنج حالت کلی جهت اشنایی و برای استفاده دوستان در زیر قرار دادم

ممکنه syntax هماهنگ با sqlite همین شرایط ( فقط مورد 5 ) رو هم دراختیارم بزارید؟ واقعا مهمه
اگر در خود sqlite همچین کدی قابل پیاده سازی نیست پساسکریپت php , sqlite رو کمک کنید بنویسم با سپاس فراوان

یک نمومه برای mysql هم در لینک (http://sqlfiddle.com/#!9/37dc1/23) زیر
http://sqlfiddle.com/#!9/37dc1/23


کد ساخت دیتابیس جهت تست


CREATE TABLE tbl_country (
id int PRIMARY KEY AUTO_INCREMENT,
name text
);


CREATE TABLE tbl_city (
id int PRIMARY KEY AUTO_INCREMENT,
name text,
id_country int
);


CREATE TABLE tbl_address (
id int PRIMARY KEY AUTO_INCREMENT,
name text,
id_country int,
id_city int
);


/*------------------ ------------------*/
INSERT INTO tbl_country VALUES
(1,'un'),
(2,'uk'),
(3,'ir');


INSERT INTO tbl_city VALUES
(1,'c1',1),
(2,'c2',1),
(3,'c3',1),
(4,'c4',1),
(5,'c5',1),


(6,'c6',2),
(7,'c7',2),
(8,'c8',2),
(9,'c9',2),
(10,'c10',2),


(11,'c11',3),
(12,'c12',3),
(13,'c13',3),
(14,'c14',3),
(15,'c15',3);


INSERT INTO tbl_address VALUES
(1,'add_1',1,1),
(2,'add_2',1,1),
(3,'add_3',1,1),
(4,'add_4',1,1),
(5,'add_5',1,1),
(6,'add_6',2,1),
(7,'add_7',2,1),
(8,'add_8',2,1),
(9,'add_9',2,1),
(10,'add_10',2,1),
(11,'add_11',3,1),
(12,'add_12',3,1),
(13,'add_13',3,1),
(14,'add_14',3,1),
(15,'add_15',3,1),
(16,'add_16',1,2),
(17,'add_17',1,2),
(18,'add_18',1,2),
(19,'add_19',1,2),
(20,'add_20',1,2),
(21,'add_21',2,2),
(22,'add_22',2,2),
(23,'add_23',2,2),
(24,'add_24',2,2),
(25,'add_25',2,2),
(26,'add_26',3,2),
(27,'add_27',3,2),
(28,'add_28',3,2),
(29,'add_29',3,2),
(30,'add_30',3,2),
(31,'add_31',1,3),
(32,'add_32',1,3),
(33,'add_33',1,3),
(34,'add_34',1,3),
(35,'add_35',1,3),
(36,'add_36',2,3),
(37,'add_37',2,3),
(38,'add_38',2,3),
(39,'add_39',2,3),
(40,'add_40',2,3),
(41,'add_41',3,3),
(42,'add_42',3,3),
(43,'add_43',3,3),
(44,'add_44',3,3),
(45,'add_45',3,3);




1




/* ------Only a tbl_address-------- NO JOIN -------START-000------- */


SET @num := 0, @type := '';


SELECT id,name,id_country, id_city,
@num := if(@type = id_country, @num + 1, 1) AS row_number,
@type := id_country AS dummy
FROM (
SELECT * FROM tbl_address
WHERE
id_city=1 and /* Only a city , For all city , remove this line (id_city=1 and ) */
id_country IN (SELECT id_country FROM tbl_address)
ORDER BY id_city
) AS temph
GROUP BY id


HAVING row_number<='2'/* View record number per group */


ORDER BY id_city, id_country , id ASC /* DESC */ ;


/* ------Only a tbl_address-------- NO JOIN -------END-000------- */






2



/* -------------- JOIN tbl_country code start -------START-001------- */

SET @num := 0, @type := '';


SELECT
temph.id,
temph.name,
temph.id_country,


tbl_country.name AS country_name,/* name column table for join */


temph.id_city,




@num := if(@type = temph.id_country, @num + 1, 1) AS row_number,
@type := temph.id_country AS dummy
FROM (
SELECT tbl_address.* FROM tbl_address
WHERE
tbl_address.id_city=1 and /* Only a city , For all city , remove this line (id_city=1 and ) */
tbl_address.id_country IN (SELECT tbl_address.id_country FROM tbl_address)
ORDER BY tbl_address.id_city
) AS temph


INNER JOIN tbl_country ON temph.id_country = tbl_country.id /* Coding join */
GROUP BY temph.id
HAVING row_number<='2' /* View record number per group */
ORDER BY temph.id_city, temph.id_country , temph.id ASC /* DESC */ ;

/* -------------- JOIN tbl_country code end -------END-001------- */






3



/* -------------- JOIN tbl_city code start -------START-002------- */

SET @num := 0, @type := '';


SELECT
temph.id,
temph.name,
temph.id_country,
temph.id_city,


tbl_city.name AS city_name, /* name column table for join */


@num := if(@type = temph.id_country, @num + 1, 1) AS row_number,
@type := temph.id_country AS dummy
FROM (
SELECT tbl_address.* FROM tbl_address
WHERE
tbl_address.id_city=1 and /* Only a city , For all city , remove this line (id_city=1 and ) */


tbl_address.id_country IN (SELECT tbl_address.id_country FROM tbl_address)
ORDER BY tbl_address.id_city
) AS temph


INNER JOIN tbl_city ON temph.id_city = tbl_city.id /* Coding join */


GROUP BY temph.id
HAVING row_number<='2' /* View record number per group */
ORDER BY temph.id_city, temph.id_country , temph.id ASC /* DESC */ ;

/* -------------- JOIN tbl_city code end -------END-002------- */





4


/* -------------- JOIN tbl_country AND tbl_city code start -------START-003------- */

SET @num := 0, @type := '';


SELECT
temph.id,
temph.name,
temph.id_country,
tbl_country.name AS country_name, /* name column table 1 for join */
temph.id_city,
tbl_city.name AS city_name, /* name column table 2 for join */


@num := if(@type = temph.id_country, @num + 1, 1) AS row_number,
@type := temph.id_country AS dummy
FROM (
SELECT tbl_address.* FROM tbl_address
WHERE
tbl_address.id_city=1 and /* Only a city , For all city , remove this line (id_city=1 and ) */
tbl_address.id_country IN (SELECT tbl_address.id_country FROM tbl_address)
ORDER BY tbl_address.id_city
) AS temph


INNER JOIN tbl_country ON temph.id_country = tbl_country.id /* Coding join 1 */


INNER JOIN tbl_city ON temph.id_city = tbl_city.id /* Coding join 2*/


GROUP BY temph.id
HAVING row_number<='2' /* View record number per group */
ORDER BY temph.id_city, temph.id_country , temph.id ASC /* DESC */ ;

/* -------------- JOIN tbl_country AND tbl_city code end -------END-003------- */





:قلب:5:قلب:
5 - برای انتخاب رندم - به همراه join (کد کامل)





/* ---------Random--AND--- JOIN tbl_country AND tbl_city code start -------START-004------- */


SET @num := 0, @type := '';


SELECT
temph.id,
temph.name,
temph.id_country,
tbl_country.name AS country_name, /* name column table 1 for join */
temph.id_city,
tbl_city.name AS city_name, /* name column table 2 for join */


@num := if(@type = temph.id_country, @num + 1, 1) AS row_number,
@type := temph.id_country AS dummy
FROM (
SELECT tbl_address.* FROM tbl_address

WHERE

tbl_address.id_city=1 and /* Only a city , For all city , remove this line (id_city=1 and ) */

tbl_address.id_country IN (SELECT tbl_address.id_country FROM tbl_address)

ORDER BY RAND(),tbl_address.id_city /* for Random */

) AS temph


INNER JOIN tbl_country ON temph.id_country = tbl_country.id /* Coding join 1 */


INNER JOIN tbl_city ON temph.id_city = tbl_city.id /* Coding join 2*/


GROUP BY temph.id
HAVING row_number<='2' /* View record number per group */
ORDER BY temph.id_city, temph.id_country , temph.id ASC /* DESC */ ;



/* ---------Random--AND--- JOIN tbl_country AND tbl_city code end -------END-004------- */