08-12-1394، 03:12 ب.ظ
(آخرین تغییر در ارسال: 09-12-1394، 06:17 ب.ظ توسط omid_bnt.)
ارسال‌ها: 3
موضوع‌ها: 2
تاریخ عضویت: تير 1394
اعتبار:
0
تشکرها: 2
0 بار تشکر شده در 0 پست
سلام
سه جدول زیر رو در نظر بگیرید
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 هم وجود داشته باشد
-
ممنون میشم بهینه ترین راه حل رو بفرمایید
با تشکر 
ارسال‌ها: 3,701
موضوع‌ها: 140
تاریخ عضویت: اردیبهشت 1394
اعتبار:
134
تشکرها: 195
3447 بار تشکر شده در 2120 پست
09-12-1394، 06:00 ب.ظ
(آخرین تغییر در ارسال: 10-12-1394، 10:59 ق.ظ توسط omid_bnt.)
ارسال‌ها: 3
موضوع‌ها: 2
تاریخ عضویت: تير 1394
اعتبار:
0
تشکرها: 2
0 بار تشکر شده در 0 پست
خیلی ممنون از توجه تون کد نهایی در پنج حالت کلی جهت اشنایی و برای استفاده دوستان در زیر قرار دادم
ممکنه syntax هماهنگ با sqlite همین شرایط ( فقط مورد 5 ) رو هم دراختیارم بزارید؟ واقعا مهمه
اگر در خود sqlite همچین کدی قابل پیاده سازی نیست پس اسکریپت php , sqlite رو کمک کنید بنویسم با سپاس فراوان
یک نمومه برای mysql هم در لینک زیر
http://sqlfiddle.com/#!9/37dc1/23
کد ساخت دیتابیس جهت تست
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | /*
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 */ ;
/*
/*
|
2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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 */ ;
/*
|
3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | /*
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 */ ;
/*
|
4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | /*
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 */ ;
/*
|
5
5 - برای انتخاب رندم - به همراه join (کد کامل)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | /*
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 */ ;
/*
|
|
![[عکس: packages.gif]](http://forum.ncis.ir/images/ads/packages.gif)