Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
On the internet often turn, often see some people in order to get some SQL statements and the burn, now I particularly own collection of some of the more refined code of SQL out and share with you
1. Row and column conversion--ordinary
Suppose there is a Student score table (CJ) as follows name Subject result John Language 80 three Mathematics 90 three physics 85 Li four languages 85 Dick Mathematics 92 Dick Physics 82
Want to be the name of the Chinese mathematical physics Zhang 380 90 85 Li 485 92 82
DECLARE @sql varchar (4000) Set @sql = ' Select Name ' Select @sql = @sql + ', sum (case Subject when ' +subject+ "' then result [' +subject+ '] ' from (select distinct Subject to CJ) as aselect @sql = @sql + ' from test group by name ' EXEC (@sql)
2. Row and column conversions-merging
Table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to convert table b:id PID 1 1,2,3 2 1,2 3 1
Create a merged function creation function Fmerg (@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 the custom function to get the result select DISTINCT Id,dbo.fmerg (ID) from Table A
3. How to get all the column names of a data table
The method is as follows: Get the systemid of the datasheet from the SystemObject system table, and then syscolumn the table with all the column names of the datasheet. The SQL statement is as follows: Declare @objid int, @objname char 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 easy? Oh, but often use AH.
4. Change the user's password through SQL statements
To modify someone else's, need sysadmin role EXEC sp_password NULL, ' newpassword ', ' User '
If the account number executes exec sp_password NULL for SA, ' newpassword ', SA
5. How can you tell which fields in a table are not allowed to be empty?
Select column_name from INFORMATION_SCHEMA. COLUMNS where is_nullable= ' NO ' and table_name=tablename
6. How do I find a table in the database that contains the same fields? A. Checking known column names select B.name as tablename,a.name as ColumnName from syscolumns a INNER JOIN sysobjects B on A.id=b.id and B.type= ' u ' and a.name= ' your field name '
B. Unknown column name check all column names that appear in different tables select O.name as tablename,s1.name as ColumnName from syscolumns s1, sysobjects o Where s1 . id = o.id and o.type = ' U ' and Exists (Select 1 from syscolumns S2 Where s1.name = S2.name and s1.id <> s2.id)
7. Query XXX line data
Suppose the ID is a primary key: SELECT * FROM (select top XXX * from yourtable) AA where NOT EXISTS (select 1 from (select Top X Xx-1 * from yourtable) BB where aa.id=bb.id) if the cursor is also available, fetch absolute [number] from [cursor_name] rows are absolute rows
8. SQL Server Date calculation a. First day of one months select DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0) B. This week in Monday select DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0) c. First day of the year select DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0) & nbsp D. First day of the quarter select DATEADD (qq, DATEDIFF (Qq,0,getdate ()), 0) E. Last day of last month select DATEADD (ms,- 3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0)) F. Last day of last year select DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0) G. The last day of this month select DateAdd (Ms,-3,dateadd mm, (DATEDIFF, GETDATE ()) +1, 0) H. This month's first Monday select DATEADD (wk, DATEDIFF DateAdd (dd,6- DatePart (Day,getdate ()), GETDATE ()) ), 0 I. The last day of this year select DateAdd (ms,-3,dateadd yy, DATEDIFF (yy, 0,getdate ()) +1,&nbsP 0)).
Thanks to the authors who provide relevant SQL online
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to firstname.lastname@example.org. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
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:
and provide relevant evidence. A staff member will contact you within 5 working days.