I. How to delete a list of a field in a table, give an example table [Table1] ID Name1 aa2 bb3 cc1 aa2 bb3 cc I think the last table is the ID Name1 AA2 BB3 CC answer: Save record to temporary In the table #t, repeated records only save one, and then store the records in the temporary table #t back to the original table, pay attention to "Select Distinct ID, Class, Name" To include all the fields you need, otherwise some fields I was deleted. Perform the following code in the Query Manager: ----------------------------- Select Distinct ID, Nameinto #tfrom table1 delete table1 insert INTO TABLE1 SELECT * FROM #T ------------------------------ II. Find out the population of both VB and PHP This is this: ID staff skills 1 1 VB2 1 PHP3 1 ASP4 2 PHP5 3 ASP6 4 VB7 4 ASP To find out people who will be both VBs and PHP, how to write? Reply:------------------------------------------------ -------------------------------------------- SELECT staff from [table ] WHERE Employee In (SELECT Employee from [Table] WHERE Skill = 'VB') And Skill = 'PHP' -------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------ Third. Database merger problem access two tables, want to merge the contents of the two tables
Table [A] Structure is as follows: [ID] Name Auto Number [Name] Name Text [Price] Price Digital [Guige] Specification Text [changjia] Producer Text [baozhuang] Packaging text [danwei] unit text has a total of 900 records, except ID and Name fields, others can be empty
Table [B] The structure is as follows: [ID] Number Auto Number [Name] Name Text [Price] Price Number [CHANGJIA] Producer Text [DanWei] Unit Text [xingzhi] Nature Text has a total of 800 records, except for ID and Name fields, A few fields in the table [a], but more [xingzhi] characters can be empty
Now I want to generate a new table [C], the structure is as follows, and the content is the sum of the contents of the two tables, [ID] Name Auto number [Name] Name text [price] Price Digital [Guige] Specifications [changjia] Manufacturer Text [baozhuang] packaging text [danwei] unit text [xingzhi] nature text
It is also possible to use the SQL statement. Handmade is also good, XML is also good, don't worry, how to achieve it, the buddies are depressed, I really want to enter 800 records, I will hang, answer: 1. This - ---------------------------- INSERT INTO C (ID, Name, .....) Select ID, Name, ... ..from ainsert into c (id, name, .....) SELECT MAX (ID) 1, Name, ..... from b ------------------------------------------------------------------------------------------------------------------ -------------- 2. Corrected: If you do it directly in the query analyzer: ----------------------- -------- Insert Into C (Name, .....) Select Name, ..... from ainsert into c (name, .....) Select Name, ..... from B ------------------------------------------------ --------------------- Insert INTO [C] ([ID], number, automatic number) SELECT [ID], number, auto number from [A] UnionSelect [ID], number, automatic numbering from [b] ----------------------------------- 4.asp Workaround --------------------------------------------- ------------- <% 'cyclic detection a table set = server.createObect ("adoDb.recordset") ????? rs.open "SELECT * from a order by id" CONN, 1, 1 ????? do while not rs.eof ????????? Call ActAdd (RS ("Name") ??? 'Touch the function of adding content like B table! ????? rs.movenext ????? looprs.closeset = nothingsub ActAdd (txt) DIM TS, SQLSQL = "INTO B (Name) VALUES ('" & txt & ")" set ts = conn . EXECUTE (SQL) ????? Ts.closset Ts = Nothingend Sub%> ------------------------------- ----------------------------------- 5.ASP solution --------- -------------------------------------------------- ------------------------ <% DIM ARR_TEMP1, ARR_TEMP2, ARR_DATASET RS = Conn.execute ("SELECT ID, NAME, PRICE, GUIGE, CHANGJIA, Baozhuang, Danwei from a ") arr_temp1 = rs.getrowsrs.closset = Nothing
SET RS = Conn.execute ("Select ID, Name, Price, Guig, Changjia, Danwei, Xingzhi from B) Arr_temp2 = rs.getrowsrs.closset = Nothing
Rem starts processing Redim Arr_Data (Ubound (Arr_Temp1, 2) Ubound (Arr_Temp2, 2), 7) REM puts the content of the two arrays to copy this part to write two loops and then save database%> --- -------------------------------------------------- ---------------------------------- Finally turn some classic SQL statements: 1. Frog frog recommendation: some 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.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) --------------------------------- -------------------------------------------------- ---------------------------------- 2. Delete Data ----------- -------------------------------------------------- -------------------------------------------------- ------ One, the case with the primary key a. Field ID with uniqueness (for unique main key) Delete Table WHERE ID NOT IN (SELECT MAX (ID) from Table Group By Col1, Col2, Col3 .. .) The field followed by the group BY clause is the condition you used to determine the repetition. If only COL1 is only, as long as the content of the COL1 field is the same, the record is the same.
b. Have a joint primary key assumes col1 ',' col2 ',' ... col5 is the combined primary key Select * from table where col1 ',' col2 ',' ... col
5 in
(SELECT MAX (COL1 ',' Col2 ',' ... col5) from table where haVing count (*)> 1Group By Col1, col2, col3, col4) Group By clauses followed by the field is you used to judge repetition Conditions, such as only col1, as long as the content of the COL1 field is the same, the record is the same.
C: Judgment All Fields Select * Into #aa from table group by id1, id2, .... delete Table Insert Into Table Select * from #aa
Second, there is no main key
A: Using a Temporary Table Select Identity (Int, 1, 1) AS ID, * INTO #Temp from Tadelete #temp Where ID NOT IN (SELECT MAX (ID) From # group by col1, col2, col3 ... delete Table Tainset Into Ta (...) Select ..... from #Tem #
B: Use to change the table structure (add a unique field) to implement the ALTER TABLE Table Add Newfield Int Id IDENTITY (1) DELETE Table Where Newfield Not in (Select Min (Newfield) FROM Table GROUP BY In addition to NEWFIELD, all fields outside NewField)
Alter Table Table Drop Column Newfield
1.tblCustomer (customerID, DepartmentID, Salary), each with a SQL displays Salary plus the average of a Customer Salarydrop table tblCustomercreate table tblCustomer sector where the Customer (CustomerID varchar (10), DepartmentID varchar (10), Salary int Insert TblcuStomer SELECT '005', 'C003', 99 Union Allselect '001', 'C001', 45 Union AllSelect '002', 'C002', 120 Union AllSelect '003', 'C003', 55 Union AllSelect '004 ',' c001 ', 88
select * from tblCustomer order by CustomerIDSELECT DepartmentID, AVG (Salary) AS AvgSalary FROM tblCustomer GROUP BY DepartmentIDselect a.CustomerID, a.Salary, d.AvgSalary, a.Salary d.AvgSalary as TotalSalary from tblCustomer a inner join (SELECT DepartmentID, AVG (SALARY) AS AVGSALY from TBLCUSTOMER GROUP BY DepartmentID) AS D on A.DEPARTMENTID = D.DEPARTMENTID
2. Existing data sheet:
Month Number Score 01 A01 8001 A02 7501 A03 82
02 B01 8502 B02 8602 B03 92
Ask how to write a SQL statement statistics from the highest and lowest points in different months, get the following results:
Month Number Maximum Number Lowest 51 A03 82 A02 7502 B03 92 B01 85
I hope that the above functions can be implemented in the simple method. CREATE TABLE table (month VARCHAR (10), number VARCHAR (10), score int) INSERT Table Select '01', 'A01', 80 Union All Select '01', ' A02 ', 75UNION All SELECT' 01 ',' A03 ', 82UNION All Select' 02 ',' B01 ', 85UNION All Select' 02 ',' B02 ', 86Union All Select' 02 ',' B03 ', 92GO - Query (if you do not consider the problem with score repetition), you can use it directly: select a. Month, highest score = b. No., a. Maximum, minimum number = c. No .: Most = max (score), minimum = min (score) from form group BY month) a Join table B on a. Month = b. Month and a. Most points = b. Score JOIN Table C on a. Month = C. Month and a. Minimum = c. Score - If the score is the same, only one, then change the SELECT month, the highest number of numbers = (SELECT TOP 1 number from the WHERE month = a. Month and score = max (a. score)), the highest score = max (score), the lowest score number = (SELECT TOP 1 number "WHERE Month = a. Month and score = min (a. score), minimum = min (score) FROM table Agroup BY month GO - Delete Test DROP TABLE Table / * - Test Results Month Most Spot Most Points Minimum Score Number Minimum Division ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ----------- 01 A03 82 A02 7502 B03 92 B01 85 (2 lines affected by 2 lines ) Month highest score number The highest score minimum ----------------------- --------------- 01 A03 82 A02 7502 B03 92 B01 85 (The number of rows affects) - * / 3 transformation is row // Rows (IDEA1) SELECT ACCOUNTID, Count (*) AS Mailing from TBLENTITYMAILING WHERE AccentidID = '' group by AccountID Union All select AccountID, count (*) as Payment from tblBizPayment where AccountID = '' group by AccountID Union Allselect AccountID, count (*) as WorkOrder from tblBizBusinessVariation where AccountID = ''
