I just remembered that I had registered such a blog for a long time. Now I want to write something useful in the project and share the database.

In fact, many things can be found on the Internet or on msdn, but sometimes it takes a certain amount of time to accumulate and exercise more is a good way to solve similar problems.

The following are only some of the problems I encountered during project development. As for the simple problems that I want everyone to solve, I only have some experience and hope to help you.

 

1. Keep in mind: the principle of group by is that columns that do not use aggregate functions in all columns after select must appear after group.
Therefore, you should write all the column names you want to display to the group by Column

2.

For example, how do I retrieve the contents of rows 5th to rows 10th from a table?

Sele select top 6 * From table_name where primary_id not in (select Top 4 primary_id from table_name order by primary_Id ASC) order by primary_id ASC

Tabl table_name, which must have a primary key. primary_id is the primary key.

 

Take m to N rows of data

Select top (n-m + 1) * From table_name where primary_id not in (select top (m-1) primary_id from table_name order by primary_Id ASC) order by primary_id ASC

 

This can be used to display data by page.

For example, each time you extract 10 rows of data, intrecordofpage = 10

The current page number is 2 intcurrtpage

 

Set start value

Delcare @ inttop int

Set @ inttop = intrecordofpage * (intCurrtPage-1)

 

Select top (10) * From table_name where primary_id not in (select top (@ inttop) primary_id from table_name order by primary_Id ASC) order by primary_id ASC

Query efficiency is more than doubled. If you only query the fields that need to be used instead of *, the efficiency may be higher.

 

 

 

ZH

Obtain the number of days in a month

Select datediff (DD, getdate (), dateadd (mm, 1, getdate ()))

The previous value is the beginning of this month.

The next value is the beginning of next month.

If the day of the month and the end of the month, add 1

 

 

Obtain the first and last days of a month.

Set @ starttime = substring (convert (varchar (10), @ datestamp, 120),) + '-1'

Set @ endtime = dateadd (D,-1, dateadd (mm, 1, @ starttime ))

Obtain the number of weekend days in a period of time

Set @ daysofweek = datediff (WW, @ starttime, @ endtime) * 2 + (case when datepart (DW, @ starttime) = 1 then 1 else 0 end) + (case when datepart (DW, @ endtime) = 7 then 1 else 0 end)

 

In SQL, how do I query tables where the same field is located?

Select a. Name 'table name', B. name' field name' from sysobjects A, syscolumns B

Where a. ID = B. ID and B. Name = 'emp'

Merge values of two columns into one column using Union

 

Select min (time1) from table
Union all
Select min (time2) from table

 

C