"Postgresql" Database functions

1.Postgresql SQL statements for several previous records

SELECT * FROM table where ... LIMIT N ;

2.SQL Limit integer Offset integer

: the need to query the middle data in a database is often used.

For example, the following SQL statement:

①select * from testtable limit 2,1;

②select * from testtable limit 2 offset 1;

Both are capable of accomplishing the need, but there is a difference between them:

① is a query starting from the third section of the database, taking a single piece of data, that is, the third piece of data

② is to start querying two data from the second data in the database, that is, the second and third articles.

3.distinct icd01 : The number of ICD01 values in the query table

Select distinct icd01, icd02 from Drug_icd a inner joins RCP_ICD10 D on a.icd=d.icd01 where cag<> ';

4.split_part (Text1,text2,integer) : A string from the Interger (starting at 1) position to the Text2 position (excluding the Text2 character) based on the Text2 split string Text1.

For example: Take the character before the first decimal point in the field icd01 the table ICD10

(Query results)

SQL statements: Select Split_part (icd01, '. ', 1) from ICD10;

5.Coalesce (SUM (value1), 0) : Equivalent to IsNull (num1,0) in SQL Server, non-null judgment

Postgresql Database functions