SQL knowledge Accumulation

Detailed articles on select, showing raw data, SQL, query results, and how SQL should be written under different databases.

Https://en.wikipedia.org/wiki/Select_ (SQL)

The directory is as follows:

Contents [Hide]1Examples2limiting result rows2.1row_number () window function2.2RANK () window function2.3FETCH FIRST clause2.4non-Standard Syntax2.5Result Limits2.6Hierarchical Query3Query Evaluation ANSI4Generating DatainchT-SQL5References6Sources7External Links

Data for 第20-30 rows in Informix: SELECT SKIP First ten * from T ORDER by C, D

Research on how to implement Oracle rownum function in Informix

Statistical Functions

Number of Oracle Tab Records (COUNT (*) implementation)

Statistics of Informix database table records

MySQL by year, Quarter, month, week, day SQL statistical query (

the following Informix SQL statements have been tested, and, if not specifically noted, the beta version is IDS11.5 development. Each phase combines a specific application with a detailed description.

First, select records
1Choose Top 10 Records Select firstTen* fromHcrk IDS7. -, IDS11 support2. Select 11-15 Records SelectSkipTenLimit5* fromHcrk IDS7.30 not supported, IDS11 supportSelectFirst5Hcrkzlbh from(SelectFirst theHcrkzlbh fromhcrk) Order BY hcrkzlbh Desc IDS7.30 Use this method to implement two, export the database structure Dbschema-D db_name-SS III, synthetic table nameSelect* fromHxqSelectId fromTAB_A) Informix 11 support, informix10,9and 7 are not supported. Iv. Date Fields --Ten- - 1. Intercept one section directly from the Datatime field ()Select Extend(hcrkczsj,year to month) fromHcrk take the monthextend function, you can count the month, day, hour, such as statistics by the hour 365 days a table of the number of log records per year

v. Some methods used by the group by and order by in the SELECT statement
Data table Hcrk, field hcrkje (numeric type*.**), HCRKRQ (character type yyyymm)
1The sequence number of the Select field in group by, order by, 1,2、3"Specify Fields,SelectA field can be an expression, a field name, an alias
Example:SelectHcrkje,z_quarter (HCRKRQ) asJd fromHCRK GROUP BY2ORDER BY2
When the Select field is Z_quarter (HCRKRQ) asJD when group by does not allow the expression "Z_quarter (HCRKRQ)", nor does it allow the alias "JD", but allows the field name "Hcrkrq" and the sequence number.
The order by does not allow the field name "Hcrkrq", but allows the expression "Z_quarter (HCRKRQ)", the sequence number, and the alias "JD".

Vi. use of case and when in SELECT statements
Count Odd, even month
1.Select CaseMoD (hcrkrq[5,6] /2) when1Then "1”Else“2” fromHcrk
2.Select CaseWhen mod (hcrkrq[5,6] /2)=1Then "1”Else“2” fromHcrk
The expressions are written in the case, and when you do, both of these are available. Case, when can also be used in the self-built function.

Seven, self-built function creation method

1. Create a function (SPL? ）
You can call back quarters like the built-in functions of informix (such as SUM, Max), pass in the parameter RQ, format "Yyyymm", character type. The function must specify the return type.
CREATE FUNCTION z_quarter (RQ VARCHAR ( -) ) RETURNS integer DEFINE quarter integer;
Let quarter= Trunc ((rq[5,6]-1)/3)+1; (or let quarter= Trunc (mod (rq-1, -)/3)+1;
RETURN quarter;
END FUNCTION
If the return type is a character type, you must specify a length. Otherwise, a single character is returned.
CREATE FUNCTION z_quarter (RQ VARCHAR ( -)) RETURNS CHAR ( -) DEFINE Quarter CHAR ( -); Let quarter= CaseTrunc ((rq-1)/3)+1When1Then"1 Quarter"When2Then"2 Quarter"When3Then"3 Quarter" when4Then"4 Quarter"Else"N"End  RETURN quarter; END FUNCTION
can use the "||"Connection string (like connection string in Select)
Example: Let quarter= CaseTrunc ((rq-1)/3)+1When1Then"1"When2Then"2"When3Then"3"When4Then"4"Else"N"end| |"Quarterly"; But let quarter= quarter| |"Quarterly"; It's not working, error.

2. Delete a function

Drop function Z_quarter 3. Calling functions
Select Trim (Z_quarter (hcrkrq[5,6)) from the Hcrk group by 1 The order by 1 trim () function truncates the front and back spaces.

Viii. Quarterly statistics on the amount of the library

1.group by and order by method with no field order number two times Select Select Jd,sum (Zje) from (select SUM (hcrkje) as Zje, Trunc ((hcrkrq[5,6]-1)/3) +1 as Jd
From HCRK GROUP by HCRKRQ ORDER by JD) GROUP by JD ORDER by JD Quarterly Amount 1 558 2 320 3 2277 4 399
2. Group BY, order by with Field order number
SELECT trunc ((hcrkrq[5,6]-1)/3) +1 as Jd,sum (Hcrkje)
From HCRK GROUP by 2 ORDER by 2

3. Replace the quarter with the Chinese name Select
Case Trunc ((hcrkrq[5,6]-1)/3) +1 if 1 then "1 quarter" when 2 then "2 Quarter" when 3 then "3 Quarter" when 4 then "4 Quarter" else HCRKRQ
End as JD, SUM (Hcrkje)
From HCRK GROUP by 2 order by 2 4. Using a self-built function
Using the function, when the rule changes, only need to modify one place, flexible, easy to use. Select Z_quarter (HCRKRQ) as JD, SUM (Hcrkje) from HCRK GROUP by 2 ORDER by 2

Statistics amount of seven, year, month, week and day
SELECT year (date) as Year,sum (Hcrkje) from HCRK Group by 1 ORDER by 1
SELECT month (date) as Month,sum (Hcrkje) from HCRK Group by 1 ORDER by 1

SELECT date as Day,sum (Hcrkje) from HCRK Group by 1 ORDER by 1

SQL knowledge Accumulation