Shulou(Shulou.com)06/01 Report--

Such as the title: there is a table EMP, which has two fields: name,chengji has three records, representing 70 points in Chinese (name), 80 points in mathematics (name), and 58 points in English (name). Please use a sql to query out these three records and show them under conditions. 80 or more means excellent, 60 or more means passing, and less than 60 points indicates failing. The display format is required as above!

First of all, let's create a table and add data such as the title!

CREATE TABLE emp (NAME VARCHAR (20), chengji INT)

INSERT INTO emp VALUES (Chinese, 70), (Mathematics, 80), (English, 58)

According to the requirements of the title, we need to judge the results of these three rows and then display them in the form of columns, in which there is a row-to-column operation.

The first way to write sql is:

SELECT MAX (CASE WHEN NAME=' language 'THEN (CASE WHEN chengji > = 80 THEN' excellent 'WHEN chengji=60 THEN' pass' ELSE 'fail' END) ELSE''END)' Chinese'

MAX (CASE WHEN NAME=' Mathematics' THEN (CASE WHEN chengji > = 80 THEN 'excellent' WHEN chengji=60 THEN 'pass' ELSE 'fail' END) ELSE''END)' Mathematics'

MAX (CASE WHEN NAME=' English 'THEN (CASE WHEN chengji > = 80 THEN' excellent 'WHEN chengji=60 THEN' pass' ELSE 'fail' END) ELSE''END)' English 'FROM emp

The implementation results are as follows:

Note: the usage of max (case) is used in the above sql. The main function of max here is to get the title that is not an empty string''in three judgments, Chinese, math, English!

The second way of writing is to use the group_concat function to piece together all the important results as shown in the figure as follows:

SELECT GROUP_CONCAT (NAME SEPARATOR'|') FROM emp UNION ALL SELECT

GROUP_CONCAT (CASE WHEN chengji > = 80 THEN 'excellent' WHEN chengji=60 THEN 'pass' ELSE 'fail' END SEPARATOR'|') FROM emp

The implementation results are as follows:

From this point of view, this result is still acceptable, and the group_concat function is used here to connect the class contents of the columns as rows! But the result is a little stiff!

Add a point: we can see the sql writing here if you want to use the first writing method. In front of us, we must know the contents of the column, such as Chinese, math, English, but the second kind we do not need to know! Here we think of a way to spell out the sql of the first method we want through a stored procedure, and then execute it so that if we add more columns or decrease the columns in our table, we won't report an error!

It is written as follows:

DELIMITER $$

USE `yhtest` $$

DROP PROCEDURE IF EXISTS `yhtest` $$

CREATE DEFINER= `root` @ `% `PROCEDURE `yhtest` ()

BEGIN

SET @ sql = NULL

SELECT

GROUP_CONCAT (DISTINCT

CONCAT (

'MAX (CASE WHEN NAME=','\', emp.name,'\', 'THEN (CASE WHEN chengji > = 80 THEN','\', 'excellent','\', 'WHEN

Chengji=60 THEN','\', 'pass','\', 'ELSE','\', 'fail','\', 'END) ELSE','\','\', 'END),'\', emp.name,'\'

)

)

INTO @ sql

FROM emp

SET @ sql = CONCAT ('select', @ sql, 'from emp')

PREPARE stmt1 FROM @ sql

EXECUTE stmt1

DEALLOCATE PREPARE stmt1

END$$

DELIMITER

Call: call yhtest ()

Insert some data! Let's assume that we don't know how many subjects there are in advance!

INSERT INTO emp VALUES (Physics, 72), (Sports, 84)

Here's a problem! Because we use the group_concat function in the stored procedure, the maximum splicing length of this splicing function is 1024 (default) exceeds the fixed length, truncation processing! Controlled by the database parameter group_concat_max_len! We can think of adjustment as needed!

Group_concat adjust stitching symbol: group_concat (name separator'_')

Group_concat sort: group_concat (name order by name separator'_')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.