Exquisite SQL Statements page 1/2

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 professional name
= '"& Strpolicsionname &" 'order by gender, student origin, 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. a = B. B right inner join c on a. a = c inner join d on
A. 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 a. 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
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, it is because
If the select into/bulkcopy attribute in the middle of your DB is not enabled, you must first execute:
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