SQL Server problem summary
1. Merge several tables?
Description: Create a new table whose field structure is the result after several tables join.
Solution: Select * INTO new table name from Old Table 1, old table 2, ......
Example:
- Table NewTableName is added to table titles and titleauthor field tables;
--- Record to record as SELECT * from Titles, Titleauthor record
Select * Into NewTablename from Titles, Titleauthor
- Table NewTablename Field [Title_ID, Title, Au_ID]
- Record as SELECT A.TITLE_ID, A.TITLE, B.AU_ID from Titles A, Titleauthor B Where a.title_id = B.TITLE_ID
SELECT A.TITLE_ID, A.TITLE, B.AU_ID INTO NEWTABLENAME from Titles A, Titleauthor B Where a.title_id = b.title_id
2. Query N-M records?
Description: To remove the middle record from the record set (eg 10 Article 3-8 in 10)
Solutions:
1. Have a keyword or make sure the unique candidate key field:
Method 1: Take M-N 1 data from the Nth record
Method 2: The first M string of the sequencing is most A, and then descend the M-N 1 record as B again.
2. No paragraph:
Add a self-added field to generate a temporary table, take the record from a temporary table [Where self-increasing lite name> N-1]
Example: -
- Take 4 to 7th Record [1 Method 1]
SELECT TOP 4 * from Jobs Where Job_ID Not in (SELECT TOP 3 JOB_ID from Jobs)
--[Method Two]
SELECT TOP 4 * from
(SELECT TOP 4 * FROM
(SELECT TOP 7 * from jobs order by job_id ASC) a
ORDER by Job_ID DESC
) B
Order by job_id ASC
- Take the 2nd to 3rd record [1 method one]
Select Id, 1, 1) AS IID, * INTO #Temptable from Discounts SELECT TOP 2 * from #temptable where iid> = 2
3. Summary in the quarterly quarterly quarterly
Description: Number of employers in each quarter of each quarter of the statistics table EMPLOYEE
Solution: Group By and [Function DatePart ()]]
Example:
// Quarter: quarter
SELECT YEAR (HIRE_DATE) Year, DatePart (q, hire_date) quarter, count (EMP_ID) Employe
Group by year (hire_date), datepart (q, hire_date)
ORDER BY year, quarter
4. Take N records random?
Description: Extract N records from the table
Solution: Follow GUID
Example:
- Take 5 records from the table
Select Top 5 * from titles Order by newid ()
5. Is the number of days in the quarter from any time?
Description: As the title
Solving the idea: Pay attention to the leap year:
Declare @t smalldatetime
SELECT @t = '2001-4-1'
Select Case DatePart (Quarter, @ T)
When 1 THEN
(Case When (Year (@T)% 4 = 0 and year (@T)% 100 <> 0) or year (@T)% 400 = 0 THEN 91 ELSE 90 END)
When 2 Then 91
When 3 Then 92
When 4 Then 92
end
6. What is the first day of the quarter of any time?
Description: As the title
Solve ideas:
Example:
- [Idea: Push the date to the first month of the first month of the quarter
- Note: May 31 will not be promoted to April 31, the system is automatically transferred to No. 30
Declare @d datetime
Set @ d = '2003-5-31'
SELECT @d as specified date,
Dateadd (Month, - (Month (@D) -1)% 3, @ d) -day (dateadd (Month, - (MONTH (@D) -1)% 3, @ D)) 1 AS quarter one day
- [Idea: Since the date of finding the year, it is turned to date]
Declare @t smalldatetime
SELECT @T = '2001-9-30'
SELECT CAST (CAST (Year (@T) as varchar) '-' CAST (3 * DatePart (q, @ T) -2 as varchar) '-1' as datetime)
- Seeking the first day of the current date quarter
SELECT CAST (CAST (Year (a getDate ()) as varchar) '-' Cast (3 * DatePart (q, getdate ()) - 2 as varchar) '-1' as datetime
7. Time overlap problem?
Source: http://expert.9cbs.net/expert/topic/2806/2806966.xml? Temp = .5277979
description:
Create Table #a (ID Int Id Idness (1, 1), Date1 DateTime, Date2 DateTime)
INSERT #A SELECT '2004-02-29 16:45:00', '2004-02-29 20:45:00'
Union all select '2004-02-29 18:45:00', '2004-02-29 22:45:00'
Union all select '2004-03-01 10:45:00', '2004-03-01 13:45:00'
Union all select '2004-03-01 13:45:00', '2004-03-01 16:45:00'
Union all select '2004-03-01 13:47:00', '2004-03-01 14:25:00'
Union all select '2004-03-01 16:45:00', '2004-03-01 19:15:00'
Union all select '2004-03-01 17:45:00', '2004-03-01 18: 55: 00' 2004-03-03 21:45:00 '
Select * from #A
I have to find out all the records over 1 hour!
For example, Article 1, 2: One is from 16:45 - 10:45, and the second is from 18:45 - 25:45 to overlap for 2 hours, so these two must be removed. Others are the same rules!
Date2-Date1 is less than 1 hour without considering, such as Article 5. You can delete the bar first, the record is sorted by Date1!
So the end result is:
ID Date1 Date2
----------- ---------------------------------------- ---------------
1 2004-02-29 16: 45: 00.000 2004-02-29 20: 45: 00.000
2 2004-02-29 18: 45: 00.000 2004-02-29 22: 45: 00.000
6 2004-03-01 16: 45: 00.000 2004-03-01 19: 15: 00.000
7 2004-03-01 17: 45: 00.000 2004-03-01 18: 55: 00.000
Solutions:
Example:
- Option One: [Author: "victorycyz (sea, went to work, not 9CBS play.)"]
Select Distinct a. *
From #a A, #A B
WHERE A.ID <> B.ID and
A.Date1
(Case When A.Date2> B.Date2 Then B.Date2 Else A.Date2 End) -
(Case when a.date1> b.date1 Then A.date1 ELSE B.DATE1 END)> 1 / 24.0
- Option II: [Author: "j9988 (j9988)"]
Select * from #a a where
EXISTS (SELECT 1 from #A
Where id <> a.id
And dateadd (Minute, 60, Date1) AND (Date1 Between A.Date11 and A.date2) And ABS (Datediff (Minute, Date1, A.Date2)> = 60) oral EXISTS (SELECT 1 From #A Where id <> a.id And dateadd (minute, 60, a.date1) And (a.date1 between date1 and date2) And Abs (Datediff (Minute, A.Date1, Date2)> = 60)
