Exquisite SQL statement (repost)

xiaoxiao2021-03-06 45

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)

============================================================================================================================================================================= # * From v_temp The above view results are as follows: user_name role_name ------------------------- System Administrator Administrator Feng Administrator Feng General User TEST General User I want to turn the result to this: user_name role_name --------------------------- System administrator manager Feng administrator, general user TEST general users =================== Create Table A_test (Name Varchar (20), role2 varchar (20)) Insert Into a_test value ('Lee', 'Administrator') Insert INTO A_TEST VALUES ('Zhang', 'Administrator') INTO A_TEST VALUES ('Zhang', 'General User') Insert Into A_Test Values ('Chang', 'General User')

Create Function Join_Str (@content varchar (100)) Returns varchar (2000) asbegindeclare @str varchar (2000) set @str = '' select @ str = @ Str ',' RTRIM (role2) from a_test where [name] = @ContentSelect @ Str = Right (@ Str, Len (@str) -1) Return @strendgo

- Call: SELECT [Name], dbo.join_STR ([Name]) role2 from a_test group by [name]

--Select Distinct Name, DBo.uf_test (Name) from a_test

2, help! Two tables with the same two tables in the same structure, a table has a record of about 30,000, a table has a record of about 20,000, how do I quickly find different records of two tables? ============================ gives you a test method, from the ORDERS Items in Northwind. Select * Into n1 from Ordersselect * INTO N2 from Orders

Select * from n1select * from n2

- Add primary key, then modify a number of 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 n1Union select * from n2) a group by Orderid Having count (*)> 1 should be, and the ID of the different records is displayed. The following applications are the same as those records.

Select * from n1 where orderid in (Select * from n1union select * from n2) a group by orderid hoving count (*)> 1) As for the records of the two sides, the records that do not exist are better - deleted N1, Several records in N2 DELETE from N1 WHERE ORDERID IN ('10728', '10730') delete from n2 where orderid in ('11000', '11001')

- ******************************************************** ************ - Both parties have this record but not exactly the same SELECT * from n1 where orderid in (Select ORDERID FROM (Select * from n1Union Select * from n2) a group by orderid Having Count (*)> 1) Union - N2 exists but in N1 in N1 in 10728, 10730select * from n1 where orderid not in (select Orderid from N2) Union - N1 is not in N2 At 11000, 11001Select * from N2 WHERE ORDERID NOT IN (SELECT ORDERID from N1) 3. Four methods table N to M record:

1.Select Top M * INTO Temporary Table (or Table Variable) from Tablename Order By ColumnName - Insert Top M Pen into Set RowCount NSelect * from Table Variable Order by ColumnName Desc

2.Select Top N * from (SELECT TOP M * from TableName Order by ColumnName) Aorder by ColumnName Desc

3. If there is no other Identity column in Tablename, then: select Identity (int) ID0, * INTO #TEMP from TableName

Take N to M strip statements: select * from #temp where id0> = n and id0 <= m

If you report the SELECT IDENTITY (Int) ID0, * INTO #TEMP from Tablename This statement is wrong, it is because the select into / bulkcopy attribute in your DB is not open, you don't open: Exec sp_dboption Your DB name, 'Select Into / Bulkcopy', True

4. If there is a Identity property in the table, it is simple: select * from Tablename where IdentityCol BetWeen N and M 5. How to delete a duplicate record 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 vircha (30)) - f_key represents a packet field, ie primary key field asbegindeclare @max integer, @ ID varchar (7999), @ type Integerselect @SQL = 'DECLARE 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_keyif @ type = 56Select @SQL = 'delete from' @ t_name 'where' @ f_key '=' @id if @ Type = 167select @SQL = 'delete from' @ T_name 'Where' @ f_key '=' '' '' @id '' 'EXEC (@SQL) Fetch cur_rows @ id, @ max end close cur_rows deallocate cur_rowsset rowcount 0nd

Select * from systemypesslection * from syscolumns where id = Object_id ('a_dist')

6. Query data for maximum sorting problems (only one statement) 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 results requested to query are as follows:

