mysql complex query

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'];
}