Sybase, several SQL uses small features

Source: Internet
Author: User
Tags sybase

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.