Sybase, several SQL uses small features

Some practical SQL can be written to reduce the complexity of their own programs, and some things can be done by SQL to allow the required data in the RS can be obtained:

1. Combine several fields in the database into a single field output:

--Main use of convert to convert other non-varchar types to a unified type and then add the operation

Select column1+ ', ' +convert (varchar (1), Column2) as column from MyTable

2. Sort by a field in the table and output the number of data records per class

--by type, the number of records per class of type (Count_num) is output, and the column1 is de-weighed, such as two of the value of "test" in the record, but only as a statistic

Select COUNT (Distinct column1) as Count_num from mytable where type in (all in a) group by type Order by type ASC

3. Multiple fields simultaneously calculate sum and output, according to a certain time condition

--Calculate clm1,,, respectively, sum of

Select SUM (CLM1) as OUT1, sum (clm2) as Out2, sum (CLM3) as out3 from mytable where convert (varchar,sdate, 112) = ' 2015-01-21 12:11:10 "

4. Sybase has a different background page than Oracle:

A. Sybase is divided into two steps:

(1) Copy the table and add the fields of the identity type (similar to the RowNum field in the Oracle table) in the Copy table;

Select *,ids=identity (9) into mytemptable from mydatatable

(2) Use select top to combine the Identify type field to isolate the record:

Select top from mytemptable where IDs > (PageSize * (pageNow-1)) and IDs <= (PageSize * pagenow)

5. In Java program logic, if you need to execute SQL statements in large batches, you can execute them in batches, such as 300 per batch, but are still very inefficient for insert because the database has log operations

public static int Executebatchsql (list<string> batchsql) {
int count = Batchsql.size ();
Connection conn = null;
Statement stmt = null;
Int[] result = new int[batchsql.size ()];
try {
conn = Dbconnection.getconnection ();
stmt = Conn.createstatement ();
for (int i = 0; i < count; i++)
{
Joining SQL to a batch pool
Stmt.addbatch (Batchsql.get (i));
if ((i>0 && i% = = 0) | | i = = batchsql.size ()-1) {
result = Stmt.executebatch ();
}
if (i!=0 && i% 300 = = 0) {
Re-establish links after each batch
Dbconnection.releaseresources ();
conn = Dbconnection.getconnection ();
stmt = Conn.createstatement ();
}
}
return result.length;
} catch (Exception ex)
{
Ex.printstacktrace ();
return-1;
} finally {
Releasing database resources
Dbconnection.releaseresources ();
}
}

6. Others, follow-up and re-edit the posts;

Sybase, several SQL uses small features