1 --Scenario 1:2 SelectPT, Greatest (WM), least (WM)3 from(SelectS.producttype PT, Wm_concat (S.productid) WM4 fromsys_product S5 Group byS.producttype)6 wherePt= 1;7 /*8 --Results:9 1 | 1 17,19,16,15,9,4,3,2,1,14,13,18Ten 2 | 2 5,12,11,10,8,7,6 One --maxt=17,19,16,15,9,4,3,2,1,14,13,18 A --mint=17,19,16,15,9,4,3,2,1,14,13,18 - */ - --Scenario 2: the SelectGreatest ( -, +, -, the,9,4,3,2,1, -, -, -,NULL) Maxt, -Least -, +, -, the,9,4,3,2,1, -, -, -) Mint - fromdual; - --results: maxt=19, Mint=1 + - /*1. + COALESCE returns the first non-null value of the expression list. A format: Coalesce (value1, value2, Value3, ...) at meaning: Returns the first non-null value of the list of values. - The value list must be the same type, or it can be the same row of a table, and the values of different columns are compared. - - */ - Select COALESCE(A,B,C) from ( - Select '1' asA'A' asBNULL asC fromDual in Union All - Select '1' asANULL asBNULL asC fromDual to Union All + Select NULL asANULL asB'shit' asC fromDual - ) the * /*greatest The maximum value in the return value list $ format: Greatest (Value1, value2, Value3, ...)Panax Notoginseng meaning: Returns the maximum value for the list of values. - The value list must be the same type, or it can be the same row of a table, and the values of different columns are compared. the a null value is returned when one of the value values in the list is null. + EXAMPLE: A */ the SelectGreatest ( -, +, -, the,9,4,3,2,1, -, -, -) MAXV,-- + +Greatest ( -, +, -, the,9,4,3,2,1, -, -, -,NULL) Maxnull,--NULL -Least -, +, -, the,9,4,3,2,1, -, -, -) Mint--1 $ fromdual; $ - - /* the 3. LEAST the minimum value in the Return value list - format: LEAST (value1, value2, Value3, ...)Wuyi meaning: Returns the smallest value in the value list. the The value list must be the same type, or it can be the same row of a table, and the values of different columns are compared. - a null value is returned when one of the value values in the list is null Wu */ - SelectGreatest ( -, +, -, the,9,4,3,2,1, -, -, -) MAXV,-- + AboutGreatest ( -, +, -, the,9,4,3,2,1, -, -, -,NULL) Maxnull,--NULL $Least -, +, -, the,9,4,3,2,1, -, -, -) Mint--1 - fromDual
Oracle Greatest (), least (), coalesce ()