Exquisite SQL statement introduction

xiaoxiao2021-03-19  198

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)

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

New Post(0)