Subtle SQL statement _mssql

• Description: Copy table (copy only structure, source table name: A new table name: b)
  SELECT * into B from a where 1<>1
  
  
• Description: Copy table (copy data, source table name: A target table name: b)
  Insert into B (A, B, c) select d,e,f from B;
  
  
• Description: Displays the article, the author, and the last reply time
  Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b
  
  
• Description: Outer join query (table name 1:a table name 2:b)
  Select A.a, A.B, A.C, B.C, B.D, B.f from-a left-out JOIN b on a.a = B.C
  
  
• Description: Schedule five minutes advance reminder
  SELECT * FROM Schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
  
  
• Description: Two related tables, delete the information in the primary table that has not been in the secondary table
  Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)
  
  
• Description:--
  
  Sql:
  
  SELECT A.num, A.name, B.upd_date, b.prev_upd_date
  
  From TABLE1,
  
  (SELECT x.num, x.upd_date, Y.upd_date prev_upd_date
  
  From (SELECT NUM, Upd_date, Inbound_qty, Stock_onhand
  
  From TABLE2
  
  WHERE to_char (upd_date, ' yyyy/mm ') = To_char (sysdate, ' yyyy/mm ')) X,
  
  (SELECT NUM, Upd_date, Stock_onhand
  
  From TABLE2
  
  WHERE to_char (upd_date, ' yyyy/mm ') =
  
  To_char (to_date to_char (sysdate, ' yyyy/mm ') | | '/01 ', ' Yyyy/mm/dd ')-1, ' yyyy/mm ') Y,
  
  WHERE X.num = y.num (+)
  
  and X.inbound_qty + NVL (y.stock_onhand,0) <> X.stock_onhand) B
  
  WHERE A.num = B.num
  
  
• Description:--
  SELECT * from Studentinfo where does exists (SELECT * from student where studentinfo.id=student.id) and system name = ' "&strdepart mentname& "' and professional name = ' &strprofessionname& ' ORDER by sex, source of students, college entrance examination total score
  
  
• to go from the database to the unit telephone fee statistics for one year (telephone number quota, telegram, fertilizer list, two table sources)
  
  SELECT A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ') as Telyear,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as FRI,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as the MAR,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as APR,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as May,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as Jue,
  
  SUM (Decode (To_char (a.telfeedate, ' mm '), ' modified ', a.factration)) as June,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as AGU,
  
  SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as SEP,
  
  SUM (Decode (To_char (a.telfeedate, ' mm '), ' ten ', a.factration)) as OCT,
  
  SUM (Decode (To_char (a.telfeedate, ' mm '), ' one ', a.factration)) as NOV,
  
  SUM (Decode (To_char (a.telfeedate, ' mm '), ' a ', a.factration)) as DEC
  
  From (SELECT a.userper, A.tel, A.standfee, B.telfeedate, b.factration
  
  From Telfeestand A, Telfee b
  
  WHERE A.tel = B.telfax) A
  
  GROUP by A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ')
  
  
• Note: Four table joint check problem
  SELECT * from a left INNER join B on a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ....
  
  
