Use of Oracle nullif Functions

// Question:
ID NAME
------------
1
1 Wang
2 Zhao
// Result:
ID NAME
--------
1
Wang
2 Zhao
// Solution 1:
With t (
Select 1 id, 'zhang 'name from dual union all
Select 1 id, 'King' name from dual union all
Select 1 id, 'lil' name from dual union all
Select 2 id, 'zhao 'name from dual)
SELECT nullif (id, pid) id, NAME
FROM (
SELECT id, NAME,
Lag (id) over (partition by id order by id) pid
FROM t)
ID NAME
--------------
1
Wang
Li
2 Zhao
--
// Analysis:
// The lag () analysis function is cleverly used here,
// This function can return multiple rows of data at a time without the need for table self-join.
// You can return the records of the rows recorded in the first row. If no record exists, null is returned.
// The Query Result of the nested view is as follows:
SELECT id, NAME,
Lag (id) over (partition by id order by id) pid
FROM t
/
ID NAME PID
------------------------
1
1 wang 1
1 Li 1
2 Zhao
// Let's look at the outer query, that is, the primary query.
// The nullif () function is used here. I still see this function for the first time.
// This function has two parameters: nullif (x, y ),
// Compare x and y. If x = y, the return value is null. If not, the return value is x.
For example:
NULLIF (12, 12) wocould return NULL
NULLIF (12, 13) wowould return 12
NULLIF ('append', 'append') wocould return NULL
NULLIF ('append', 'oranges') wocould return 'append'
//
// Solution 2:
With tt (
Select 1 id, 'zhang 'name from dual union all
Select 1 id, 'King' name from dual union all
Select 1 id, 'lil' name from dual union all
Select 2 id, 'zhao 'name from dual)
Select decode (row_number () over (partition by id order by id), 1, id) id,
Name
From tt
ID NAME
--------------
1
Wang
Li
2 Zhao
// Resolution:
// This solution uses the window function row_number () to analyze the function,
// This function returns a sort value for each group and sort. Its usage is a bit like rownum.
// Let's take a look at the result if the decode function is useless:
Select row_number () over (partition by id order by id) id,
Name
From tt
ID NAME
--------------
1
2 Wang
3 Li
1 Zhao
// Use the decode function for comparison,
// If the id value returned by the row_number function is 1, this id value is displayed.
// If the id returned by the row_number function is not 1, null is displayed.
// So we can see the above result: Zhang id is 1, Zhao id is also 1