Exquisite SQL statements

• Note: copy a table (only copy structure, source table name: a new table name: B)
  Select * into B from a where 1 <> 1

• Description: copy a 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, Submitter, and 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: External join query (table name 1: Table a 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
• Note: Five minutes in advance of the schedule
  Select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 5
• Note: Two associated tables are used to delete information that is already unavailable to the primary table in the secondary table.
  Delete from info where not exists (select * from infobz where info. infid = infobz. infid)
• Note :--

  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

• Note :--
  Select * from studentinfo where not exists (select * from student where studentinfo. id = student. id) and Department name = '"& strdepartmentname &"' and major name = '"& strinclusionname &" 'order by gender, student source, total score of the College Entrance Examination
• Go to the telephone fee statistics of each unit from the database for one year (two sources of the fixed telephone fee congratulation fertilizer List)

  SELECT a. userper, a. tel, a. standalone, TO_CHAR (a. telfeedate, 'yyyy') AS telyear,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '01', a. factration) as jan,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '02', a. factration) as fri,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '03', a. factration) as mar,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '04 ', a. factration) as apr,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '05 ', a. factration) as may,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '06', a. factration) as jue,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '07 ', a. factration) as jul,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '08 ', a. factration) as agu,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '09', a. factration) as sep,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '10', a. factration) as oct,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '11', a. factration) as nov,

  SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '12', a. factration) AS DEC

  FROM (SELECT a. userper, a. tel, a. standalone, B. telfeedate, B. factration

  From telfeestand a, telstmb

  WHERE a. tel = B. telfax)

  Group by a. userper, a. tel, a. standalone, TO_CHAR (a. telfeedate, 'yyyy ')

• Notes: Four-table join query Problems
  Select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where .....
• Note: Obtain 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 FROM Handle where not HandleID IN (SELECT. handleID-1 FROM Handle)
• A problem with an SQL statement: column and column Conversion
  Select * from v_temp
  The preceding view result is as follows:
  User_name role_name
  -------------------------
  System Administrator
  Feng Administrator
  Feng users
  Test Users
  The result is as follows:
  User_name role_name
  ---------------------------
  System Administrator
  Feng administrator, general user
  Test Users
  ==============================
  Create table a_test (name varchar (20), role2 varchar (20 ))
  Insert into a_test values ('lil', 'admin ')
  Insert into a_test values ('zhang', 'admin ')
  Insert into a_test values ('zhang', 'average user ')
  Insert into a_test values ('change', 'General user ')

  Create function join_str (@ content varchar (100 ))
  Returns varchar (2000)
  As
  Begin
  Declare @ str varchar (2000)
  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
  For two tables with the same structure, one table has about 30 thousand records and one table has about 20 thousand records. How can I quickly find different records of the two tables?
  ======================================
  Here is a test method for getting 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 modify several entries of several fields in 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 * from n2) a group by OrderID having count (*)> 1

  Yes, and the IDs of different records are displayed.
  The following applies to situations where both parties record the same,

  Select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count (*)> 1)
  Records that do not exist between the two parties are better handled.
  -- Delete several records in n1 and n2
  Delete from n1 where orderID in ('20140901', '20160901 ')
  Delete from n2 where orderID in ('20140901', '20160901 ')

  --*************************************** **********************
  -- Both parties have this record but are not exactly the same
  Select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count (*)> 1)
  Union
  -- If n2 exists but does not exist in n1, it is stored in, limit 30.
  Select * from n1 where OrderID not in (select OrderID from n2)
  Union
  -- N1.
  Select * from n2 where OrderID not in (select OrderID from n1)

• Four methods to obtain n to m records in the table:

  1.
  Select top m * into temporary table (or table variable) from tablename order by columnname -- insert top m pen
  Set rowcount n
  Select * from Table variable order by columnname desc

  2.
  Select top n * from (select top m * from tablename order by columnname) a order by columnname desc

  3. If there are no other identity columns in tablename, then:
  Select identity (int) id0, * into # temp from tablename

  N to m statements:
  Select * from # temp where id0> = n and id0 <= m

  If you report an error when executing the select identity (int) id0, * into # temp from tablename statement, this is because the select into/bulkcopy attribute in the middle of your DB is not enabled and must be executed first:
  Exec sp_dboption your DB name, 'select into/bulkcopy', true

  4. If the table contains the identity attribute, it is simple:
  Select * from tablename where identitycol between n and m

• How do I delete repeated records 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 (30), @ f_key varchar (30 ))
  -- F_key indicates the group field, that is, the primary key field.
  As
  Begin
  Declare @ max integer, @ id varchar (30), @ SQL varchar (7999), @ type integer
  Select @ SQL = 'maid cur_rows cursor for select' + @ f_key + ', count (*) from '+ @ t_name + 'group by' + @ f_key + 'having count (*)> 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 policypes
  Select * from syscolumns where id = object_id ('A _ dist ')

