format
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)
INSERT INTO B (A, B, C) SELECT D, E, F from B;
-------------------------------------------------- -------------
Description: Merge data (table name 1: a table name 2: B)
SELECT A, B, C from A Union SELECT D, E, F from B;
-------------------------------------------------- -------------
Description: Subquerion (Table 1: A Table 2: B)
SELECT A, B, C from a WHERE A in (SELECT D from B)
or:
SELECT A, B, C from A WHERE A in (1, 2, 3)
-------------------------------------------------- -------------
俺 俺 个 让 俺 俺 对 对 例 例 例
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
-------------------------------------------------- -------------
Self-connection takes out the data
Take only one of all the names
SELECT A.NAME from Table_name A Where A.ID in
(Select B.id from table_name b where A.id <> B.ID)
Simultaneously delete honor data
Delete from table_name where top_name.id in
(Select B.id from table_name b where Table_name.id <> B.ID)
-------------------------------------------------- -------------
Select Isnull (A.Name, B.Name), Isnull (a.code, b.code) from table1 a
Full Out Join Table2 B on
A.ID = B.ID
-------------------------------------------------- -------------
Features:
Type Vender PCS
Computer A 1
Computer A 1
CD B 2
CD A 2
Mobile phone B 3
Mobile phone C 3
SELECT TYPE, SUM (Case Vender When 'a' Then PCS Else 0 End), SUM (Case Vender When 'C' PCS Else 0 End), SUM (Case Vender When 'B' Then PCS Else 0 End) from Tablename Group By TYPE ------------------------------------------------------------------------------------------------------------ ---------------
TO Annkie (alive)
Take a look at whether the above statement solves the problem?
Correct, it should be
SELECT DISTINCT kzx4dm, (SELECT COUNT (jylsfsdm) FROM tablename WHERE kzx4dm = TA.kzx4dm) AS bys_count, (SELECT COUNT (jylsfsdm) FROM tablename WHERE kzx4dm = TA.kzx4dm AND jylsfsdm = 10) AS yjs_count, yjs_count / bys_count AS jy_ratio
From Tablename as Ta
-------------------------------------------------- -------------
SELECT * FROM schedule Where Datediff ('minute', f start time, getdate ())> 5
The schedule reminded five minutes in advance.
-------------------------------------------------- -------------
CREATE OR REPLACE Procedure Dump_to_Web_TCLHD_SP_OBJ
AS
Begin
CALC_PIA_PRICE;
Delete from tclhd_sp_obj;
INSERT INTO TCLHD_SP_OBJ (Name, Code, ID, Price, Type, Fields) (
Select C.Description, C.SEGMENT1, A.INVENTORY_ITEM_ID, NVL (C.Attribute14, '0'), 0, NVL (C.Attribute 13, 0)
From mtl_item_categories a, mtl_categories b, MTL_SYSTEM_ITEMS C
Where a.category_id = b.category_id and b.segment1 = 'raw material'
And a.inventory_item_id = C.INVENTORY_ITEM_ID and A.OrGanization_ID = 21
And c.organization_id = 21 and c.INventory_Item_status_code = 'active');
COMMIT;
END;
-------------------------------------------------- -------------
From the database, the unit of telephone bills (telephone billing, constraints, payment, single source)
Select a.user, a.tel, a.standfee, to_char (a.telfeedate, 'yyyy') as Telyear,
Sum (Decode (a.telfeedate, 'mm'), '01', a.factration) AS JAN, SUM (Decode (to_char (a.telfeedate, 'mm'), '02', A.FACTRATION) AS fri,
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')
-------------------------------------------------- -------------
Interesting, I will come to one, solve the problem of jump:
SELECT MIN (BH) 1 from Table1 WHERE BH 1 Not in (SELECT BH from Table1)
-------------------------------------------------- -------------
Personally think that I have written for so long, only speaking is very bored, the meaning and flexible combination of statements is very important. The following is a stored process for writing a drug management, very classic:
Update Pharmacy Stock Set Inventory Quantity = Inventory Quantity - B. Dosage from [Pharmacy Stock] A,
(Select SUM) AS Dosage, Drug Price, FROM Patient WHERE Prescription = @ Recipeno and Prescription Sequence Number = @ RecipexNo
Group By drug price
) B Where A. Pharmaceutical price = b. Drug price and A. Treasure name = @ depotname
This is a reference to the B table to modify the inventory inventory, and there is a summation in the B table, and the table is associated with the B table while meeting a table condition.
-------------------------------------------------- -------------
I have been hired for a small program, the best, happy, and extracting 100 !! About SQL query display
The title is probably SQL Table 1: Class / Teacher Table 2 Students / Class Table 3 Mathematics / Student Table 4 Language / Student
The query display result is probably this:
Class 1 teacher 1
Student 1 Mathematics Chinese
Student 2 Mathematics Chinese
Class 2 teacher 1
Student 1 Mathematics Chinese
Student 2 Mathematics Chinese
failed:
Student 1 class 1 teacher 1 math language
Student 1 class 1 teacher 1 math language
There are also interfaces that are enrolled in student and grades
Used inner join on the state between multiple tables, and I showed very beautiful, rely, for an afternoon, I want to smoke, at the time :)
Look at me, four table investigations:
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 .....
-------------------------------------------------- -------------
demand:
Get the smallest unused ID number in the table.
example:
Table Name: Handle
Handleid
------------
1
2
5
6
Seduce
--5 Records
Executive result must be 3
solve:
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)
-------------------------------------------------- -------------
One data sheet on the two SQL servers is synchronized!
Drop Procedure dbsync
Go
/* data synchronization */
Create Procedure DBSync
@stabelname varchar (255), - to synchronize the table name
@skeyfield varchar (255), - Key Fields
@sserver varchar (255), - server name or IP
@SUSERNAME VARCHAR (255), - Log in to the server's username, generally sa @ spassword varchar (32) - user login to the server password
AS
/ * Delete temporary table * /
IF exists (select * from dbo.sysObjects where id = Object_id (n'Temptbl ') And ObjectProperty (ID, n'susertable') = 1)
Drop Table TempTBL
Declare @SQL VARCHAR (2000)
/ * Copy the table @stabelname [remote] to temporary table * /
Set @ SQL = 'SELECT * INTO TEMPTBL FROM'
Set @ SQL = @ SQL 'OpenDataSource ('
Set @ SQL = @ SQL '' 'SQLOLEDB.1' ','
Set @ SQL = @ SQL '' 'PERSIST Security Info = true; user ID =' @SUSERNAME
Set @ SQL = @ SQL '; Password =' @SPassword
Set @ SQL = @ SQL '; Initial Catalog = Toys; Data Source =' @SServer
Set @ SQL = @ SQL '') .toys.dbo. ' @ stabelname
EXEC (@SQL)
/ * Insert the data in the @stabelname [local] in the Temporary table * /
Set @ SQL = 'INSERT INTO TEMPTBL SELECT * FROM' @ stabelname 'Where [' @ SKEYFIELD '] NOT IN (SELECT [' @ SKEYFIELD '] from TempTBL)'
EXEC (@SQL)
/ * Clear Table @stabelname [Local] * /
Set @ SQL = 'Truncate Table' @ stabelname
EXEC (@SQL)
- get the column name
Declare @mysql varchar (2000)
Set @ mysql = ''
Declare @title varchar (20)
DECLARE TITLES_CURSOR CURSOR for
Select Name from syscolumns where id = Object_id (@stabelname)
Open titles_CURSOR
Fetch next from titles_cursor @title
While @@ fetch_status = 0begin
IF @title <> 'id'
Begin
IF @MYSQL = ''
Set @MYSQ
