ورود

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

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

***ADMIN*** · 09-12-1394، 09:36 ق.ظ

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

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

omid_bnt

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

ممکنه 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

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------- */

ورود
نام‌کاربری
رمزعبور:	فراموشی رمزعبور
	مرا به‌خاطر بسپار.