The Mysql count+if function is used in conjunction with

The Mysql count+if function is used in conjunction with Fruit Grove Coconut Care 2017.05.18 13:48* Words 508 Read 148 Comments 0 likes 1

involved functions

Count function

In MySQL, the Count function is used to count the total number of rows in a data table, or to count the number of rows that a column contains based on the results of the query, the common usage is that count (*) calculates the row count of the table, including the empty value count (field name) to calculate the row count under the specified column, ignoring the null value We will use this feature later)

if (expr, V1, v2) functions

The IF (expr, V1, v2) function means that if the expression expr is true (expr<>0 and expr <> NULL), then if () returns V1, otherwise returns v2

Combining the two functions above, you can count the number of rows in a single statement that satisfy different conditions

Business Scenario: A table that records all the books owned by different publishers and the corresponding types of each book.

The table structure is as follows:

ID Press BookName Booktyoe

1 Xinhua Press "Thinking in Java" Computer class

We need to count the total number of different types of books in different publishing houses.

Example: Statistics on the number of literature books in different publishing houses

Select count (if (Booktyoe = ' Literature class ', ID, NULL)) from the table group by press

Parse: When type=1, return the value of the corresponding ID, otherwise put back null, for the specified column of the Count function, NULL is ignored, so that we want to get the number of statistics.

Similarly, the number of sci-fi + computer books in different publishing houses:

Select count (if (Booktyoein (' sci-fi class ', ' Computer book '), ID, NULL)) from the table group by press

In the case of a table query, the ID in the returned result is not unique, and if you want to remove the duplicate ID, you can also precede the if with DISTINCT, which is count (DISTINCT if (type in (2, 3), ID, NULL))

Equivalent notation count (DISTINCT (type = 2 and type = 3) or null)

You can also use Sum+if to replace the above notation

Select SUM (if (Booktyoeinin (' sci-fi class ', ' Computer Book '), 1, 0)) from the table group by press

Mysql© Copyright by author All report articlesconcernFruit Forest Coconut

Wrote 4561 words, was paid attention by 3 people, gained 15 likes

A little gift to walk around, to Jane book Follow me

The Mysql count+if function is used in conjunction with