• Maximum sorting of query data (only one statement can be used for writing)
  Create table hard (qu char (11), co char (11), 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 query results are as follows:

  Qu co je
  ---------------------------
  A 6 9
  A 2 4
  B 3 6
  B 2 5
  C 6 7
  C 3 4

  Group by qu. In each group, the first two digits of je are the largest !!
  Only one SQL statement can be used !!!
  Select * from hard a where je in (select top 2 je from hard B where a. qu = B. qu order by je)

• Are you sure you want to delete the SQL statement for Repeated Records?
  How to delete a record with the same field, leaving only one record.
  For example, the table test contains the id and name fields.
  If there are records with the same name, only one record is left, and the remaining records are deleted.
  The content of the name is not fixed, and the number of identical records is not fixed.
  Is there such an SQL statement?
  ====================================
  A: A complete solution:

  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 non-Repeated Records in Table temp1:
  Insert temp1 select [flag field id], count (*) from [Table name] group by [flag field id] having count (*) = 1

  3. Create a table that contains all non-Repeated Records:
  Select * into temp2 from [Table name] where flag field id in (select flag field id from temp1)

  4. Delete duplicate tables:
  Delete [Table name]

  5. Restore table:
  Insert [Table name] select * from temp2

  6. delete a 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 (30), @ f_key varchar (30 ))
  -- F_key indicates the group field, that is, the primary key field.
  As
  Begin
  Declare @ max integer, @ id varchar (30), @ SQL varchar (7999), @ type integer
  Select @ SQL = 'maid cur_rows cursor for select' + @ f_key + ', count (*) from '+ @ t_name + 'group by' + @ f_key + 'having count (*)> 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 policypes
  Select * from syscolumns where id = object_id ('A _ dist ')

• Conversion of rows and columns-common

  Assume that there is a student summary table (CJ) as follows:
  Name Subject Result
  Zhang San Chinese 80
  Zhang San, mathematics 90
  Zhang San physical 85
  Li Si Chinese 85
  Li Si mathematics 92
  Li Si physical 82

  Want to become
  Name, Chinese, Mathematics, 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 + ''' then Result end) [' + Subject + ']'
  From (select distinct Subject from CJ) as
  Select @ SQL = @ SQL + 'from test group by name'
  Exec (@ SQL)

  Column and column conversion-Merge

  Table,
  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
  Create function fmerg (@ id int)
  Returns varchar (8000)
  As
  Begin
  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

  -- Call the custom function to obtain the result.
  Select distinct id, dbo. fmerg (id) from Table

• How to obtain all column names of a data table

  The method is as follows: first obtain the SYSTEMID of the data table from the SYSTEMOBJECT system table, and then retrieve all the column names of the data table from the SYSCOLUMN table.
  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'

• Use SQL statements to change user passwords

  Sysadmin role is required to modify other users.
  EXEC sp_password NULL, 'newpassword', 'user'

  If the account is SA, execute EXEC sp_password NULL, 'newpassword', sa

• How can I determine which fields of a table cannot be blank?

  Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'no' and TABLE_NAME = tablename

• How can I find a table with the same fields in the database?
  A. query the names of known Columns
  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'
• Query all names of unknown columns 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 row xxx data

  Assume that 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 aa. id = bb. id)

  You can also use a cursor.
  Fetch absolute [number] from [cursor_name]
  The number of rows is absolute.

• SQL Server date calculation
  A. the first day of a month
  Select dateadd (mm, DATEDIFF (mm, 0, getdate (), 0)
  B. Monday of the week
  Select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0)
  C. The first day of a year
  Select dateadd (yy, DATEDIFF (yy, 0, getdate (), 0)
  D. The 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. Last day of the month
  SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 ))
  H. The first Monday of this 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 )).
• Obtain the 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 (mb. 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
  JOIN policypes 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 <= I. keycnt ))
  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

• SQL statement that extracts detailed descriptions of fields from all tables in the database

  SELECT
  (Case when a. colorder = 1 then d. name else ''end) n' table name ',
  A. colorder n' Field Sequence Number ',
  A. name n' field name ',
  (Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else''
  End) n' identifiers ',
  (Case when (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 'number of bytes occupied ',
  COLUMNPROPERTY (a. id, a. name, 'precision ') as N 'length ',
  Isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0) as N 'decimal ',
  (Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
  Isnull (e. text, '') n' default value ',
  Isnull (g. [value], '') AS n' field description'
  FROM syscolumns
  Left join policypes 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

• Quickly retrieve the total number of test records in a table [very effective for large-capacity tables]

  Quick query of the total number of test records:
  Select rows from sysindexes where id = object_id ('test') and indid in (0, 1)

  Update 2 set KHXH = (ID + 1) \ 2 2 2 rows increment number
  Update [23] set id1 = 'no. '+ right ('2013' + id, 6) where id not like 'no %' // increments
  Update [23] set id1 = 'no. '+ right ('000000' + replace (id1, 'no.', ''), 6) // increment the position
  Delete from [1] where (id % 2) = 1
  Odd

• Replace the table name
  Update [1] set domurl = replace (domurl, 'upload/Imgswf/', 'upload/Photo/') where domurl like '% Upload/Imgswf/%'
• Truncation
  Select left (Table Name, 5)