Qu CO JE --------------------- ----- A 6 9A 2 4B 3 6B 2 5C 6 7C 3 4

Just press the QU group, take the top 2 digits in JE in each group! ! And you can only use a SQL statement! ! ! Select * from hard a where Je in (SELECT TOP 2 JE from Hard B Where A.qu = B.qu Order By JE) 7. Ask to delete the SQL statement repeated record? How to delete the record with the same field, leaving only one. For example, there is ID in Test, and the Name field leaving only one, the rest is left. The content of the Name is uncertain, the same record number is uncertain. Is there such a SQL statement? ============================== A: a complete solution:

Record the repeated record into the TEMP1 table: SELECT [flag field ID], count (*) INTO TEMP1 FROM [Name] group by [flag field ID] HAVING Count (*)> 1

2, record the recorded record in Temp1 table: INSERT TEMP1SELECT [Sign Field ID], count (*) from [Name] Group by [Sign Field ID] HAVING Count (*) = 1

3, make a table containing all non-repeated records: SELECT * INTO TEMP2 FROM [Terminal] WHERE flag field ID in (SELECT flag field id from temp1)

4, delete repeat table: delete [table name]

5, recovery table: INSERT [Name] Select * from Temp2

6, delete temporary table: DROP TABLE TEMP1DROP TABLE TEMP2 ======================================================================================================== B: 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

Select * from systemypesslection * from syscolumns where id = Object_id ('a_dist')

Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.

Published on July 19, 2004 8:23 PM

comment

Reply: Exquisite SQL statement

2004-07-23 3:17 PM

The sea laughs

SQL Import & Export Command

EXEC MASTER..XP_CMDSHEC 'BCP SETTLEDB.DBO.SHANGHU OUT C: /TEMP1.XLS -C -Q -S "gnetdata / gnetdata" -u "sa" -p "" / ******** ** Import Excel

SELECT *

