It is often turned online, often seeing some people in order to seek some SQL statements, now I especially collect some of the more classic SQLs you collect and share it with you.
Row-Range-ordinary
Suppose is a student transcript (CJ) as follows Name Subject Result Zhang San language 80 sheets three mathematics 90 sheets physics 85 Li Si language 85 Li Si Mathematics 92 Li Si Physics 82
Want to become a name language mathematical physics Zhang San 80 90 85 Li Si 85 92 82
Declare @sql varchar (4000) set @SQL = 'select name'select @SQL = @SQL ', SUM (Case Subject when '' SUBJECT '' '' '' '' TENRT End [' Subject '] 'from ( Select Distinct Subject from CJ) as aselect @SQL = @ SQL 'from test group by name'exec (@SQL)
2. Row transition - merge
Table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to make a table B: ID PID 1 1, 2, 3 2 1, 2 3 1
Create a merged function create Ferg (@ID int) returns varchar (8000) asbegindeclare @str varchar (8000) set @str = '' select @ str = @ Str ',' Cast (PID as varchar) from Table A Where id = @ idset @ str = right (@ Str, Len (@STR) -1) Return (@str) endGO
- Call custom functions get the result Select Distinct ID, DBO.FMERG (ID) from FROM Table A
3. How to get all the column names in a data table
The method is as follows: First obtain the systemID of the data table from the SystemObject system table, and then all column names in the data table are obtained in the syscolumn table. SQL statement is as follows: declare @objid int, @ objname char (40) set @objname = 'tablename'select @objid = id from sysobjects where id = object_id (@objname) select' Column_name '= name from syscolumns where id = @objid ORDER by colid
Is it too simple? Oh, but often used.
4. Change the user's password through the SQL statement
Modify others, need sysadmin role exec sp_password null, 'newpassword', 'user'
If the account is SA executes exec sp_password null, 'newpassword', sa
5. How to determine which fields do not allow empty?
Select column_name from information_schema.columns where is_null = 'no' and table_name = TABLENAME6. How do I find a table with the same field in the database? a. Syrics known to the column name SELECT B.NAME AS TABLENAME, A.NAME As ColumnName from syscolumns a.id = B.ID and B.TYPE = 'u' and a.name = ' Your field name '
b. Unknown column names All column name Select O.Name as Tablename, S1.Name As ColumnName from syscolumns s1, sysobjects o where s1.id = o w i w = 'U' AND EXISTS (SELECT 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)
7. Query the XXX line data
Assumption ID is the primary key: Select * from (select top xxx * from yourtable) aa where not exists (SELECT 1 from (SELECT TOP XXX-1 * from yourtable) bb where a.id = bb.id) If using a cursor is also possible FETCH ABSOLUTE [NUMBER] from [cursor_name] The number of rows is absolute
8. SQL Server Date calculation a. One month of the first day Select Dateadd (mm, datediff (mm, 0), getdate ()), 0) b. This week's Monday SELECT DATEADD (WK, Datediff (WK, 0, Getdate ()), 0) C. One year of the first day Select Dateadd (YY, dateDiff (yy, 0, getdate ()), 0) D. Quarter first day Select Dateadd (QQ, Datediff (QQ, 0 Getdate ()), 0) E. last day Select Dateadd (MS, -3, DateAdd (mm, Datediff (mm, 0, getdate ()), 0) f. last year SELECT DATEADD (MS, -3, Dateadd (YY, Datediff (Yy, 0, getDate ()), 0)) G. Select Dateadd (MS, -3, DateAdd (M, 0, 0, Getdate) this month ()) 1, 0)) h. This month's first Monday Select Dateadd (WK, Datediff (WK, 0, Dateadd (DD, 6-datepart (DAY, Getdate ()), getdate ())) 0) i. This year, the last day Select Dateadd (MS, -3, DateAdd (YY, Datediff (YY, 0, getDate ()) 1, 0)). Thanks to those who offer relevant SQL authorities.