• Description: Get the smallest unused ID number in the table
  
  
• Select (Case when EXISTS (SELECT * from Handle b WHERE b.handleid = 1) THEN MIN (Handleid) + 1 ELSE 1 end) as Handleid Handle WHERE not Handleid in (SELECT a.handleid-1 from Handle a)
  
  
• 
  SELECT * from V_temp
  The view results above are as follows:
  User_name Role_name
  ----- --------------------
  System Administrator Administrator
  Feng Administrator
  Feng General user
  Test general users
  to change the result:
  user_name role_name
  ---- -----------------------
  System Administrator Administrator
  Feng Administrator, General user
  Test general user
  ===================
  CREATE TABLE A_test ( Name varchar (role2 varchar)
  INSERT INTO a_test values (' Lee ', ' admin ')
  INSERT into a_test values (' Zhang ', ' admin ')
  INSERT into a_test values (' Zhang ', ' General user ')
  INSERT into a_test values (' regular ', ' General user ')
  
  Create function Join_str (@ Content varchar
  Returns varchar
  as
  begin
  Declare @str varchar (MB)
  Set @str = '
  Select @str = @str + ', ' +rtrim (role2) from a_test where [name]= @content
  Select @str =right (@str, Len (@str)-1)
  Return @str
  End
  Go
  
  --Call:
  SELECT [Name],dbo.join_str ([name]) Role2 from A_test Group by [name]
  
  -- SELECT DISTINCT name,dbo.uf_test (name) from A_test
  
  
• quickly compare two tables with the same structure
  Structure of the same two tables, a table with a record of about 30,000, a table with a record of about 20,000, how can I quickly find the different records of two tables?
  ============================
  Give you a test method that takes data from the Orders table in Northwind.
  SELECT * into N1 from orders
  SELECT * into N2 from orders
  
  SELECT * FROM N1
  SELECT * from N2
  
  --Add a primary key and then modify several of the fields in the N1
  ALTER TABLE n1 ADD constraint pk_n1_id primary key (OrderID)
  ALTER TABLE n2 add constraint pk_n2_id primary key (OrderID)
  
  Select OrderID from (SELECT * FROM N1 UNION SELECT * to N2) a group by OrderID have count (*) > 1
  
  It should be, and the IDs of the different records will be displayed.
  The following applies to the same situation as the two records,
  
  SELECT * FROM N1 where OrderID (SELECT OrderID from N1 UNION select * from N2) A group by OrderID has COUNT (*) > 1)
  It's better to have records that don't exist between the two sides.
  --delete several records from N1,N2
  Delete from N1 where OrderID in (' 10728 ', ' 10730 ')
  Delete from N2 where OrderID in (' 11000 ', ' 11001 ')
  
  --*************************************************************
  Both have the record but not exactly the same
  SELECT * FROM N1 where OrderID (SELECT OrderID from N1 UNION select * from N2) A group by OrderID has COUNT (*) > 1)
  Union
  --n2 in the N1, but not in the 10728,10730
  SELECT * FROM N1 where OrderID isn't in (select OrderID from N2)
  Union
  --n1 in the N2, but not in the 11000,11001
  SELECT * from N2 where OrderID isn't in (select OrderID from N1)
  
  
• Four methods to take table N to M Records:
  
  1.
  Select Top M * into a temporary table (or table variable) from TableName to ColumnName--inserts a top m pen
  SET ROWCOUNT n
  SELECT * FROM table variable ORDER BY columnname desc
  
  
  2.
  Select Top N * "from" (select top M * to TableName ORDER by ColumnName) an ORDER by columnname Desc
  
  
  3. If there are no other identity columns in the TableName, then:
  Select identity (int) id0,* into #temp from TableName
  
  The statements that take the N to M Bar are:
  SELECT * from #temp where id0 >=n and id0 <= m
  
  If you make an error while executing the statement of select identity (int) id0,* into #temp TableName, it is because the Select Into/bulkcopy attribute in the middle of your db is not opened to be executed first:
  EXEC sp_dboption your DB name, ' Select Into/bulkcopy ', True
  
  
  4. If the table has the identity attribute, so simple:
  SELECT * FROM tablename where Identitycol between N and M
  
  
