1-1 Hàm CASE
** Biểu thị sự phân chia điều kiện trong SQL Hàm CASE là một kĩ thuật rất tiện đồng thời quan trọng cần thiết để thực hiện quá trình phân loại điều kiện trong SQL. Chương này chúng ta sẽ học cách dùng hàm CASE cùng với những ví dụ về phân loại điều kiện đối với kết quả thu thập, tái phân loại các ...
** Biểu thị sự phân chia điều kiện trong SQL Hàm CASE là một kĩ thuật rất tiện đồng thời quan trọng cần thiết để thực hiện quá trình phân loại điều kiện trong SQL. Chương này chúng ta sẽ học cách dùng hàm CASE cùng với những ví dụ về phân loại điều kiện đối với kết quả thu thập, tái phân loại các thể code, thay đổi dãy, ... **
Khởi đầu
Hàm CASE đã được đưa vào sử dụng như một hàm cơ bản từ SQL-92. Không biết có phải đây là công cụ mới so với những công cụ khác mà người ta không biết đến sự tiện lợi hay giá trị thật của nó mà không dùng, thay vào đó người ta dùng các hàm thay thế khác là bản giản lược của hàm CASE như DECODE (Oracle), IF (MySQL). Tuy nhiên cũng như Celco đã nói rằng "Trong những gì được thêm vào SQL-92 thì có lẽ đây là thứ hữu dụng nhất", từ khi người ta dùng hàm CASE thì những gì có thể làm trên SQL đã trở nên rộng hơn, cách viết cũng trở nên thông minh hơn. Hơn nữa, dùng hàm CASE thì tính sử dụng của code sẽ cao hơn. Nhất là đối với những người dùng Oracle dùng hàm DECODE thì nhất định hay thay thế sử dụng hàm CASE
*1 DECODE là hàm rất quen thuộc đối với những người dùng Oracle nhưng có 4 điểm sau đây sẽ kém hơn hàm CASE:
- Đây là từ "địa phương" chỉ dùng cho Oracle nên không có tính tương thích.
- Lượng phân loại chỉ được giới hạn ở 127 (số lượng cao nhất của argument là 255, một lần hiển thị phân loại thì cần 2 argument)
- Nếu tăng thêm lượng phân loại thì code sẽ trở nên rất khó đọc
- Khả năng ghi chú rất nghèo nàn. Về cơ bản thì chúng ta không thể lấy hàm đã thêm vị từ vào argument. Tất nhiên cũng không thể tạo subquery (một truy vấn chọn bên trong một truy vấn chọn).
Nhập môn: Hàm CASE là gì?
Đầu tiên phần này sẽ giới thiệu từ ngữ pháp cơ bản nhất. Trong dãy lệnh hàm CASE sẽ có 2 loại là hàm CASE thông thường (simple case) và hàm CASE tìm kiếm (searched case). Hai hàm CASE này sẽ được viết như dưới đây.
Cách viết hàm CASEHàm CASE thông thường
CASE sex
WHEN '1' THEN 'Nam'
When '2' THEN 'Nữ'
ELSE 'Khác' END
Hàm CASE tìm kiếm
CASE WHEN sex = '1' WHEN 'Nam'
WHEN sex = '2' WHEN 'Nữ'
ELSE 'Khác' END
Trong hai hàm này thì thì cả hai bên đều có có những động tác giống nhau. Là động tác trong dãy 'sex' nếu là '1' thì sẽ đọc lại kết quả 'Nam' còn nếu là '2' thì sẽ đọc vào kết quả 'Nữ'. Hàm CASE thông thường có thể viết đơn giản như tên của nó nhưng điều nó có thể làm thì sẽ bị giới hạn. Điều kiện để viết được câu CASE thông thường là cũng có thể viết được bằng hàm CASE tìm kiếm nên trong quyển sách này về sau thì hàm CASE tìm kiếm sẽ được sử dụng nhiều hơn.
Mặt khác, bình luận của hàm CASE là nếu câu điều kiện trong WHEN trả lại kết quả đúng thì sẽ bị dừng giữa chừng ngay tại thời điểm đó, những câu điều kiện trong WHEN còn lại sẽ không được đọc, bị lơ đi nên chúng ta cần chú ý đoạn này trong khi lập trình. Để tránh những rối loạn không đáng có thì chúng ta nên viết câu điều kiện WHEN mang tính loại trừ.
Ví dụ nêu lên những câu điều kiện WHEN sau sẽ bị lơ đi, không được đọcVí dụ nếu viết như sau thì ở kết quả sẽ không bao giờ hiện lên là 'Số 2'
CASE WHEN col_1 IN ('a', 'b') THEN 'Số 1'
WHEN col_1 IN ('a') THEN 'Số 2'
ELSE 'Khác' END
Trong khi sử dụng hàm CASE thì chúng ta phải chú ý những điểm sau.
Điểm chú ý 1: Thống nhất dạng dữ liệu kết quả sau khi phân kì
Tất nhiên, có nghĩa là dạng của dữ liệu trả về từ hàm CASE cần thiết phải được thống nhất. Ở một lần phân loại trả lại kết quả chữ, rồi lần phân loại khác lại trả lại kết quả số, thì cách viết này không thể chấp nhận được.
Điểm chú ý 2: Chú ý không quên viết END
Trong hàm CASE thì lỗi hay gặp nhất đó chính là quên viết END ở cuối hàm. Tất nhiên nếu quên không viết thì sẽ có tin nhắn báo lỗi dễ hiểu được trả về nên không trở thành một vấn đề lớn nhưng hơn một nửa lý do chúng ta có suy nghĩ "Câu lệnh thì viết đúng mà chương trình không chạy" là do lỗi này nên chúng ta nên chú ý
Điểm chú ý 3: Nhất định phải viết câu ELSE
Khác với câu END đằng trước thì ELSE là một option nên nếu không viết thì chương trình cũng không bị lỗi. Trong trường hợp này thì nó sẽ tự động hiểu là [ELSE NULL]. Tuy nhiên nó sẽ thành nệm ấm cho những lỗi phức tạp của máy tính kiểu "Không error nhưng kết quả thì khác" (kể cả trong trường hợp NULL cũng không sao) nên chúng ta nên tạo ra thói quen viết câu ELSE một cách rõ ràng. Bằng cách như vậy thì trên code kể cả NULL có sinh ra thì nó cũng trở nên rõ ràng, trong trường hợp phải sửa chữa trong tương lai cũng có thể giảm số lỗi phải sửa.
Chuyển hệ thống code đã có thành hệ thống code mới và tập hợp lại
Trong nghiệp vụ thực hiện tổng hợp mang tính bất định thì cũng có lúc chúng ta có những nhu cầu muốn chuyển đổi hệ thống code đã có thành hệ thống code chuyên để phân tích và muốn tập hợp bằng đơn vị tạo hệ thống mới đó. Ví dụ như có trường hợp chúng ta muốn chuyển hệ thống code về các tỉnh "1: Hokkaidou,2:Aomori,...47:Okinawa" tập hợp lại thành đơn vị khu vực như Touhoku, Kantou, Kyuushuu và sẽ tính dân số bằng đơn vị đó. Chi tiết ví dụ như trường hợp dưới đây, chúng ta sẽ tập hợp lại nội dung của PopTbl được hiển thị dưới đây và yêu cầu kết quả
Bảng ban đầu trước khi tập hợp PopTbl| pref_name (Tên tỉnh) | population (Dân số) |
|---|---|
| Tokushima | 100 |
| Kagawa | 200 |
| Ehime | 150 |
| Kouchi | 200 |
| Fukuoka | 300 |
| Saga | 100 |
| Nagasaki | 200 |
| Toukyou | 400 |
| Gunma | 50 |
| Tên khu vực | Dân số |
|---|---|
| Shikoku | 650 |
| Kyuushuu | 600 |
| Khác | 450 |
Trong những trường hợp thế này thì các bạn làm thế nào? Cũng có một phương pháp là định nghĩa một view mang dãy code khu vực. Như vậy thì chúng ta phải thêm dãy có đủ những thành phần muốn sử dụng để tập hợp, tại đây thì một biến đổi mang tính động là rất khó khăn. Nếu sử dụng hàm CASE thì chúng ta có thể gọi ra bằng một SQL. Để dễ hiểu hơn thì chúng ta sẽ thay code bằng dùng key GROUP BY cho tên tỉnh (pref_name).
Phân loại lại code tỉnh thành code khu vực
SELECT CASE pref_name
WHEN 'Tokushima' THEN 'Shikoku'
WHEN 'Kagawa' THEN 'Shikoku'
WHEN 'Ehime' THEN 'Shikoku'
WHEN 'Kouchi' THEN 'Shikoku'
WHEN 'Fukuoka' THEN 'Kyuushuu'
WHEN 'Saga' THEN 'Kyuushuu'
WHEN 'Nagasaki' THEN 'Kyuushuu'
ELSE 'Khác' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN 'Tokushima' THEN 'Shikoku'
WHEN 'Kagawa' THEN 'Shikoku'
WHEN 'Ehime' THEN 'Shikoku'
WHEN 'Kouchi' THEN 'Shikoku'
WHEN 'Fukuoka' THEN 'Kyuushuu'
WHEN 'Saga' THEN 'Kyuushuu'
WHEN 'Nagasaki' THEN 'Kyuushuu'
ELSE 'Khác' END;
Điểm nhấn ở đây chính là copy hàm CASE trong câu SELECT vào GROUP BY. Nếu chỉ đơn giản chỉ định dãy trước khi thay đổi là [GROUP BY pref_name] thì chúng ta nên chú ý là sẽ không nhận được kết quả đúng như mong muốn. (Vì trong dãy không trở nên lỗi nên rất hay dễ bị bỏ qua) Mặt khác, bằng cách nghĩ giống như vậy thì chúng ta cũng có thể phân ra rồi tập hợp lại bằng hệ thống bậc theo số thích hợp. Ví dụ chúng ta muốn tra cứu số lượng tỉnh theo từng bậc class về dân số (pop_class) thì sẽ có SQL như dưới đây.
--Phân loại tỉnh theo bậc dân số
SELECT CASE pref_name
WHEN population < 100 THEN '01'
WHEN population >= 100 AND population <200 THEN '02'
WHEN population >= 200 AND population <300 THEN '03'
WHEN population > 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
FROM PopTbl
GROUP BY CASE pref_name
WHEN population < 100 THEN '01'
WHEN population >= 100 AND population <200 THEN '02'
WHEN population >= 200 AND population <300 THEN '03'
WHEN population > 300 THEN '04'
ELSE NULL END;
pop_class cnt
-------- ----
01 1
02 2
03 3
04 4
Thủ thuật này rất tiện lợi, nhưng trong 2 khu vực trong cả câu SELECT và GROUP BY thì chúng ta đều phải nhập những dữ liệu giống nhau nên điều này có một chút vất vả. Hơn nữa sau này nếu có trường hợp phải sửa chữa nội dung bên trong thì rất có thể chúng ta sẽ quên sửa nội dung trong một phần nên lỗi rất dễ xảy ra. Như vậy thì các bạn nghĩ cách viết dưới đây có tiện hơn không?
--Tái phân loại code theo đơn vị khu vực. Ví dụ 2: tập hợp hàm CASE về một khu vực
SELECT CASE pref_name
WHEN 'Tokushima' THEN 'Shikoku'
WHEN 'Kagawa' THEN 'Shikoku'
WHEN 'Ehime' THEN 'Shikoku'
WHEN 'Kouchi' THEN 'Shikoku'
WHEN 'Fukuoka' THEN 'Kyuushuu'
WHEN 'Saga' THEN 'Kyuushuu'
WHEN 'Nagasaki' THEN 'Kyuushuu'
ELSE 'Khác' END AS district,
SUM(population)
FROM PopTbl
GROUP BY dictrict;
Dãy được điền trong SELECT được đặt tên là "district" và nó được dùng trong GROUP BY. Nhưng nếu nhìn một cách chi tiết thì cách viết này không được cho phép tại SQL bình thường. Vì GROUP BY sẽ được thực hiện trước SELECT nên tên đặc biệt khác được đặt trong SELECT sẽ không được dùng trong GROUP BY. Và trên thực tế thì Oracle, DB2 và SQLServer thì cách viết này sẽ trả lại lỗi.
Tuy nhiên, vẫn có những DB cho qua câu SQL này. PostgreSQL hay MySQL thì query này vẫn được thực hiện mà không có vấn đề gì. Đó là tại vì list có trong SELECT sẽ được chạy qua trước rồi sẽ xảy ra tính toán cho dãy. Vì đây là cách viết vi phạm thông thường nên tôi không khuyến khích một cách tích cực nhưng nó có sức cuốn hút ở chỗ dòng code có thể viết một cách rất ngắn gọn và đọc một cách dễ dàng.
Thực hiện tập hợp những điều kiện khác nhau chỉ trong một dòng SQL
Tập hợp những điều kiện khác nhau là một trong những cách sử dụng nổi tiếng của hàm CASE. Ví dụ tại bảng lưu dữ dữ liệu về dân số của từng tỉnh phía trên chúng ta muốn thêm dãy về giới tính, suy nghĩ về trường hợp có yêu cầu số lượng người theo từng giới tính của những tỉnh khác. Ví dụ trong trường hợp tập hợp nội dung có trong bản dưới đây và yêu cầu kết quả như bảng dưới đó nữa.
Bảng trước khi tập hợp PopTbl2| pref_name (Tên tỉnh) | sex (Giới tính) |population (Dân số) | |--------|--------| | Tokushima|1| 60 | | Tokushima|2| 40 | | Kagawa|1| 100| | Kagawa|2| 100| |Ehime|1|100| |Ehime|2|50| |Kouchi|1|100| |Kouchi|2|100| |Fukuoka|1|100| |Fukuoka|2|200| |Saga|1|20| |Saga|2|80| |Nagasaki|1|125| |Nagasaki|2|125| |Toukyou|1|250| |Toukyou|2|150|
Kết quả tập hợp|Tên tỉnh|Nam|Nữ| |--------|--------| |Tokushima|60|40| |Kagawa|100|100| |Ehime|100|50| |Kouchi|100|100| |Fukuoka|100|200| |Saga|20|80| |Nagasaki|125|125| |Toukyou|250|150|
Cũng như bình thường chúng ta điền điều kiện khác vào câu WHERE và phát hành câu SQL 2 lần.
Ví dụ 3--Dân số nam
SELECT pref_name
SUM(population)
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
--Dân số nữ
SELECT pref_name
SUM(population)
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
Nếu chúng ta sử dụng UNION thì chỉ cần trong một câu SQL nhưng như thế không những không làm giảm tài nguyên mà còn làm SQL dài ra một cách lãng phí. Tuy nhiên nếu dùng CASE thì chúng ta có thể hoàn thành chỉ trong một SQL như dưới đây.
SELECT pref_name,
--Dân số nam
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m
--Dân số nữ
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name
Như vậy thì máy tính sẽ tính toán dãy dân số theo dãy được lưu Nam là '1' và Nữ là '2'. Như vậy chúng ta đã triển khai dữ liệu từ dòng ngang sang cột dọc. Nếu là hàm số thông thường thì không chỉ giới hạn ở SUM mà chúng ta cũng có thể dùng COUNT và AVG.
Điều quan trọng đạt được trong thuật đó chính là chúng ta có thể biểu diễn kết quả của SQL trên bảng 2D. Đơn giản tóm lại tất cả bằng GROUP BY sau đó trên những ứng dụng như Excel thì chúng ta phải chỉnh lại thành dạng biểu cross. Tuy nhiên, theo như nhìn vào kết quả trên thì cột bên chỉ tên tỉnh, dòng trên chỉ giới tính thì chúng ta có thể hiểu là bảng đã hiện lên kết quả là biểu cross. Đây là tính năng rất tiện lợi khi chúng ta tạo bảng tập hợp thông tin. Nếu chúng ta biểu diễn thuật này bằng một slogan thì có thể là,
Dùng WHERE để phân loại điều kiện thì đó là việc làm của Amateur. Người Pro sẽ dùng SELECT để phân kì
Đây là một thuật dùng rất tiện lợi nên mọi người hãy sử dụng nó nhiều vào nhé!
Định nghĩa quan hệ điều kiện của dãy phức số bằng CHECK
Hàm CASE rất tương thích với chế ước CHECK. Có thể có rất nhiều kĩ sư về DB không hay sử dụng chế ước CHECK nhưng nếu biết được sức mạnh khi biễu diễn kết hợp với hàm CASE thì mọi người ai cũng muốn sử dụng nó. Ví dụ, ở đây chúng ta có một công ty có thể chế lương là "Lương của nhân viên nữ sẽ dưới 20 vạn". Đối với bảng nhân sự của một công ty không còn lời nào để nói này thì SQL tiếp theo đây chính là biểu hiện của chế ước CHECK.
CONSTRAINT check_salary CHECK
(CHECK WHEN sex = '2'
THEN CASE WHEN salary <= 20000
THEN 1 ELSE 0 END
ELSE 1 END = 1)
Hàm CASE được đưa vào như một hàm con, để đưa ra mệnh đề "Nếu giới tính của nhân viên công ty là nữ thì lương sẽ dưới 20 vạn". Cái này trong mệnh đề logic thì sẽ được gọi là điều kiện, còn nếu viết thành kí hiệu thì nó sẽ là P->Q. Tại đây có 1 điểm các bạn cần phải hiểu. Đó chính là sự khác nhau giữa câu điều kiện và mệnh đề logic. Mệnh đề logic là một công thức logic biểu thị ý nghĩa "P và Q" là "P^Q". Và nếu muốn hiển thị điều này trong chế ước CHECK thì chúng ta viết như sau.
CONSTRAINT check_salary CHECK
( sex = '2' AND salary <= 20000)
Hai chế ước này tất nhiên sẽ thực hiện những động tác khác nhau. Nhưng nó khác nhau ở chỗ nào. Dưới đây là viết giải thích, để tiến lên những bước tiếp theo thì các bạn nên dừng lại đọc một chút.
| Câu trả lời |
|---|
| Nếu chúng ta thêm chế ước CHECK vào mệnh đề logic thì công ty này không thể tuyển được đàn ông. Nếu thêm vào câu điều kiện thì đàn ông mới có thể làm việc được. |
| Giải thích |
| Để thỏa mãn mệnh đề logic "P^Q" thì cả hai mệnh đề P và Q đều phải đúng hoặc một trong 2 mệnh đề đúng, mệnh đề kia bất định. Có nghĩa là người có thể làm việc ở công ty này là người "Là nhân viên nữ hoặc có lương dưới 20 vạn", hoặc trường hợp hoặc giới tính hoặc số lương là không xác định. (Nếu một trong hai vế mệnh đề là sai thì mệnh đề còn lại mà bất định thì người đó không thể làm việc). Mặt khác, để câu điều kiện "nếu P thì Q" đúng là trường hợp cả 2 đều đúng và trường hợp P sai hoặc bất định. Có nghĩa là điều kiện phía trước, nhân viên là giới tính nữ mà không thỏa mãn thì chế ước về lương đằng sau sẽ không bao giờ được xét đến. |
Mọi người hãy tham khảo bảng mệnh đề đúng sai như dưới đây. U là giá trị thứ 3 đặc biệt chỉ có SQL sở hữu mang ý nghĩa là "unknown".
Bảng phép hội và phép kéo theo| P | Q | P^Q | P | Q | P => Q |
|---|---|---|---|---|---|
| T | T | T | T | T | T |
| T | F | F | T | F | F |
| T | U | T | T | U | F |
| F | T | F | F | T | T |
| F | F | F | F | F | T |
| F | U | F | F | U | T |
| U | T | U | U | T | T |
| U | F | U | U | F | T |
| U | U | U | U | U | T |
Giống như những gì chúng ta có thể thấy ở bảng, trong phép kéo theo (câu điều kiện), nếu trong trường hợp giới tính không phải là nữ (hoặc trong trường hợp không biết có phải nam hay không) thì mệnh đề mới vẫn đúng.
Sau khi phân kì xong điều kiện UPDATE
Đối với dãy giá trị số, chúng ta suy nghĩ đến trường hợp muốn chuyển giá trị hiện tại thành một giá trị khác như một đối tượng. Vấn đề là trường hợp điều kiện của UPDATE lúc đó sẽ được phân chia khá phức tạp. Ví dụ chúng ta thử xử dụng bàn của khi vực nhân sự khi nhập lương cho nhân viên xem sao.
Salaries| name | salary |
|---|---|
| Aida | 300,000 |
| Kamazaki | 270,000 |
| Kimura | 220,000 |
| Saito | 290,000 |
Bây giờ đối với bảng này, chúng ta sẽ cập nhật lại lương theo điều kiện dưới đây.
- Đối với những nhân viên có số lương hiện tại trên 30 vạn thì sẽ giảm 10% lương.
- Đối với những nhân viên có mức lương hiện tại từ 25 đến 28 vạn thì tăng 20% lương.
Theo như kế hoạch trên thì bảng kết quả sẽ nhất định như dưới đây.
| name | salary |
|---|---|
| Aida | 270,000 |
| Kamazaki | 324,000 |
| Kimura | 220,000 |
| Saito | 290,000 |
Nếu suy nghĩ đơn giản thì chúng ta chỉ cần thực hiện lệnh UPDATE 2 lần nhưng thực tế là không đúng như vậy.
--Điều kiện 1 UPDATE Personnel SET salary = salary * 0.9 WHERE salary >= 300000; UPDATE Personnel SET salary = salary * 1.2 WHERE salary >= 250000 AND salary < 280000;
Nếu như vậy thì ví dụ trong trường hợp lương của nhân viên hiện tại và trên 30 vạn, tất nhiên như vậy theo điều kiện 1 của UPDATE thì lương sẽ tụt xuống còn 27 vạn. Tuy nhiên tại đó sẽ không kết thúc mà theo như điều kiện 2 của UPDATE thì nó sẽ lại tăng lên là 32 vạn 4000 yên. Và đúng theo thực tế thì chương trình chạy sẽ ra kết quả cuối cùng là 32 vạn 4000 yên.
| name | salary |
|---|---|
| Aida | 324,000 |
| Kamazaki | 324,000 |
| Kimura | 220,000 |
| Saito | 290,000 |
Tất nhiên kết quả này không phải là ý đồ của bộ phận nhân sự. Chúng ta nhất định phải giảm lương của anh Aida xuống 27 vạn. Vấn đề là khi lần đầu tiên chạy UPDATE thì sẽ chạy được về mức lương như hiện tại nhưng khi chạy UPDATE lần thứ 2 thì mức lương hiện tại sẽ phù hợp với điều kiện của lần 2 nên kết quả lại được chạy lại. Thứ tự SQL có được đổi lại đi chăng nữa thì trường hợp bọ tính sai này vẫn sẽ phát sinh đối với người có lương 27 vạn. SQL phản ánh được ý đồ của trưởng bộ phận nhân sự nhất thiết cần viết hàm CASE như dưới đây.
--Cập nhật đúng được viết bằng hàm CASE
UPDATE Personel
SET salary = CASE WHEN salary >= 300000
THEN salary*0.9
WHEN salary >= 250000 AND salary < 280000
THEN salary*1.2
ELSE salary END;
SQL này không những sẽ chạy ra kết quả đúng mà vì chỉ cần chạy 1 lần là xong nên tốc độ thực hiện chương trình cũng nhanh hơn. Như thế này thì bộ phận nhân sự chắc sẽ đồng ý.
Tại đây thì dòng cuối cùng ELSE salary rất quan trọng, nhất định các bạn cần phải viết. Nếu không viết dòng này thì nhân viên nào có lương không giống với điều kiện 1 và 2 thì kết quả sẽ trả về giá trị NULL. Như vậy cũng giống như vấn đề đã được nêu ra ở phần nhập môn là "Trong trường hợp hàm CASE không có dòng ELSE một cách minh bạch thì kết quả sẽ được trả lại giá trị mặc định là ELSE NULL". Chúng ta cứ chỉ cần hiểu trong trường hợp dùng hàm CASE thì thói quen viết ELSE là một thói quen nhất định phải có là được.
Ứng dụng của chiêu này được sử dụng trong phạm vi rộng, nếu sử dụng phương tiện này thì công việc kinh điển là thay đổi giá trị của những phím chính chúng ta cũng có thể dễ dàng làm được. Bình thường để thay đổi giá trị của phím nhập vào thì theo phương pháp bình thường chúng ta sẽ phải chạy UPDATE 3 lần nhưng nếu dùng hàm CASE thì chỉ cần 1 lần chạy.
SomeTable| p_key | col_1 |col_2| |--------|--------| |a|1|あ| |b|2|い| |c|3|う|
Ví dụ như bảng trên thì chúng ta thay đổi giá trị a và b mà không dùng CASE thì sẽ viết 3 SQL như sau.
---1 Chuyển giá trị d vào a UPDATE SomeTable SET p_key = 'd' WHERE p_key = 'a'; --2 Thay b bằng a UPDATE SomeTable SET p_key = 'a' WHERE p_key = 'b'; --3 Thay d bằng b UPDATE SomeTable SET p_key = 'b' WHERE p_key = 'd';
Cái này thì đúng là chương trình sẽ chạy như mong muốn nhưng việc phải sử dụng UPDATE 3 lần là một sự lãng phí quá nhiều, rồi thêm cả nỗi lo lắng giá trị thay thế d không biết còn có thể sử dụng không. Nếu dùng hàm CASE thì nỗi lo này sẽ đồng thời mất đi.
---Thay đổi giá trị phím dùng hàm CASE
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
Chỉ đọc qua một lần chúng ta cũng có thể hiểu UPDATE sẽ thực hiện chương trình nếu a thì thay bằng b, nếu b thì thay bằng a, tất nhiên không chỉ những key chính mà chúng ta cũng có thể đồng thời thay vào những key có tính sáng tạo khác. Điểm mấu chốt sẽ xảy ra giống như trường hợp tăng lương, giảm lương như ví dụ phía trên. Có nghĩa là sự phân kì của CASE được thực hiện đồng thời nên chúng ta có thể tránh được việc lặp lại lỗi của key chính.
Tuy nhiên query này thì trong PostgreSQL hay MySQL thì sẽ trở thành lỗi tùy theo sự trùng nhau của key. Tuy nhiên sau này sau khi được cải tiến thì như trong Oracle, DB2 hay SQLServer thì chúng sẽ được chạy mà không bị vấn đề gì cả.
Tuy nhiên, trong trường hợp phát sinh cần thiết phải thay đổi giá trị như thế này thì rất có khả năng cao là sẽ gây nhầm lẫn trong những thiết kế bàng nên đầu tiên chúng ta nhìn qua thiết kế bảng, nếu không quá cần thiết thì hãy gỡ chế ước này ra.
Matching bảng với nhau
Điểm lợi thế lớn hơn của hàm CASE khi so sánh với hàm DECODE là có thể đánh giá được hàm. Có nghĩa là chúng ta có thể sử dụng những nhóm từ ngữ tiện lợi như BETWEEN, LIKE, >, < trong hàm CASE. Rồi trong đó có thể lấy được argument của subquery là IN và EXISTS, nên nó mang một năng lực biểu hiện rất mạnh cái này.
Dưới đây là bảng những ca học của một trường dự bị đào tạo chứng chỉ và một bảng quản lý những khóa học được mở ra tổ chức trong nhóm.
Khóa học Maaster. CourseMaster| course_id | course_name |
|---|---|
| 1 | Nhập môn kế toán |
| 2 | Kiến thức tài vụ |
| 3 | Kiểm tra viết |
| 4 | Thanh tra thuế |
Buổi giảng khai giảng bằng tháng OpenCoures
| month | course_id |
|---|---|
| 200706 | 1 |
| 200706 | 3 |
| 200706 | 4 |
| 200707 | 4 |
| 200708 | 2 |
| 200708 | 4 |
Từ những bảng trên chúng ta cùng làm thành một bảng nhìn được hết trạng thái khai giảng khóa học được khai giảng mỗi tháng.
course_name Tháng 6 Tháng 7 Tháng 8 Nhập môn kế toán ○ × × Kiến thức tài vụ × × ○ Kiểm tra viết ○ × × Thanh tra thuế ○ ○ ○
Có nghĩa chúng ta đi tiến hành chương trình check xem trogn bảng OpenCourse trong mỗi tháng có tồn tại tiết học của bảng CourseMaster không. Điều kiện cảu sự matching chính là viết được bằng hàm CASE.
--Matching bảng: sử dụng thuật ngữ IN
SELECT course_name,
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourse
WHERE month = 200706) THEN '○'
ELSE '×' END AS 'Tháng 6',
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourse
WHERE month = 200707) THEN '○'
ELSE '×' END AS 'Tháng 7',
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourse
WHERE month = 200708) THEN '○'
ELSE '×' END AS 'Tháng 8',
FROM CourseMasster;
--Matching bảng: sử dụng EXISTS
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourse OC
WHERE month = 200706
AND OC.coure_id = CM.course_id) THEN '○'
ELSE '×' END AS 'Tháng 6',
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourse OC
WHERE month = 200707
AND OC.coure_id = CM.course_id) THEN '○'
ELSE '×' END AS 'Tháng 7',
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourse OC
WHERE month = 200708
AND OC.coure_id = CM.course_id) THEN '○'
ELSE '×' END AS 'Tháng 8',
FROM CourseMasster CM;
Tại đây không phát sinh sort nên khi số tháng tăng lên thì chúng ta chỉ cần chỉnh sửa câu lệnh SELECT nên tính mở dộng của nó rất lớn.
Sử dụng IN hay EXISTS thì kêt quả đều giống nhau nhưng để biểu diễn thì sử dụng EXISTS sẽ tốt hơn. Vì chúng ta xử dụng chỉ số của những key (month, course_id)trong subquery, đặc biệt trong trường hợp kích thước bảng OpenCourses lớn thì chúng đặc biệt giúp đỡ mang tính hiệu quả hơn.
Sử dụng hàm số tính toán trong hàm CASE
Đây là cách dùng được sử dụng ở mật độ cao bình thường. Về ví dụ thì chúng ta sẽ lấy bảng thông tin sinh viên và câu lạc bộ sinh viên đó thuộc vào. Những key chính là "Số hiệu học sinh", "ID CLB". Để tiếp nhận những mối quan hệ liên quan phức tạp này chúng ta có cấu tạo NTT.
StudentClub|std_id|club_id|club_name|main_club_flg| |--------|--------| |100 | 1 | CLBBóng chày|Y| |100|2|CLB Nhạc cụ|N| |200|2|CLB Nhạc cụ|N| |200|3|CLB Cầu lông|Y| |200|4|CLB Bóng đá|N| |300|4|CLB Bóng đá|N| |400|5|CLB Bơi lội|N| |500|6|CLB Cờ|N|
Cũng có một học sinh thuộc ở nhiều CLB (100,200) rồi cũng có học sinh chỉ thuộc 1 CLB (300,400,500). Đối với những học sinh thuộc nhiều CLB thì để chỉ đâu là CLB chính thì trong dãy sẽ có thêm giá trị Y hay N. Trong trường hợp học sinh chỉ thuộc trong 1 CLB thì giá trị trả lại sẽ là N.
Như vậy, từ bảng thì chúng ta phải phát hành những query với điều kiện như dưới đây.
- Đối với những học sinh chỉ thuộc một CLB thì lấy ID của CLB họ tham gia
- Đối với học sinh tham gia nhiều CLB thì lấy ID của CLB chúng tham gia chính.
Nếu suy nghĩ một cách đơn giản thì chúng ta sẽ nghĩ chỉ cần phát hành ra query đối ứng với 2 điều kiện tiếp theo đây là được. Chúng ta sẽ lấy điều kiện là "Có tham gia nhiều CLB hay không" nên sẽ sử dụng câu lệnh HAVING.
SQL của điều kiện 1--Điều kiện 1: Lựa chọn những học sinh chỉ tham gia 1 CLB SELECT std_id, MAX(club_id) AS main_club FROM StudentClub GROUP BY std_id HAVING COUNT(*) = 1;Kết quả thực hiện 1
std_id main_club ------ --------- 300 4 400 5 500 6SQL của điều kiện 2
--Chọn những học sinh thuộc nhiều CLB SELECT std_id, club_id AS main_club FROM StudentClub WHERE main_club_flg = 'Y';Kết quả thực hiện 2
std_id main_club ------ --------- 100 1 200 3
Đúng là cho đến đây thì chúng ta có thể nhận được kết quả như mong muốn nhưng theo như ví dụ đã cho thì chúng ta cần nhiều những SQL khác nhau. Nếu sử dụng hàm CASE thì chúng ta có thể hoàn thành trong một SQL.
SELECT std_id
CASE WHEN COUNT(*) = 1 --Trường hợp học sinh chỉ tham gia 1 CLB
THEN MAX(club_id)
ELSE MAX(CASE WHEN main_club_flg = 'Y'
THEN club_id
ELSE NULL END)
END AS main_club
FROM StudentClub
GROUP BY std_id;
std_id main_club ------ --------- 100 1 200 3 300 4 400 5 500 6
Viết hàm số tính toán trong hàm CASE rồi trong đó lại sử dụng hàm CASE là một cấu tạo có thể làm người ta rơi vào tình trạng hoa mắt nhưng điều chúng ta muốn thực hiện đó chính là phân loại điều kiện "Chỉ chuyên tham gia một CLB hay tham gia nhiều CLB" mà biểu hiện sử dụng hàm CASE theo dạng CASE WHEN COUNT(*) = 1 ... ELSE ... Cách viết này mang một chút tính chất cách mạng. Đó là vì khi chúng ta học nhập môn về SQL thì chúng ta được học sử dụng HAVING là điều kiện đối với kết quả sau khi tập hợp nhưng nếu sử dụng hàm CASE thì chúng ta cũng có thể phân loại điều kiện bằng cách sử dụng SELECT để được kết quả tương tự. Việc sử dụng này nếu đưa thành một slogan thì chúng ta có thể nói,
Dùng HAVING để phân điều kiện thì đó là việc làm của một amateur. Việc làm của người pro là sử dụng SELECT để làm việc đó
Trong hàm CASE thì ngoài việc sử dụng SELECT thì chúng ta cũng có thể viết theo cách khác như ví dụ đã nêu ra. Độ tự do sử dụng cao thế này chính là điểm thu hút của hàm CASE.
Tóm tắt
Chương này chúng ta đã nhìn thấy một phần khả năng biểu hiện cao cùng với tính mềm dẻo của hàm CASE. Hàm CASE chính là một trong những đường sinh mệnh hỗ trợ cho lập trình SQL, là kĩ thuật cơ bản để sử dụng thành thạo SQL nên nhất định các bạn hãy chuyên nghiệp trong cách sử dụng này. Nhiều đến mức trong những chương sau của cuốn sách này gần như không có nơi nào là không sử dụng hàm CASE.
Và cuối cùng của chương này hãy để tôi nói chuyện chi tiết hơn một chút. Hàm CASE thường được gọi như câu lệnh CASE của ngôn ngữ C hay VB. Tuy nhiên, một cách chính xác thì đây không phải là câu mà là hàm cũng giống như 1+1 hay a/b. Chúng ta nhìn thấy END ở cuối cùng cũng như là chỉ block của thủ tục đã dừng tại đây , và đây cũng chình là điểm gây rối đối với những người lần đầu tiên tiếp xúc với hàm CASE. Sự khác nhau giữa cách goi "câu" hay "hàm" thì điểm mấu chốt quan trọng chính là phản ánh điểm khác nhau giữa sự sắp xếp tính năng. Mặc dù là hàm nhưng khi chạy hàm CASE thì giá trị được chạy chỉ có một (nên nó có thể được viết ngay trong một hàm khác), vì là hàm nên chúng ta cũng có thể viết vào trong đó câu SELECT rồi GROUP BY, WHERE hay ORDER BY. Nói một cách rộng ra thì hàm CASE thì ở chỗ nào viết được dãy số hay chữ thì đều có thể viết được. Đối với ý nghĩa đó thì thứ gần giống nhất với hàm CASE thì không phải câu lệnh CASE của các ngôn ngữ mà là hàm điều kiện case hay cond của ngôn ngữ dạng hàm như Lisp hay Scheme. Về SQL và ngôn ngữ dạng hàm số thì sẽ được nêu như chủ đề chính ở chương 2.
Tiếp dưới đây là những điểm chính của chương này.
- Sử dụng hàm CASE bằng mệnh đề GROUP BY để thiết kế một cách mềm dẻo code hay dữ liệu theo đơn vị của cả nhóm. Các hàm tập hợp (như SUM) trả về một tập hợp của các giá trị trong cột mỗi khi chúng được gọi, và nếu không có GROUP BY ta không thể nào tính được tổng của các giá trị theo từng nhóm riêng lẻ trong cột.
- Sử dụng hàm tập hợp có thể tự do triển khai từ sắp xếp theo dòng thành theo cột.
- Ngược lại thì hàm tập hợp cũng có thể kết hợp với những hàm điều kiện khác
- Khả năng biểu hiện mạnh mà tính thực dụng cũng cao.
- Hàm CASE không phải là một mệnh đề mà là một hàm.
Đối với những người muốn nghiên cứu thêm về hàm CASE thì có thể tham khảo ở sách tham khảo dưới đây.
-
Joe CELKO "SQL cho lập trình Bản 2" (2001) Chương 7.1 Hàm CASE Chương 9.3.5 Cách sử dụng hàm CASE trong UPDATE Quyển sách này có viết hàm CASE trong phạm vi rất rộng từ ngữ pháp hàm CASE chi tiết đến những ví dụ cụ thể.
-
Joe CELKO "SQL Puzzle" Về kĩ thuật kết hợp với những hàm tập hợp của hàm CASE "Puzzle 13 Hai người à, hay trên thế nữa, đó chính là vấn đề" "Puzzle 36 Một người hai việc" "Puzzle 43 Tốt nghiệp"
Về UPDATE trong hàm CASE ta có "Puzzle 44"
