Exquisite SQL statements

Source: Internet
Author: User

  • 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)

Contact Us

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: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.