• How do I delete a duplicate record in a table?
  CREATE TABLE a_dist (ID int,name varchar (20))
  
  INSERT into a_dist values (1, ' abc ')
  INSERT into a_dist values (1, ' abc ')
  INSERT into a_dist values (1, ' abc ')
  INSERT into a_dist values (1, ' abc ')
  
  exec up_distinct ' a_dist ', ' ID '
  
  SELECT * FROM A_dist
  
  CREATE PROCEDURE up_distinct (@t_name varchar), @f_key varchar (30))
  --f_key is a grouped field, that is, a primary key field
  As
  Begin
  Declare @max integer, @id varchar, @sql varchar (7999), @type integer
  Select @sql = ' Declare cur_rows cursor for select ' + @f_key + ', COUNT (*) from ' + @t_name + ' GROUP BY ' + @f_key + ' has cou NT (*) > 1 '
  EXEC (@sql)
  Open Cur_rows
  Fetch cur_rows into @id, @max
  While @ @fetch_status =0
  Begin
  Select @max = @max-1
  SET ROWCOUNT @max
  Select @type = Xtype from syscolumns where id=object_id (@t_name) and Name= @f_key
  If @type =56
  Select @sql = ' Delete from ' + @t_name + ' where ' + @f_key + ' = ' + @id
  If @type =167
  Select @sql = ' Delete from ' + @t_name + ' where ' + @f_key + ' = ' + ' + ' + @id + ' '
  EXEC (@sql)
  Fetch cur_rows into @id, @max
  End
  Close Cur_rows
  Deallocate cur_rows
  SET ROWCOUNT 0
  End
  
  SELECT * FROM systypes
  SELECT * from syscolumns where id = object_id (' a_dist ')
  
  
• Maximum sorting problem for query data (written in one statement only)
  CREATE TABLE Hard (qu Char (one), CO char (one), je Numeric (3, 0))
  
  INSERT into hard values (' A ', ' 1 ', 3)
  INSERT into hard values (' A ', ' 2 ', 4)
  INSERT into hard values (' A ', ' 4 ', 2)
  INSERT into hard values (' A ', ' 6 ', 9)
  INSERT into hard values (' B ', ' 1 ', 4)
  INSERT into hard values (' B ', ' 2 ', 5)
  INSERT into hard values (' B ', ' 3 ', 6)
  INSERT into hard values (' C ', ' 3 ', 4)
  INSERT into hard values (' C ', ' 6 ', 7)
  INSERT into hard values (' C ', ' 2 ', 3)
  
  
  The results of the request query are as follows:
  
  Qu Co JE
  ----------- ----------- -----
  A 6 9
  A 2 4
  B 3 6
  B 2 5
  C 6 7
  C 3 4
  
  
  is to press Qu Group, each group to take JE The largest of the top 2!!
  And you can only use one SQL statement!!!
  SELECT * from Hard a Where JE to (select top 2 je to hard b where a.qu=b.qu order by JE)
  
  
• find an SQL statement that deletes duplicate records?
  How to delete a record with the same field, leaving only one.
  For example, there are id,name fields in table test
  If a record with the same name leaves only one, the rest is deleted.
  The contents of name are indefinite, and the same number of records is uncertain.
  Is there such a SQL statement?
  ==============================
  A: A complete solution:
  
  To record duplicate records in the Temp1 table:
  Select [Flag Field id],count (*) into TEMP1 from [table name]
  Group BY [flag field ID]
  Having Count (*) >1
  
  2. Record the TEMP1 records into the table:
  Insert Temp1 SELECT [Flag Field Id],count (*) from [table name] GROUP by [flag Field ID] having count (*) =1
  
  3. Make a table containing all the records that are not duplicated:
  SELECT * into Temp2 from [table name] where flag field ID in (Select flag field ID from TEMP1)
  
  4, delete the repeating table:
  Delete [table name]
  
  5, restore the table:
  Insert [table name] SELECT * FROM Temp2
  
  6. Delete temporary table:
  drop TABLE Temp1
  drop TABLE Temp2
  ================================
  B:
  CREATE TABLE a_dist (ID int,name varchar (20))
  
  INSERT into a_dist values (1, ' abc ')
  INSERT into a_dist values (1, ' abc ')
  INSERT into a_dist values (1, ' abc ')
  INSERT into a_dist values (1, ' abc ')
  
  exec up_distinct ' a_dist ', ' ID '
  
  SELECT * FROM A_dist
  
  CREATE PROCEDURE up_distinct (@t_name varchar), @f_key varchar (30))
  --f_key is a grouped field, that is, a primary key field
  As
  Begin
  Declare @max integer, @id varchar, @sql varchar (7999), @type integer
  Select @sql = ' Declare cur_rows cursor for select ' + @f_key + ', COUNT (*) from ' + @t_name + ' GROUP BY ' + @f_key + ' has cou NT (*) > 1 '
  EXEC (@sql)
  Open Cur_rows
  Fetch cur_rows into @id, @max
  While @ @fetch_status =0
  Begin
  Select @max = @max-1
  SET ROWCOUNT @max
  Select @type = Xtype from syscolumns where id=object_id (@t_name) and Name= @f_key
  If @type =56
  Select @sql = ' Delete from ' + @t_name + ' where ' + @f_key + ' = ' + @id
  If @type =167
  Select @sql = ' Delete from ' + @t_name + ' where ' + @f_key + ' = ' + ' + ' + @id + ' '
  EXEC (@sql)
  Fetch cur_rows into @id, @max
  End
  Close Cur_rows
  Deallocate cur_rows
  SET ROWCOUNT 0
  End
  
  SELECT * FROM systypes
  SELECT * from syscolumns where id = object_id (' a_dist ')
  
  
