"Daily Summary" database queries encountered problems: Three mesh operation, null value processing of SUM, multi-table joint check

Tag: The Execute div style method represents the difference instance count details

1. There are two methods of ternary operators in MySQL:

case when condition then (executes when condition is true) else (executes when condition is false) end/*end not less */

Select *,if (EXPR1,EXPR2,EXPR3) from table name

Problem Description: The total number of records that need to be queried for response volume >0 when statistics

Solution: Ifnull (SUM (IF (p.reply_count>0, ' 1 ', ' 0 ')), 0) Replycount

2. Problem description: When statistics are performed, the sum () function is used in SQL and a null value is returned in the result set

Solution: Use the Ifnull () function to determine if the result set of the sum () function query is NULL, if NULL, to set the result to a fixed value (for example, 0)

Reference: Problems with NULL when troubleshooting database queries (such as the SUM function)

1 #SUM (expr) function if the expression has no data, the returned result is empty, and here are four ways to solve the null problem:2       3 #第一种: With the ifnull (EXPR1,EXPR2) function, the data returns the default value when EXPR1 is null Expre24   5SELECT ifnull (SUM (expr),0) --indicates if the SUM () function results in a return of NULL ze returns 06   7 #第二种: Using the From coalesce (value,...) function, the COALESCE function returns the first non-null value in the passed-in parameter8   9SELECT COALESCE (SUM (expr),0)   Ten    One#第三种: Using CaseWhen and then . ELSE End Function, note that the case when function ends with end A    -SELECT case when ISNULL (SUM (expr)) then0ELSE SUM (expr) END -    the #第四种: Processing in a result set -        -#比如在Java在进行数据库交互查询之后, processing the result set of SUM (expr), returning null, doing = =judgment can: -     #if(result = = null) {result=0} return result; +#orif(Result instanceof Integer) {returnResultElse{return 0;}

Attached: SUM () differs from count ():

• Sum is the sum of the numeric columns of the records that match the criteria
• Count is the number of results (or records) that match the criteria in the query

The processing of NULL values by aggregate functions such as AVG, COUNT, SUM, Max, and so on in sql:

• AVG ignores null values instead of participating in the calculation as "0"
• COUNT (*) counts the number of rows in a table regardless of whether there is a null,count (field name) count the row with data for a particular column ignores null values
• Max (), Min () max, min values ignore null
• You can sum a single column, or you can sum a number of columns and omit the null value, and when you sum multiple column operations, the record of the row is ignored if the value of any column in the operation is null.

3, problem Description: From the Department table (org) to query departments more than 3 people (employee) of all departments of information

Solution:

SELECT o.* from org o WHERE status=1 and O.id in (

SELECT Temp.oid from (

SELECT COUNT (*) custnum,e.org_id OID from the employee e WHERE e.state=1 GROUP by OID has a custnum>0

) as Temp

)

"Daily Summary" database queries encountered problems: Three mesh operation, null value processing of SUM, multi-table joint check