From OpenDataSource ('Microsoft.jet.OleDb.4.0',

'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions

/ * Dynamic file name

Declare @fn varchar (20), @ s varchar (1000)

Set @fn = 'c: /test.xls'

Set @S = '' 'Microsoft.jet.OleDb.4.0' ',

'' Data Source = "' @ fn '"; user ID = admin; password =; extended Properties = Excel 5.0 '' '

Set @S = 'SELECT * OPENDATASOURCE (' @ s ') ... Sheet1 $'

EXEC (@S)

* /

SELECT CAST (CAST (Subject No. AS Numeric (10, 2)) AS NVARCHAR (255)) '' Convert alias

From OpenDataSource ('Microsoft.jet.OleDb.4.0',

'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions

/ ********************* EXCEL is derived from remote SQL

INSERT OpenDataSource

'Sqloledb',

'Data Source = Remote IP; User ID = SA; Password = Password'

). Name .dbo. Table name (column name 1, column 2)

SELECT column name 1, column name 2

From OpenDataSource ('Microsoft.jet.OleDb.4.0',

'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions

/ ** import text file

EXEC MASTER..XP_CMDSHEC 'BCP DBNAME..TABLENAME IN C: /DT.TXT -C -SSERVERNAME -USA-Password'

/ ** Export text file

Exec master..xp_cmdshell 'bcp dbname..tablename out c: /dt.txt -c -ssrvername -usa -ppassword'

Exec master..xp_cmdshell 'bcp "Select * from dbname..tablename" Queryout C: /dt.txt -c -ss "exports to txt text, separated by comma

Exec master..xp_cmdshell 'bcp "library name .. Name" D: /TT.TXT "-c -t, -u sa -p password'

Bulk INSERT library name .. Name

From 'c: /test.txt'

With

FIELDTERMINATOR = ';',

Rowterminator = '/ n'

)

- / * DBASE IV file

SELECT * FROM

OpenRowSet ('Microsoft.jet.OleDb.4.0'

, 'DBASE IV; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Data 4.DBF]')

- * /

- / * DBASE III file

SELECT * FROM

OpenRowSet ('Microsoft.jet.OleDb.4.0'

, 'DBASE III; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Information 3.dbf]')

- * /

- / * FoxPro database

Select * from OpenRowSet ('msdasql',

'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = C: /',

'Select * from [aa.dbf]')

- * /

/ ************** Import DBF file *************** /

Select * from OpenRowSet ('msdasql',

'Driver = Microsoft Visual FoxPro Driver;

SourceDB = E: / VFP98 / DATA;

SourceType = DBF ',

'Select * from customer where country! = "USA" ORDER BY Country')

/ **************** Export to DBF *************** /

If you want to export data to a structure (ie, existing) FoxPro list, you can directly use the following SQL statement

INSERT INTO OpenRowSet ('msdasql',

'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = C: /',

'Select * from [aa.dbf]')

SELECT * FROM table

Description:

SOURCEDB = C: / Specify the folder where the FoxPro table is located

AA.DBF Specifies the file name of the FoxPro table.

/ ************ Export to Access ****************** /

INSERT INTO OpenRowset ('Microsoft.jet.OleDb.4.0',

'x: /a.mdb'; 'admin'; '', a table) SELECT * FROM database name ..b table

/ ************* Import Access ******************************* / INSERT INTO B SELET * from OpenRowSet ('Microsoft.jet .Oledb.4.0 ',

'x: /a.mdb'; 'admin'; '', a table)

File name is parameter

Declare @fname varchar (20)

Set @fname = 'd: /test.mdb'

EXEC ('SELECT A. * from OpenDataSource (' 'Microsoft.jet.OleDb.4.0' ",

'' ' @ fname ' ''; '' admin ''; '' '', Topics AS A ')

SELECT *

From OpenDataSource ('Microsoft.jet.OleDb.4.0',

'Data Source = "f: /northwind.mdb"; Jet OLEDB: Database Password = 123; User ID = Admin; PASSWORD =;') ... Product

******************* Import XML file

Declare @IDOC INT

Declare @doc varchar (1000)

--Sample XML Document

Set @doc = '

Customer Was Very Satisfied

important

Happy Customer.

- Create An Internal Representation of the xml document.

EXEC SP_XML_PREPAREDocument @idoc output, @doc

- Execute A Select Statement Using OpenXML ROWSet Provider.select *

From OpenXML (@idoc, '/ root / customer / order ", 1)

With (Oid Char (5),

Amount float,

Comment nText 'text ()')

EXEC SP_XML_REMOVEDOCUMENT @IDOC

???????

/ ********************* EXCEL is guided to txt ********************* ***************** /

I want to use

Select * inTo OpenDataSource (...) from openDataSource (...)

Import an Excel file content into a text file

Suppose there is two columns in Excel, the first list is named, the second list is a very account (16 digits)

And the bank account is exported to the text file and divided by two parts, and the first 8 bits and the last 8 bits were separated.

Zou Jian:

If you want to insert the statement above, the text file must exist, and there is a line: name, bank account 1, bank account 2

Then you can insert with the following statement

Note that the file name and directory are modified according to your actual situation.

INSERT INTO

OpenDataSource ('Microsoft.jet.OleDb.4.0'

, 'Text; HDR = YES; Database = C: /'

) ... [aa # txt]

-, aa # txt)

- * /

Select Name, Bank Account 1 = Left (Bank Account, 8), Bank Account 2 = Right (Bank Account, 8)

From

OpenDataSource ('Microsoft.jet.OleDb.4.0'

, 'Excel 5.0; HDR = YES; IMEX = 2; Database = C: /A.XLS'

-, Sheet1 $)

) ... [Sheet1 $]

If you want to insert and generate a text file directly, use BCP

Declare @SQL VARCHAR (8000), @ TbName Varchar (50)

- Import Excel table content to a global temporary table

SELECT @tbname = '[## Temp' Cast (newid () as varchar (40)) ']'

, @ SQL = 'SELECT Name, Bank Account 1 = Left (Bank Account, 8), Bank Account 2 = Right (Bank Account, 8)

INTO ' @ TBNAME ' from

OpenDataSource ('' Microsoft.jet.OleDb.4.0 '"

, '' Excel 5.0; HDR = YES; IMEX = 2; Database = C: /A.XLS ''

) ... [sheet1 $] '

EXEC (@SQL)

- Then use BCP from global temporary table to text files

Set @ SQL = 'BCP "' @ TBNAME '" OUT "C: /AA.TXT" / S "(local)" / p "" / c'

Exec master..xp_cmdshell @SQL

- Delete temporary table

EXEC ('Drop Table' @ TBNAME)

/ ****************************************************************************************************** ****************** / with BCP implementation stored procedures

/ *

Implement data import / export stored procedure

Import / export throughout the database / single table can be implemented according to different parameters

Call example:

- Export call example

---- Export a single table

Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' c: /zj.txt' ,1

---- Export the entire database

Exec file2table 'zj', '', '', 'xzkh_sa', 'C: / DOCMAN', 1

- Imported call example

---- Import a single table

Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' c: / zj.txt' ,0

---- Import the entire database

Exec file2table 'zj', '', '', 'xzkh_sa', 'C: / DOCMAN', 0

* /

If EXISTS (SELECT 1 from sysobjects where name = 'file2table' and objectproperty (id, 'isprocedure') = 1)

Drop Procedure File2Table

Create Procedure File2Table

@servername varchar (200) - server name

@ username varchar (200) - Username, if using NT authentication mode, it is empty ''

, @ Password Varchar (200) - Password

, @ TBNAME VARCHAR (500) - Database. DBO. Table name, if not specified: .dbo. Download, export all user tables for the database

@ filename varchar (1000) - Import / Export Path / File Name, if the @TBName parameter indicates that the entire database is exported, this parameter is the file storage path, the file name is automatically used .txt

@ isout bit --1 is exported, 0 is imported

Declare @SQL VARCHAR (8000)

If @tbname like '%.%.%' - If the table name is specified, the single table is exported directly.

Begin

Set @ SQL = 'BCP' @ TBNAME

Case when @ ixout = 1 TEN 'OUT' ELSE 'IN' END

'"' @ filename '" / w'

'/ S' @ ServerName

Case When Isnull (@username, '') = '' TEN '' ELSE '/ U' @ Username End

'/ P' isnull (@password, '')

Exec master..xp_cmdshell @SQL

end

Else

Begin exports the entire database, define a cursor, remove all user tables

Declare @m_tbname varchar (250)

If Right (@ filename, 1) <> '/' set @ filename = @ filename '/' set @ m_tbname = 'declare #tb cursor for select name from' @ tbname '.. sysobjects where xtype =' u ' '' '

EXEC (@m_tbname)

Open #TB

Fetch next from #TB INTO @m_tbname

While @@ fetch_status = 0

Begin

Set @ SQL = 'bcp' @ TBNAME '..' @ m_tbname

Case when @ ixout = 1 TEN 'OUT' ELSE 'IN' END

'"' @ filename @ m_tbname '. txt" / w'

'/ S' @ ServerName

Case When Isnull (@username, '') = '' TEN '' ELSE '/ U' @ Username End

'/ P' isnull (@password, '')

Exec master..xp_cmdshell @SQL

Fetch next from #TB INTO @m_tbname

end

Close #TB

Deallocate #TB

end

/ ************ Oracle ************* /

Exec sp_addlinkedServer 'ORACLESVR',

'Oracle 7.3',

'Msdaora',

'Orcldb'

Delete from OpenQuery (Mailser, 'Select * from Yulin')

Select * from OpenQuery (Mailser, 'Select * from Yulin')

Update OpenQuery (mailser, 'select * from yulin where id = 15') set disorder = 555, cataGO = 888

INSERT INTO OpenQuery (Mailser, 'Select Disorder, catago from yulin "Values (333, 777)

supplement:

For exporting with BCP, there is no field name.

Export with OpenRowSet and need to be built in advance.

Import in OpenRowSet, except for Access and Excel, non-public data imports are not supported

delete

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

9cbs

New Post(0)