Làm cách nào để nhóm dữ liệu trong mysql?
|
Mệnh đề GROUP BY của MySQL được sử dụng trong câu lệnh SELECT để thu thập dữ liệu trên nhiều bản ghi và nhóm các kết quả theo một hoặc nhiều cột Show
cú phápCú pháp của mệnh đề GROUP BY trong MySQL là SELECT expression1, expression2, .. expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, .. expression_n;Tham số hoặc Đối sốbiểu thức1, biểu thức2,. biểu thức_nCác biểu thức không được gói gọn trong hàm tổng hợp và phải được bao gồm trong mệnh đề GROUP BY. hàm_tổng hợpMột hàm chẳng hạn như các hàm SUM, COUNT, MIN, MAX hoặc AVG. tableCác bảng mà bạn muốn truy xuất các bản ghi từ. Phải có ít nhất một bảng được liệt kê trong mệnh đề TỪ. điều kiện WHERETùy chọn. Các điều kiện phải được đáp ứng để hồ sơ được chọnVí dụ - Sử dụng hàm SUMHãy xem ví dụ truy vấn MySQL GROUP BY sử dụng hàm SUM Ví dụ MySQL GROUP BY này sử dụng hàm SUM để trả về tên của sản phẩm và tổng số lượng (đối với sản phẩm) SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product; Vì bạn đã liệt kê một cột (trường sản phẩm) trong câu lệnh SELECT không được gói gọn trong hàm SUM, nên bạn phải sử dụng Mệnh đề GROUP BY. Do đó, trường sản phẩm phải được liệt kê trong mệnh đề GROUP BY Ví dụ - Sử dụng hàm COUNTHãy xem cách chúng ta có thể sử dụng mệnh đề GROUP BY với hàm COUNT trong MySQL Ví dụ GROUP BY này sử dụng hàm MAX để trả về tên của từng bộ phận và mức lương tối đa trong bộ phận Bạn có thể sử dụng GROUP BY để nhóm các giá trị từ một cột và nếu muốn, hãy thực hiện các phép tính trên cột đó. Bạn có thể sử dụng COUNT, SUM, AVG, v.v. , chức năng trên cột được nhóm Để hiểu mệnh đề GROUP BY, hãy xem xét bảng employee_tbl có các bản ghi sau: mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | 170 | | 3 | Jack | 2007-04-06 | 100 | | 4 | Jill | 2007-04-06 | 220 | | 5 | Zara | 2007-06-06 | 300 | | 5 | Zara | 2007-02-06 | 350 | +------+------+------------+--------------------+ 7 rows in set (0.00 sec) Bây giờ, giả sử dựa trên bảng trên, chúng tôi muốn đếm số ngày mỗi nhân viên đã làm việc Nếu chúng ta viết một truy vấn SQL như sau, thì chúng ta sẽ nhận được kết quả sau - mysql> SELECT COUNT(*) FROM employee_tbl; +---------------------------+ | COUNT(*) | +---------------------------+ | 7 | +---------------------------+ Nhưng điều này không phục vụ mục đích của chúng tôi, chúng tôi muốn hiển thị tổng số trang được nhập bởi mỗi người một cách riêng biệt. Điều này được thực hiện bằng cách sử dụng các hàm tổng hợp kết hợp với mệnh đề GROUP BY như sau - Tìm hiểu về lệnh GROUP BY của MySQL để nhóm dữ liệu của bảng theo các thao tác khác nhau được thực hiện trên bảng với các ví dụ Mệnh đề GROUP BY của MySQL được sử dụng như một mệnh đề tùy chọn cùng với lệnh SELECT và được sử dụng để tóm tắt dữ liệu trong các hàng của bảng Mệnh đề GROUP BY là mệnh đề quan trọng được sử dụng để kết hợp dữ liệu dựa trên giá trị cột và thực hiện các hoạt động khác nhau như COUNT, SUM, AVERAGE, v.v. => Nhấp vào đây để xem toàn bộ loạt bài hướng dẫn về MySQL Bạn sẽ học được gì NHÓM MySQL THEONó có rất nhiều trường hợp sử dụng quan trọng trong khi phân tích dữ liệu giữa một hoặc nhiều bảng (được truy vấn bằng MySQL THAM GIA) – Ví dụ: xác định tổng doanh số trong một tháng/quý/năm nhất định, v.v. bằng cách sử dụng NHÓM THEO các phần khác nhau của các cột hiện có, v.v. cú pháp SELECT column1, column2, column3 FROM tableName GROUP BY column1, [column2] [HAVING condition] Hãy cố gắng hiểu cú pháp này
Dữ liệu mẫu Giả sử rằng chúng ta có một bảng ĐƠN HÀNG chứa thông tin chi tiết về các đơn đặt hàng của khách hàng được đặt ở các cửa hàng khác nhau Chúng tôi sẽ tạo 3 bảng với các trường sau #1) ĐƠN HÀNG
#2) ĐẶT HÀNG_DETAILS
#3) CHI TIẾT SẢN PHẨM
Hãy tạo các bảng này và chèn dữ liệu mẫu vào các bảng này CREATE TABLE `orders` ( `order_id` INT NOT NULL, `customer_name` VARCHAR(255), `city` VARCHAR(255), `order_total` DECIMAL(5,2), `order_date` VARCHAR(255), PRIMARY KEY (order_id) ) CREATE TABLE `order_details` ( `order_id` INT, `product_id` INT, `quantity` INT, FOREIGN KEY (product_id) REFERENCES product_details(product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ) CREATE TABLE `product_details` ( `product_id` INT NOT NULL, `product_name` VARCHAR(100), PRIMARY KEY(product_id)); ------------------------------------------------------------------------ // insert data into orders table ------------------------------------------------------------------------ INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52"); ------------------------------------------------------------------------------- // insert data into order_details table ------------------------------------------------------------------------------- INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2); ------------------------------------------------------------------------------- // insert data into product_details table ------------------------------------------------------------------------------- INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar'); Vui lòng tham khảo hình ảnh của các bảng với dữ liệu thử nghiệm như được tạo ở trên THÔNG TIN CHI TIẾT SẢN PHẨM ĐƠN HÀNG ORDER_DETAILS NHÓM THEO Ví dụTrong phần này, chúng ta sẽ thấy các cách khác nhau mà chúng ta có thể sử dụng mệnh đề GROUP_BY để tổng hợp thông tin ở các cấp độ khác nhau Lưu ý – Hiển thị đầu ra hàng giới hạn (không có hàng nào > 20 để tránh nhiều đầu ra từ bảng) NHÓM đơn giản THEOHãy xem một số ví dụ đối với dữ liệu thử nghiệm mà chúng tôi đã tạo ở trên Trong ví dụ dưới đây, lệnh là NHÓM dữ liệu từ bảng của ORDER theo THÀNH PHỐ – i. e. chúng tôi muốn có được số lượng đơn đặt hàng từ các thành phố khác nhau SELECT city, COUNT(*) AS no_of_orders FROM orders GROUP BY city thành phốno_of_ordersKöln1Scena/Schenna1Wyoming1Surendranagar1Belfast1Götzis1Rostock1Bocchigliero1San Rosendo1Essex1Casole d'Elsa1 Đây là một ví dụ khác, nơi chúng tôi muốn NHÓM THEO thành phố và tìm tổng doanh thu được tạo từ mỗi thành phố SELECT city, SUM(order_total) AS revenue FROM orders GROUP BY city doanh thu thành phốKöln117. 79Scena/Schenna95. 73Wyoming37. 28 Chương 95. 55Belfast115. 85Götzis123. 7Rostock88. 2Bocchigliero52. 68San Rosedo124. 01Essex58. 99 NHÓM THEO HAVINGHãy xem một ví dụ sử dụng mệnh đề HAVING với GROUP BY. Chúng tôi sẽ cố gắng tìm doanh thu cho tất cả các thành phố có tên bắt đầu bằng chữ 'C' ________số 8cityno_of_ordersCasole d'Elsa1Cumaribo1Crystal Springs1Cherepovets1Cumberland1Cardiff1Cefalà Diana1Colico1 NHÓM THEO Với ĐẶT HÀNGChúng ta cũng có thể thêm ORDERING cùng với mệnh đề GROUP BY. Ví dụ: NHÓM THEO thành phố và số doanh thu của tên thành phố bắt đầu bằng 'S' và các hàng được liệt kê theo giá trị doanh thu giảm dần SELECT city, SUM(order_total) AS revenue FROM orders GROUP BY city HAVING city LIKE 'S%' ORDER BY revenue DESC doanh thu thành phốSan Rosedo124. 01Saint-Dié-des-Vosges115. 37St. Paul113. 78Khu vực Sunshine Coast110. 53San Giacomo degli Schiavoni96. 92Scena/Schenna95. 73 Chương 95. 55Singkawang87. 34San Ignacio75. 02Sautin68. 99Đồi thiên nga68. 85Saint-Marc60. 72San Giorgio Albanese45. 98 NHÓM THEO Với Nhiều CộtChúng ta có thể sử dụng GROUP BY với nhiều cột hoặc có thể gọi đơn giản là phân nhóm Ví dụ. Giả sử bạn có dữ liệu đơn đặt hàng và bạn muốn NHÓM doanh thu THEO – tháng và năm. Trong trường hợp này, chúng ta có thể sử dụng nhiều cột SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product;0 Truy vấn trên sẽ trả về kết quả được nhóm cho năm 2020, được nhóm theo tháng _năm_thángdoanh thu202012594. 84202011713. 17202010223. 6820209124. 0120208356. 9120207204. 1320206675. 5920205357. 0520204238. 362020340. 7920202197. 7320201132. 31 NHÓM THEO Với ROLLUPROLLUP tạo một hàng tổng hợp bổ sung, tùy thuộc vào số lượng cột được chỉ định trong truy vấn GROUP BY Đây là cú pháp sử dụng GROUP BY với ROLLUP SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product;1 Bây giờ, hãy xem một số ví dụ với tổng số – NHÓM THEO năm và doanh thu SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product;2 Lệnh ROLLUP này sẽ tạo ra một hàng bổ sung là tổng của tất cả các mục trong GROUP BY – i. e. cùng với các mục nhập của năm 2019, 2020 và 2021, bạn sẽ thấy mục nhập có giá trị null cho năm và tổng doanh thu cho cả 3 dữ liệu được nhóm đầu ra _yearrevenue2019278. 6420203858. 5720213233. 69NULL7370. 9 Thêm một cột khác vào nhóm bằng cách sử dụng lệnh bên dưới SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product;3 Tại đây, bạn sẽ thấy thêm một hàng CUỘN LÊN đối với mỗi cột NHÓM THEO – i. e. tổng hàng tháng cho tất cả các năm (cho tất cả các tháng). Ví dụ: bạn sẽ thấy thêm một hàng CUỘN LÊN cho tháng 1 năm 2020 và tháng 1 năm 2021 và một hàng CUỘN LÊN cuối cùng khác để tính tổng tất cả các giá trị Cột cuộn lên được đánh dấu _năm_thángdoanh thu20201132. 3120211508. 59NULL1640. 920202197. 7320212380. 97NULL2578. 72020340. 79202010223. 68202110355. 8NULL10579. 48201911157. 73202011713. 1720211199. 28NULL11970. 18201912120. 91202012594. 84NULL12715. 75NULLNULL7370. 9 NHÓM THEO Với THAM GIAGROUP BY cũng có thể được kết hợp với MySQL THAM GIA Ví dụ. Nếu chúng tôi muốn tìm tên sản phẩm và tổng số lượng sản phẩm trong tất cả các đơn đặt hàng, chúng tôi cần tham gia bảng order_details và product_details, vì bảng order_details tham chiếu product_id dưới dạng KHÓA NGOẠI TỆ từ bảng product_details Hãy xem cách chúng ta có thể sử dụng THAM GIA để tìm nạp loại dữ liệu đó CREATE TABLE `orders` ( `order_id` INT NOT NULL, `customer_name` VARCHAR(255), `city` VARCHAR(255), `order_total` DECIMAL(5,2), `order_date` VARCHAR(255), PRIMARY KEY (order_id) ) CREATE TABLE `order_details` ( `order_id` INT, `product_id` INT, `quantity` INT, FOREIGN KEY (product_id) REFERENCES product_details(product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ) CREATE TABLE `product_details` ( `product_id` INT NOT NULL, `product_name` VARCHAR(100), PRIMARY KEY(product_id)); ------------------------------------------------------------------------ // insert data into orders table ------------------------------------------------------------------------ INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52"); ------------------------------------------------------------------------------- // insert data into order_details table ------------------------------------------------------------------------------- INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2); ------------------------------------------------------------------------------- // insert data into product_details table ------------------------------------------------------------------------------- INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar');0 Trong truy vấn trên – bạn có thể thấy rằng chúng tôi đã thực hiện INNER JOIN trên bảng PRODUCT_DETAILS để tìm nạp product_name và GROUP BY product_name so với tổng số lượng từ bảng ORDER_DETAILS đầu ra prod_nametotal_quantityBánh quy31Sô cô la27Bột giặt34Bột mì12Pin34Nước lau sàn 32Bàn chải đánh răng28Trái cây17Rau củ32Đường15 Các câu hỏi thường gặpQ #1) Có thể sử dụng GROUP BY và ORDER BY cùng nhau không? Câu trả lời. Có – GROUP BY có thể được sử dụng cùng với mệnh đề ORDER BY để sắp xếp hoặc sắp xếp các hàng được nhóm kết quả theo một thứ tự cụ thể – như tăng dần hoặc giảm dần. Ví dụ: giả sử chúng ta muốn NHÓM THEO doanh thu theo thứ tự giảm dần với NHÓM THEO NĂM từ bảng ĐƠN HÀNG CREATE TABLE `orders` ( `order_id` INT NOT NULL, `customer_name` VARCHAR(255), `city` VARCHAR(255), `order_total` DECIMAL(5,2), `order_date` VARCHAR(255), PRIMARY KEY (order_id) ) CREATE TABLE `order_details` ( `order_id` INT, `product_id` INT, `quantity` INT, FOREIGN KEY (product_id) REFERENCES product_details(product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ) CREATE TABLE `product_details` ( `product_id` INT NOT NULL, `product_name` VARCHAR(100), PRIMARY KEY(product_id)); ------------------------------------------------------------------------ // insert data into orders table ------------------------------------------------------------------------ INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52"); ------------------------------------------------------------------------------- // insert data into order_details table ------------------------------------------------------------------------------- INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2); ------------------------------------------------------------------------------- // insert data into product_details table ------------------------------------------------------------------------------- INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar');1 _yearrevenue20203858. 5720213233. 692019278. 64 Q #2) Làm cách nào bạn có thể chỉ định các điều kiện trong mệnh đề GROUP BY? Câu trả lời. Tương tự như mệnh đề WHERE trong các truy vấn CHỌN thông thường, trong lệnh GROUP BY, chúng ta có thể sử dụng mệnh đề HAVING để chỉ định bất kỳ điều kiện cụ thể nào mà chúng ta muốn áp dụng cho tiêu chí GROUPED Ví dụ: Giả sử chúng ta muốn NHÓM THEO tên thành phố trong bảng ĐẶT HÀNG và muốn chụp các thành phố có tên bắt đầu bằng 'K' Chúng ta có thể sử dụng lệnh dưới đây CREATE TABLE `orders` ( `order_id` INT NOT NULL, `customer_name` VARCHAR(255), `city` VARCHAR(255), `order_total` DECIMAL(5,2), `order_date` VARCHAR(255), PRIMARY KEY (order_id) ) CREATE TABLE `order_details` ( `order_id` INT, `product_id` INT, `quantity` INT, FOREIGN KEY (product_id) REFERENCES product_details(product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ) CREATE TABLE `product_details` ( `product_id` INT NOT NULL, `product_name` VARCHAR(100), PRIMARY KEY(product_id)); ------------------------------------------------------------------------ // insert data into orders table ------------------------------------------------------------------------ INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52"); ------------------------------------------------------------------------------- // insert data into order_details table ------------------------------------------------------------------------------- INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2); ------------------------------------------------------------------------------- // insert data into product_details table ------------------------------------------------------------------------------- INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar');2 DOANH THU THÀNH PHỐKöln117. 79Kech42. 63Khanwal113. 46 SỬ DỤNG HOẶC với HAVING – Dưới đây là lệnh bao gồm các thành phố có tên như – ‘SA’ CREATE TABLE `orders` ( `order_id` INT NOT NULL, `customer_name` VARCHAR(255), `city` VARCHAR(255), `order_total` DECIMAL(5,2), `order_date` VARCHAR(255), PRIMARY KEY (order_id) ) CREATE TABLE `order_details` ( `order_id` INT, `product_id` INT, `quantity` INT, FOREIGN KEY (product_id) REFERENCES product_details(product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ) CREATE TABLE `product_details` ( `product_id` INT NOT NULL, `product_name` VARCHAR(100), PRIMARY KEY(product_id)); ------------------------------------------------------------------------ // insert data into orders table ------------------------------------------------------------------------ INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52"); ------------------------------------------------------------------------------- // insert data into order_details table ------------------------------------------------------------------------------- INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2); ------------------------------------------------------------------------------- // insert data into product_details table ------------------------------------------------------------------------------- INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar');3 DOANH THU THÀNH PHỐKöln117. 79San Rosedo124. 01Saint-Dié-des-Vosges115. 37Saint-Marc60. 72Kech42. 63San Giacomo degli Schiavoni96. 92Sautin68. 99Khanwal113. 46San Ignacio75. 02San Giorgio Albanese45. 98 Q #3) Làm cách nào để nhóm theo hai cột trong MySQL? Trả lời. Có, GROUP BY cũng có thể được sử dụng trên nhiều cột. Điều quan trọng cần lưu ý ở đây là khi sử dụng nhiều cột, tùy chọn cột sẽ được thực hiện từ trái sang phải. Cú pháp sử dụng GROUP BY với nhiều cột CREATE TABLE `orders` ( `order_id` INT NOT NULL, `customer_name` VARCHAR(255), `city` VARCHAR(255), `order_total` DECIMAL(5,2), `order_date` VARCHAR(255), PRIMARY KEY (order_id) ) CREATE TABLE `order_details` ( `order_id` INT, `product_id` INT, `quantity` INT, FOREIGN KEY (product_id) REFERENCES product_details(product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ) CREATE TABLE `product_details` ( `product_id` INT NOT NULL, `product_name` VARCHAR(100), PRIMARY KEY(product_id)); ------------------------------------------------------------------------ // insert data into orders table ------------------------------------------------------------------------ INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1000,"Roth W. Porter","Köln","117.79","2020-12-28 12:23:13"),(1001,"Abbot G. Mann","Scena/Schenna","95.73","2020-06-14 15:39:50"),(1002,"Christian Park","Wyoming","37.28","2021-06-23 05:18:13"),(1003,"Florence Pace","Surendranagar","95.55","2020-11-05 11:17:50"),(1004,"Calvin Mathis","Belfast","115.85","2021-10-06 22:23:25"),(1005,"Portia X. Sutton","Götzis","123.70","2020-06-11 17:50:17"),(1006,"Arden Q. Morton","Rostock","88.20","2021-07-02 03:11:57"),(1007,"Felix Hurley","Bocchigliero","52.68","2020-05-05 22:43:22"),(1008,"Hilary F. Cortez","San Rosendo","124.01","2020-09-10 17:55:19"),(1009,"Jordan Farrell","Essex","58.99","2020-08-28 18:44:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1010,"Whilemina G. Morris","Casole d'Elsa","45.74","2021-06-14 04:09:53"),(1011,"Malik E. Mcclure","Tambov","50.40","2020-08-26 10:04:10"),(1012,"Adam Keller","St. Paul","113.78","2019-11-26 17:25:13"),(1013,"Nero Curtis","Gliwice","91.24","2020-10-15 03:22:04"),(1014,"Axel W. Bruce","Minneapolis","60.28","2020-05-25 13:20:57"),(1015,"Buffy G. Martinez","Arsoli","59.25","2021-01-22 19:04:20"),(1016,"Blaze Fox","Hastings","59.61","2020-07-14 18:23:00"),(1017,"Cameran Velasquez","Saint-Dié-des-Vosges","115.37","2021-04-04 22:29:55"),(1018,"Janna Solis","Molina","41.57","2020-05-27 08:44:09"),(1019,"Gil K. Rivera","Tranås","111.41","2020-11-14 00:57:47"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1020,"Adena Y. Barnett","Mal","43.95","2019-11-17 16:00:21"),(1021,"Idola Tyler","Ingraj Bazar","41.42","2021-01-29 16:16:10"),(1022,"Josephine Gilliam","Berwick","97.35","2020-12-27 09:27:30"),(1023,"Jenna Whitaker","Gatchina","109.41","2020-08-01 00:04:40"),(1024,"Damon Norman","Saint-Marc","60.72","2021-02-19 04:49:02"),(1025,"Meredith Maldonado","Libramont-Chevigny","25.04","2020-10-31 06:06:15"),(1026,"India X. Hurley","Bottidda","55.93","2021-10-14 12:23:01"),(1027,"Chantale R. Baird","Niort","111.73","2020-07-21 15:54:08"),(1028,"Chanda Stafford","Magdeburg","111.64","2021-01-08 16:52:49"),(1029,"India Tate","Yangju","62.27","2021-07-29 11:29:52"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1030,"Ryder Q. Clayton","Bad Nauheim","73.42","2021-02-20 04:45:11"),(1031,"Griffin I. Ramos","Kech","42.63","2021-10-04 23:49:57"),(1032,"Wang H. Saunders","Fusagasugá","65.72","2020-04-20 21:23:11"),(1033,"Juliet K. Morse","Pugwash","105.93","2020-05-01 07:06:23"),(1034,"Heather T. Mcmahon","Altamura","28.44","2021-03-09 03:39:44"),(1035,"Shannon S. Odonnell","Vandoies/Vintl","45.39","2021-09-10 23:48:49"),(1036,"Jacob Torres","San Giacomo degli Schiavoni","96.92","2020-12-12 09:17:43"),(1037,"Clayton L. Garrison","Watson Lake","76.72","2020-11-25 10:55:18"),(1038,"Naida S. Douglas","Bergisch Gladbach","66.65","2021-08-30 03:46:09"),(1039,"Adara W. Bernard","Alma","104.99","2020-01-19 13:43:09"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1040,"Aphrodite J. Carrillo","Banda","74.73","2021-09-12 03:32:31"),(1041,"Cameron Hendricks","Pilibhit","83.08","2019-12-26 17:44:22"),(1042,"Dale J. Marsh","OugrŽe","60.17","2021-02-19 06:04:12"),(1043,"Plato Jimenez","Aubagne","62.75","2020-10-24 09:14:01"),(1044,"Vincent F. Burris","Drogenbos","62.18","2021-10-29 03:08:20"),(1045,"Reed Jordan","Uppingham. Cottesmore","66.00","2021-02-11 11:01:36"),(1046,"Giacomo Paul","Cumaribo","115.53","2020-12-27 18:45:14"),(1047,"Kuame A. Norman","Darlington","54.62","2020-05-23 11:07:50"),(1048,"Simon Cotton","Losino-Petrovsky","113.52","2021-09-27 05:54:02"),(1049,"Camden V. Ramos","Dutse","79.21","2021-10-13 02:39:30"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1050,"Samson Willis","Puerto Vallarta","27.80","2021-01-25 05:05:52"),(1051,"Regina N. Raymond","Pescantina","27.32","2020-01-11 06:51:09"),(1052,"Constance Schwartz","Blieskastel","73.52","2020-12-30 16:47:53"),(1053,"Hamilton Church","Ercis","66.38","2020-11-08 04:56:46"),(1054,"Aidan T. Davenport","Tonalá","37.83","2019-12-04 13:15:41"),(1055,"Suki Rasmussen","Dyatkovo","105.50","2020-11-06 22:26:25"),(1056,"Armando Hoover","Marano Lagunare","88.68","2021-01-08 05:21:47"),(1057,"Baker Oliver","Mandi Burewala","116.19","2021-09-23 09:12:28"),(1058,"Tana Le","Crystal Springs","85.09","2021-02-08 01:26:23"),(1059,"Finn Dejesus","Swan Hill","68.85","2021-05-10 04:57:17"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1060,"Shafira Cleveland","El Quisco","103.65","2020-04-13 19:18:25"),(1061,"Hector C. Henson","Singkawang","87.34","2021-06-16 04:01:45"),(1062,"Donna X. Craig","Graven Grez-Doiceau","32.79","2020-07-10 04:39:11"),(1063,"Kenyon P. Wilson","Lives-sur-Meuse","38.66","2021-06-18 06:38:19"),(1064,"Harlan R. Wagner","Lipetsk","35.57","2021-02-24 21:18:25"),(1065,"Belle Hampton","Parla","69.93","2021-06-03 02:04:57"),(1066,"Sean K. Ayers","Morpeth","68.03","2021-06-17 04:01:53"),(1067,"Montana Randall","Sautin","68.99","2020-04-04 23:45:47"),(1068,"Kelsie J. Bond","Cherepovets","99.28","2021-11-07 23:07:55"),(1069,"Hashim Y. Burch","Villarrica","57.76","2020-02-16 18:02:08"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1070,"Adria F. Kline","Sunshine Coast Regional District","110.53","2021-01-15 14:58:55"),(1071,"Aline Bryant","Cumberland","74.46","2020-06-07 02:10:17"),(1072,"Shoshana F. Patterson","Tczew","80.63","2020-06-15 14:40:21"),(1073,"Ferris Casey","Baschi","31.61","2021-04-16 02:12:38"),(1074,"Brianna Vargas","Khanewal","113.46","2020-11-07 10:59:43"),(1075,"Beatrice C. Fleming","San Ignacio","75.02","2020-06-10 10:35:10"),(1076,"Teagan W. Bush","Laval","38.63","2020-02-03 05:39:34"),(1077,"Audra G. Harmon","Legal","41.97","2020-05-07 00:15:01"),(1078,"Aaron Maldonado","Montauban","92.13","2020-08-01 16:27:49"),(1079,"Amir Hurst","Villers-le-Peuplier","93.73","2020-12-29 06:32:11"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1080,"Nell L. Aguirre","Hanam","109.31","2021-04-11 11:32:51"),(1081,"Dustin Love","Minucciano","29.57","2020-06-28 06:39:49"),(1082,"Judah Frazier","Monte San Savino","28.57","2021-05-24 18:44:27"),(1083,"Macey Ingram","Rouen","68.68","2021-07-24 17:09:53"),(1084,"Jayme H. Blackburn","San Giorgio Albanese","45.98","2020-08-29 02:21:02"),(1085,"Xavier Gould","Eluru","92.44","2020-06-25 08:43:08"),(1086,"Desiree Buckley","Rotem","37.64","2020-11-11 21:28:12"),(1087,"Elvis Contreras","Montluçon","28.15","2021-04-10 05:56:04"),(1088,"Felix Q. Whitaker","Bristol","40.79","2020-03-21 03:13:03"),(1089,"Katell Willis","Quarona","101.34","2020-02-03 02:05:00"); INSERT INTO `orders` (`order_id`,`customer_name`,`city`,`order_total`,`order_date`) VALUES (1090,"Austin T. Casey","Cardiff","108.22","2021-05-06 11:55:54"),(1091,"Dalton Q. Sims","Cefalà Diana","104.04","2020-06-08 01:50:00"),(1092,"Althea C. Townsend","Ruda","44.65","2020-10-12 07:46:17"),(1093,"Ruby Rivas","Colico","108.03","2021-07-09 18:34:27"),(1094,"Fletcher H. Moses","Leugnies","69.27","2021-01-16 22:59:23"),(1095,"Britanney D. Pitts","Minneapolis","86.91","2021-09-23 01:17:49"),(1096,"Ginger A. Roth","Beho","106.51","2020-11-08 16:54:30"),(1097,"Merritt A. Humphrey","Pomarolo","50.84","2021-07-25 01:10:52"),(1098,"Ina Rush","Herne","84.31","2021-08-17 23:27:09"),(1099,"Dana Rasmussen","Gary","57.83","2021-09-06 12:48:52"); ------------------------------------------------------------------------------- // insert data into order_details table ------------------------------------------------------------------------------- INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1089,7,3),(1024,5,1),(1067,5,3),(1089,6,3),(1027,3,4),(1054,4,1),(1070,6,4),(1026,1,3),(1039,8,2),(1009,8,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1016,3,3),(1051,7,4),(1095,6,4),(1098,4,2),(1052,2,4),(1064,1,4),(1052,7,4),(1004,1,4),(1099,5,2),(1075,9,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1069,5,3),(1071,2,2),(1088,5,3),(1090,7,1),(1021,1,4),(1076,5,4),(1060,5,1),(1029,10,1),(1035,9,4),(1095,10,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1045,1,2),(1043,6,3),(1073,5,2),(1082,1,1),(1050,5,1),(1057,3,4),(1062,2,3),(1029,1,1),(1020,9,3),(1003,3,1); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1074,2,2),(1025,1,2),(1025,6,4),(1083,7,1),(1072,6,2),(1045,9,4),(1098,9,2),(1023,5,2),(1098,6,3),(1052,10,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1066,2,2),(1018,9,3),(1061,3,3),(1007,2,2),(1093,9,1),(1035,3,2),(1090,3,4),(1021,9,2),(1095,9,1),(1004,1,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1006,6,3),(1033,5,4),(1022,7,4),(1051,1,2),(1000,7,2),(1017,9,4),(1016,5,3),(1083,10,2),(1018,1,1),(1018,3,4); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1039,1,3),(1051,4,3),(1024,6,4),(1048,10,4),(1091,2,4),(1023,9,2),(1065,8,4),(1098,10,2),(1018,8,1),(1043,8,3); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1084,6,1),(1009,8,3),(1015,4,3),(1041,7,1),(1046,7,4),(1071,6,1),(1023,3,3),(1031,7,4),(1085,9,1),(1014,2,2); INSERT INTO `order_details` (`order_id`,`product_id`,`quantity`) VALUES (1047,2,3),(1012,9,2),(1079,2,3),(1019,3,1),(1021,5,3),(1003,5,2),(1088,3,2),(1091,3,3),(1048,4,3),(1060,9,2); ------------------------------------------------------------------------------- // insert data into product_details table ------------------------------------------------------------------------------- INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (1,'Biscuits'),(2,'Chocolates'), (3, 'Washing Powder'),(4, 'Flour'), (5, 'Batteries'),(6,'Floor cleaner'),(7,'Toothbrush'),(8,'Fruits'),(9,'Vegetables'),(10,'Sugar');4 Phần kết luậnTrong hướng dẫn này, chúng ta đã tìm hiểu về mệnh đề GROUP BY của MySQL. Chúng ta đã thấy cách mệnh đề GROUP BY có thể được sử dụng để tạo các nhóm dữ liệu tổng hợp có sẵn trong một hoặc nhiều bảng Chúng tôi cũng đã xem xét các cách khác nhau mà chúng tôi có thể sử dụng NHÓM THEO để tổng hợp nhiều cột và sử dụng các tùy chọn như ROLLUP, GROUPING BY THAM GIA, v.v. GROUP BY là một trong những lệnh mạnh mẽ và được sử dụng rộng rãi nhất để chạy phân tích trên dữ liệu đã cho và đưa ra các quyết định kinh doanh ngoài phân tích đã tiến hành Làm cách nào để sử dụng chức năng nhóm trong MySQL?Câu lệnh MySQL GROUP BY
. Câu lệnh GROUP BY thường được sử dụng với các hàm tổng hợp ( COUNT() , MAX() , MIN() , SUM() , AVG() ) để nhóm tập kết quả theo một hoặc nhiều . .
Làm cách nào để thêm nhóm trong MySQL?Quy trình . Từ ngăn điều hướng, đi đến Bảo vệ > Cơ sở dữ liệu. . Nhấp vào phiên bản mà bạn muốn thêm nhóm cơ sở dữ liệu vào. . Trong phần Nhóm cơ sở dữ liệu, nhấp vào Thêm nhóm cơ sở dữ liệu. . Trong hộp Tên nhóm cơ sở dữ liệu, hãy nhập tên cho nhóm cơ sở dữ liệu Từ danh sách Gói, chọn một gói máy chủ MySQL có bộ nhóm không?Bắt đầu với MySQL 8. 0. 1, máy chủ hỗ trợ chức năng SQL GROUPING . Hàm GROUPING được sử dụng để phân biệt giữa NULL đại diện cho tập hợp tất cả các giá trị trong một hàng siêu tổng hợp (do thao tác ROLLUP tạo ra) với NULL trong một hàng thông thường.
Làm cách nào để sử dụng GROUP BY cho nhiều cột trong MySQL?Để sắp xếp các dữ liệu tương tự (giống hệt nhau) thành các nhóm, chúng ta sử dụng mệnh đề SQL GROUP BY. Mệnh đề SQL GROUP BY được sử dụng cùng với một số hàm tổng hợp để nhóm các cột có cùng giá trị trong các hàng khác nhau. Chúng tôi thường sử dụng mệnh đề GROUP BY với câu lệnh SELECT, mệnh đề WHERE và mệnh đề ORDER BY . |
