1. GROUP BY Query Pagination
SELECT * FROM box;
id box1 box2
4 1 abc
3 1 def
5 2 ghi
6 3 jkl
7 2 mno
SELECT COUNT(*) FROM box GROUP BY( box1 );
box1 COUNT(*)
1 2
2 2
3 1
จะเห็นได้ว่า count(*) จะเป็นค่าที่เก็บจำนวนของ box1 ที่ซ้ำกัน อย่างเช่น box=1 มี 2 แถว จึงมีค่า count(*)=2
โดยปกติเมื่อเราจะทำการแบ่งหน้าใน PHP (Pagination) เราต้องการจำนวนแถวของผลลัพธ์ทั้งหมด
จะเห็นได้ว่าเมื่อใช้คำสั่ง GROUP BY เราจะไม่สามารถใช้ query count(*) อย่างข้างบนในการหาจำนวนแถวผลลัพธ์ทั้งหมด
เนื่องจาก query count(*) จะถูกนำไปใช้เป็นจำนวนที่ซ้ำกันของข้อมูลที่ถูก GROUP BY ไว้
ซึ่งปัญหานี้จะสามารถแก้ไขได้ง่ายๆ ด้วยการใช้ sub query อย่างข้างล่างนี้ครับ
SELECT COUNT(*) FROM (SELECT COUNT(*) FROM box GROUP BY box1) AS t1;
COUNT(*)
3
2. FIND THE STUDENTS GOT MAXIMUM SCORE IN EACH SUBJECT
ปัญหานี้ค่อนข้างซับซ้อนกว่าที่คิดครับ
ถ้าต้องการจะทดสอบ query สามารถเอา sql statement นี้ไปสร้างตารางเพื่อใช้ในการทดสอบได้ครับ
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
insert into `students`(`id`,`sname`) values (1,'A'),(2,'B'),(3,'C'),(4,'D');
CREATE TABLE `subjects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sjname` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
insert into `subjects`(`id`,`sjname`) values (1,'CT211'),(2,'CT212'),(3,'CT316'),(4,'CT317'),(5,'CT488');
CREATE TABLE `tests` (
`student_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`student_id`,`subject_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into `tests`(`student_id`,`subject_id`,`score`) values (1,1,80),(1,2,70),(1,3,25),(1,4,61),(1,5,99),(2,1,50),(2,2,72),(2,3,85),(2,4,11),(3,1,83),(3,2,39),(3,3,95),(3,4,96),(3,5,99),(4,1,98),(4,2,21),(4,3,60);
มาดู query ที่ใช้ในการหาคนที่ได้คะแนนสูงสุดในแต่ละวิชากันครับ
SELECT sname,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id AND
CONCAT(tests.student_id,'-',tests.subject_id) IN
(
SELECT CONCAT(student_id,'-',subject_id) FROM
(
SELECT tests.student_id,tests.subject_id FROM tests ORDER BY tests.subject_id, tests.score DESC
)
AS temp_table
GROUP BY subject_id
);
ซึ่งจะได้ผลลัพธ์
sname sjname score
A CT488 99
B CT212 72
C CT316 95
C CT317 96
D CT211 98
query ดังกล่าวดูเผินๆ ก็ทำงานปกติดีแต่ มีข้อเสียคือทำงานช้าครับ เนื่องจากใช้ฟังค์ชั่น concat()
ตอนนั้นผมยังไม่รู้จักการใช้ column หลายๆ column มาเปรียบเทียบพร้อมๆ กัน ซึ่งจะทำให้ query
SELECT * FROM tb1 WHERE c1=1 AND c2=2;
ทำให้เขียนได้สั้นๆ ดังนี้ครับ
SELECT * FROM tb1 WHERE (c1,c2)=(1,2);
จากความรู้นี้ทำให้สามารถปรับปรุง query ก่อนให้เร็วขึ้นและเขียนสั้นลงอีกด้วยครับ
SELECT sname,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id AND
(tests.student_id,tests.subject_id) IN
(
SELECT student_id,subject_id FROM
(
SELECT tests.student_id,tests.subject_id FROM tests ORDER BY tests.subject_id, tests.score DESC
)
AS temp_table
GROUP BY subject_id
);
ในตอนหลังผมพบว่าสามารถเขียน query ให้ได้ง่ายๆ ขึ้นด้วยคำสั่งนี้
SELECT sname,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id
AND tests.score = (SELECT MAX(score) FROM tests AS t1 WHERE t1.subject_id=tests.subject_id);
ปัญหาใหม่ก็คือในบางครั้งจะมีคนที่ได้คะแนนสูงสุดเหมือนกัน แต่ query ข้างบนนี้จะแสดงแยกแถวกันซึ่งไม่สะดวกในการใช้งาน
ซึ่งเราสามารถแก้ไขโดยใช้คำสั่ง GROUP BY ร่วมกับ GROUP_CONCAT
SELECT GROUP_CONCAT(sname) AS snames,sjname,score
FROM students,subjects,tests
WHERE tests.student_id = students.id AND
tests.subject_id = subjects.id
AND tests.score = (SELECT MAX(score) FROM tests AS t1 WHERE t1.subject_id=tests.subject_id)
GROUP BY sjname;
ซึ่งจะได้ผลลัพธ์เป็นไปตามความต้องการดังนี้ครับ
snames sjname score
D CT211 98
B CT212 72
C CT316 95
C CT317 96
C,A CT488 99 /* C และ A ได้คะแนนสูงสุดเท่ากัน */
3. FIND RANDOM NAME OF EACH GROUP ONLY ONCE
s name
1 ddd
2 eee
3 fff
1 mmm
2 ggg
3 zzz
1 hhh
2 bbb
3 ttt
SELECT CONCAT('set',s) AS `set`,`name` FROM (SELECT * FROM t ORDER BY RAND()) AS t1 GROUP BY s;
set name
set1 hhh
set2 eee
set3 fff
4. JOIN TABLE
แบบที่ 1) SELECT b1.id FROM b1,b2 WHERE b1.id = b2.b1_id;
แบบที่ 2) SELECT b1.id FROM b1 INNER JOIN b2 ON b1.id = b2.b1_id;
ทั้ง 2 แบบให้ผลลัพธ์เหมือนกัน แต่แบบที่ 2 จะเร็วกกว่าครับ
5. DELETE DUPLICATE RECORD
DROP TABLE IF EXISTS id_temp;
CREATE TEMPORARY TABLE id_temp (
`id` SMALLINT(5) UNSIGNED NOT NULL
) SELECT `id` FROM b3 GROUP BY `code` HAVING COUNT(*)>1;
DELETE FROM b3 WHERE b3.`id` IN(SELECT id_temp.`id` FROM id_temp);
6. GET MAX ORDER_ID OF CURRENT YEAR
$r = mysql_query("
SELECT
MAX(CAST(MID(pr_order,5,3) AS UNSIGNED)) AS now_id,
RIGHT(pr_order,4) AS year_id
FROM pr_description
GROUP BY year_id
HAVING year_id = YEAR(CURRENT_DATE)+543
");
if (mysql_num_rows($r)){
$now_id = mysql_result($r,0,'now_id');
} else {
$now_id = 1;
}
$now_id = sprintf("%02d",$now_id);
echo $now_id;
SELECT
MAX(CAST(MID(pr_order,5,3) AS UNSIGNED)) AS now_id,
RIGHT(pr_order,4) AS year_id
FROM
(
SELECT 'CPMK001/2552' AS pr_order
UNION
SELECT 'CPMK003/2552' AS pr_order
UNION
SELECT 'CPMK004/2551' AS pr_order
UNION
SELECT 'CPMK002/2552' AS pr_order
) AS t1
GROUP BY year_id
HAVING year_id = YEAR(CURRENT_DATE)+543
7. TOP 5 BEST SELLER
SELECT products.id,products.name,SUM(order_details.qty) AS sum_qty
FROM orders
INNER JOIN order_details ON orders.id=order_details.order_id
INNER JOIN products ON products.id=order_details.product_id
WHERE YEAR(orders.create_at)='2009'
GROUP BY products.id
ORDER BY sum_qty DESC
LIMIT 5;
7. FIXING THAI CHARACTER SET SORT
character set utf8 จะมีปัญหากับการเรียงลำดับภาษาไทย
เช่น กางเกง และ เกม จะเรียงลำดับห่างกัน เพราะ collation ของ utf8 จะนับเอาตัวอักษรตัวแรกเป็นตัวเริ่มต้นเรียงลำดับ
แต่สามารถแก้ไขโดยการ convert character set ให้เป็น tis620 ก่อน
SELECT * FROM tb ORDER BY CONVERT (NAME USING tis620);
8. Find Related Post
SELECT COUNT(posts_tags.tag_id) ct FROM posts_tags
WHERE posts_tags.tag_id IN
(
SELECT posts_tags.tag_id
FROM posts_tags
WHERE posts_tags.post_id='10'
)
GROUP BY post_id
ORDER BY ct DESC LIMIT 10
;
9. SELECT between date or datetime
SELECT CAST(created AS DATETIME)
FROM posts
WHERE created BETWEEN CAST('2009-02-01' AS DATETIME) AND CAST('2009-06-01' AS DATETIME);
10. SELECT ORDER BY ID ASC LIMIT 5 OF SELECT ORDER BY ID DESC
$rs = mysql_query('SET @lim:=0;');
$rs = mysql_query('SELECT * FROM (SELECT id FROM posts ORDER BY id DESC) AS t1 WHERE (@lim:=(@lim+1)) < 5 ORDER BY id ASC');
echo mysql_error();
while($r = mysql_fetch_assoc($rs)){
echo $r['id'],'<br','>';
}
11. SEARCH MULTI TABLE
$sql = "
select column_1 as c1 from tb1 where column_1 like '%notebook%'
union
select column_2 as c1 from tb2 where column_2 like '%notebook%'
union
select column_3 as c1 from tb3 where column_3 like '%notebook%'
";
$rs = mysql_query($sql);
while($r = mysql_fetch_assoc($rs)){
echo '<br','>',$r['c1'];
}
ไม่มีความคิดเห็น:
แสดงความคิดเห็น