Exquisite SQL statement

zhaozj2021-02-16  91

Exquisite SQL statement 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_date, '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.C inner Join D on a.a = d.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)

转载请注明原文地址:https://www.9cbs.com/read-13791.html

New Post(0)