تالار گفتمان nCIS.ir

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

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  استفاده کنیم ایا بهینه است

نقل قول:

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 هم وجود داشته باشد


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

به پاسخی که خودم توی این لینک دادم دقت کنید:

http://stackoverflow.com/questions/21296...9#27633629

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


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

یک نمومه برای mysql هم در لینک زیر
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------- */

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

---

2

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 - برای انتخاب رندم - به همراه 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------- */