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 (adddddate) addddde 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
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')
