Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use the if function in mysql, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The specific scenarios are as follows

Take a look at the table structure first:

CREATE TABLE `message` (`id` varchar (30) NOT NULL, `title` varchar (30) DEFAULT NULL, `content` text, `send_ time` datetime DEFAULT NULL, `type` int (1) DEFAULT NULL COMMENT'1: system Notification, 2: complaint suggestion', `status` int (1) DEFAULT NULL COMMENT'0: to be sent, 1: success, 2: failure, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

The specific requirements are: count the total number of messages, the number of successful messages and the number of failed messages of system notification and complaint suggestion respectively.

When we encounter such a problem, our general idea is to use type grouping to query the total number of system notifications and complaint suggestions respectively, and then use two sub-queries to count the number of success and failure. Sql is as follows:

SELECT COUNT (1) total, m.type, (SELECT COUNT (1) FROM message ms WHERE ms.status = 1 AND m.type = ms.type) successtotal, (SELECT COUNT (1) FROM message mf WHERE mf.status = 1 AND m.type = mf.type) failtotalFROM message mGROUP BY m.type

Let's take a look at the running time, and it takes about 6 minutes and 18 seconds to count 10 million pieces of data.

Well, is there any simpler and faster statistical method, of course, that is, the if () function that we are mainly talking about today.

Basic grammar

IF (expr1,expr2,expr3) returns the value of expr2 if the value of expr1 is true, and the value of expr3 if the value of expr1 is false. Is a simple trinomial expression.

How to do it

Speaking of ideas, if we count the number of successful entries, we can write if (status=1,1,0) so that if status==1, it returns 1, otherwise it returns 0. Then we can add the number of successful entries through the SUM () function.

Mode of realization

The sql statement is as follows:

SELECT COUNT (1) total, m.type, SUM (IF (m.status = 1Magne0)) successtotal, SUM (IF (m.status! = 1Magne0)) failtotalFROM message mGROUP BY m.type. After reading the above, do you have any further understanding of how to use the if function in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.