How to Make master: exquisite SQL statement description Author: Unknown Source: Unknown Date: 2005-12-16 11:59:16 Posted: Polaris decrease font increase font
How to become a master from a rookie into a master, the SQL statement that is flexible is essential. This article collected some of the more classic, common SQL statements for your reference, I hope to help everyone.
Description: Replication table (only copy structure, source name: A new table name: b)
SQL: SELECT * INTO B from a where 1 <> 1
Description: Copy Table (copy data, source name: a target table name: b)
SQL: INSERT INTO B (A, B, C) SELECT D, E, F from B;
Description: Display article, author and last reply time
SQL: SELECT A.TITLE, A.USERNAME, B.Adddate from Table A, (Select Max (AddDDate) Addddate from Table Where Table.title = a.title) B
Description: Outer connection 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
Description: Reminder in advance in advance
SQL: SELECT * FROM schedule Arranging WHERE DATEDIFF ('minute', f Start time, getdate ())> 5
Description: Two related tables, delete information that is already 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 / 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:
SQL:
select * from studentinfo where not exists (select * from student where studentinfo.id = student.id) and department name = ' "& strdepartmentname &"' and professional name = ' "& strprofessionname &"' order by gender, students, the college entrance examination total score Description :
From the database, the unit of telephone bills (telephone billing, constraints, payment, single source)
SQL:
Select a.user, a.tel, a.standfee, to_char (a.telfeedate, 'yyyy') as Telyear,
SUM (decode (to_char (a.telfeedate, 'mm'), '01', a.factration) AS JAN,
Sum (decode (a.telfeedate, 'mm'), '02', a.factration) ASFRI,
Sum (decode (a.telfeedate, 'mm'), '03', a.factration) AS Mar,
SUM (decode (a.telfeedate, 'mm'), '04', a.factration) AS APR,
Sum (decode (a.telfeedate, 'mm'), '05', a.factration) AS May,
Sum (decode (a.telfeedate, 'mm'), '06', a.factration) AS JUE,
Sum (decode (a.telfeedate, 'mm'), '07', a.factration) AS JUL,
SUM (decode (to_char (a.telfeedate, 'mm'), '08', a.factration) AS AGU,
Sum (decode (a.telfeedate, 'mm'), '09', a.factration) AS SEP,
Sum (decode (a.telfeedate, 'mm'), '10', a.factration) AS OCT,
Sum (decode (a.telfeedate, 'mm'), '11', a.factration) AS NOV,
Sum (decode (to_char (a.telfeedate, 'mm'), '12', a.factration) AS DEC
From (Select A.user, A.TEL, A.Standfee, B.Telfeedate, B.Factration
From TelFeestand A, Telfee B
Where a.tel = b.telfax) a
Group by a.user, a.tel, a.standfee, to_char (a.telfeedate, 'yyyy')
Description: Four Table Interview Questions:
SQL: Select * from a left inner join b on a.a = B.B Right Inner Join C on A.A = C.D where ..... Description: Get the smallest unused ID number in the table
SQL:
SELECT (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)