A collection of sophisticated SQL statement techniques

Subtle "SQL" statements:

Copy table (copy structure only, source table name: A new table name: b)

Sql:select * into B from where 1<>1

Copy table (copy data, source table name: A target table name: b)

Sql:insert into B (A, B, c) select d,e,f from B;

Show article, submitter, and last reply time

Sql: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)

Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left-out JOIN b on a.a =

B.c

Schedule five minutes Advance reminder

Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

Two related tables, deleting information that is already not in the secondary table in the primary table

SQL:

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 ')-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：

SQL:

SELECT * from Studentinfo where NOT EXISTS (SELECT * from student where

studentinfo.id=student.id) and system name = ' &strdepartmentname& ' and professional name

= ' &strprofessionname& ' ORDER by sex, source of students, college entrance examination score.