• row-and-column conversion--normal
  
  assumes that there is a Student score table (CJ) as follows
  Name Subject result
  John Language no
  John Math
  John Physics The
  Dick language
  Dick Math,
  Dick Physics
  
  want to become
  Name Language Mathematics Physics
  Zhang 380,
  Lee 485
  
  Declare @sql varchar (4000)
  Set @sql = ' Select Name '
  Select @sql = @sql + ', sum (case Subject when ' +subject+ ' "then result E nd) [' +subject+ '] '
  from (select distinct Subject to CJ) as a
  Select @sql = @sql + ' from test group by name '
  E Xec (@sql)
  
  Row and column conversions-merge
  
  has table A,
  ID PID
  1 1
  1 2
  1 3
  2 1
  2 2
  3 1
  How to make Table B:
  ID PID
  1 1,2,3
  2 1,2
  3 1
  
  Create a merged function
  Create function Fmerg (@id int)
  Returns varchar (8000)
  as
  be Gin
  Declare @str varchar (8000)
  Set @str = '
  Select @str = @str + ', ' +cast (PID as varchar) from table A where id= @id
  Set @str =right (@str, Len (@str)-1)
  Return (@str)
  End
  Go
  
  --Invoke custom function to get results
  SELECT DISTINCT ID, Dbo.fmerg (ID) from table a
  
  
• 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 (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
  
  Or
  
  SELECT * from INFORMATION_SCHEMA. COLUMNS WHERE table_name = ' users '
  
  
• To change a user's password through an SQL statement
  
  Modify someone else's and need the sysadmin role
  EXEC sp_password NULL, ' newpassword ', ' User '
  
  If the account number executes exec sp_password NULL for SA, ' newpassword ', SA
  
  
• How can I 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
  
  
• How do I find a table with the same field in my database?
  A. Investigation of known 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 '
  
  
• 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
  )
  
  
• Query section XXX line data
  
  Suppose the ID is a primary key:
  SELECT * FROM (select top XXX * to yourtable) AA where NOT EXISTS (select 1 to (select Top Xxx-1 * from Yourtable) BB w Here Aa.id=bb.id)
  
  It is also possible to use cursors
  Fetch absolute [number] from [cursor_name]
  Number of rows is an absolute number of rows
  
  
• SQL Server Date Calculation
  A. First day of one months
  SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)
  B. Monday of this week
  SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)
  C. First day of the year
  SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)
  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 (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))
  G. The last day of the month
  SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))
  H. First Monday of the month
  Select DATEADD (wk, DATEDIFF (wk,0,
  DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ())
  ), 0)
  I. The last day of the year
  SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0)).
  
  
• get table structure [replace ' sysobjects ' with ' tablename ']
  
  SELECT Case IsNull (I.name, "")
  When ' Then '
  Else ' * '
  End as ISPK,
  object_name (a.id) as T_name,
  A.name as C_name,
  IsNull (SubString (M.text, 1, 254), ") as Pbc_init,
  T.name as F_datatype,
  Case IsNull (Typeproperty (t.name, ' Scale '), "")
  When ' Then Cast (a.prec as varchar)
  ELSE cast (a.prec as varchar) + ', ' + cast (a.scale as varchar)
  End as F_scale,
  A.isnullable as F_isnullable
  From syscolumns as A
  JOIN systypes as T
  On (A.xtype = T.xusertype and a.id = object_id (' sysobjects '))
  Left JOIN (sysindexes as I
  JOIN syscolumns as A1
  On (i.id = a1.id and a1.id = object_id (' sysobjects ') and (I.status & 0x800) = 0x800 and A1.colid <=))
  On (a.id = i.id and A.name = Index_col (' sysobjects ', I.indid, A1.colid))
  Left JOIN syscomments as M
  On (m.id = A.cdefault and ObjectProperty (A.cdefault, ' isconstraint ') = 1)
  ORDER BY A.colid ASC
  
  
• an SQL statement that extracts the detailed description of fields for all tables in the database
  
  SELECT
  (case when A.colorder=1 then D.name else "end) N ' table name ',
  A.colorder N ' field ordinal ',
  A.name N ' field name ',
  (Case when ColumnProperty (A.id,a.name, ' isidentity ') =1 then ' √ ' Else '
  End) N ' logo ',
  (SELECT count (*)
  From sysobjects
  WHERE (name in
  (SELECT name
  From sysindexes
  WHERE (id = a.id) and (Indid in
  (SELECT indid
  From Sysindexkeys
  WHERE (id = a.id) and (Colid in
  (SELECT colid
  From syscolumns
  WHERE (id = a.id) and (name = A.name)))) and
  (xtype = ' PK ')) >0 Then ' √ ' Else ' ' End ' N ' primary key ',
  B.name N ' type ',
  A.length N ' occupies bytes ',
  ColumnProperty (a.id,a.name, ' PRECISION ') as N ' length ',
  IsNull (ColumnProperty (a.id,a.name, ' Scale '), 0) as N ' decimal digits ',
  (case when a.isnullable=1 then ' √ ' else ') N ' Allow null ',
  IsNull (E.text, ') N ' default value ',
  IsNull (G.[value], ') as N ' field description '
  From Syscolumns A
  Left JOIN Systypes b
  On A.xtype=b.xusertype
  INNER JOIN sysobjects D
  On a.id=d.id and d.xtype= ' U ' and d.name<> ' dtproperties '
  Left JOIN syscomments E
  On A.cdefault=e.id
  Left Join Sysproperties G
  On a.id=g.id and a.colid = G.smallid
  Order by object_name (a.id), A.colorder
  
  
• Quick to get the total number of records in table test [very effective for bulk tables]
  
  To quickly get the total number of records for table test:
  Select rows from sysindexes where ids = object_id (' Test ') and indid in (0,1)
  
  Update 2 set khxh= (id+1) \2 2 line increment number
  Update [] Set id1 = ' No. ' +right (' 00000000 ' +id,6) where id not like ' no% '//incrementing
  Update [] Set id1= ' No. ' +right (' 00000000 ' +replace (id1, ' No. ', '), 6)///complement increment
  Delete FROM [1] where (id%2) =1
  Odd
  
  
• Replace table Name field
  Update [1] Set domurl = replace (Domurl, ' upload/imgswf/', ' upload/photo/') where Domurl like '%upload/imgswf/% '
  
  
• Cutting position
  SELECT Left (table name, 5)