I have organized some SQL database skills, laid a next. 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 this ID Name1 aa2 BB3 CC Answer: Save the record to the temporary table #t, repeat the record only one, then save the record in the temporary table #t back to the original table, pay attention to "SELECT DISTINCT ID, CLASS, NAME" to include you All fields need, otherwise some fields are 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 Merge Problem Access two tables, want to consolidate the contents of the two tables [A] structure as follows: [ID] Number Automatic 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, in addition to ID and Name fields, other can be empty table [ B] The structure is as follows: [ID] Name Auto Number [Name] Name Text [Price] Price Digital [changjia] Producer Text [Danwei] Unit text [xingzhi] Nature text has a total of 800 records, except for ID and Name fields, specifications [A] There are few fields, but more [xingzhi] characters can be empty now 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] Number Auto Number [Name] Name Text [Price] Price Number [Guige] Specifications [Changjia] Manufacturer Text [Baozhuang] Packaging Text [danwei] Unit Text [xingzhi] Nature text can be used with SQL statements, handmade, XML is also good, don't worry, how to achieve it, the buddies will be depressed, really let us enter 8 The 00 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. Correct: If you do it directly in the query analyzer:
------------------------------ INSERT INTO C (Name, .....) Select Name, .... .from ainsert into c (name, .....) SELECT NAME, ..... from b -------------------------- ------ 3. With UNION Method -------------------------------- Insert Into [C] ( [ID], number, automatic number) SELECT [ID], number, automatic number from [A] unionselect [id], number, automatic numbering from [b] --------------- -------------------- 4.ASP solution ------------------------ ----------------------------------- <% 'cycle 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 actd (RS ("name") 'calling a 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 RS = nothingset rs = conn.execute ( "select id, name, price, guige, changjia, danwei, xingzhi from b") arr_temp2 = rs.getrowsrs.closeset rs = nothingrem start process redim arr_data (ubound (arr_temp1,2) ubound (Arr_temp2, 2), 7) REM puts the content of the two arrays to write this part to write two loops and then save database%> ----------------- -------------------------------------------------- -------------------- Final turn some classic SQL statements: 1. Frog frog recommendation: some exquisite SQL statements ----------- -------------------------------------------------- -------------------------------------------------- -------- Description: Replication table (only copy structure, source table 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) adddddddate from table where table.title = a. Title) B Description: Outer connection query (table name 1: a table name 2: b) SQL: SELECT AA, AB, AC, BC, BD, BF from a left out join b on aa = BC Description: Schedule advance five Minutes reminder SQL: SELECT * FROM schedule Arranging WHERE DATEDIFF ('Minute', F Start Time, getDate ())> 5 Description: Two related tables, delete information 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_CH Ar (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) bwhere a.num = B.NUM Description: - SQL: Select * from studentInfo where not exists (SELECT * from Student Where StudentInfo. ID = student.id) AND system name = '"& strdEpartmentname &"' and professional name = '"& strprofessional"' ORDER BY Gender, Source, college entrance examination summary Description: From the database to the annual telephone fee statistics (telephone Fixing congratulatory fertilizer list two menu sources)
SQL: SELECT A. IUSERPER, 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) AS fri, sum (decode (to_char (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 (to_CHAR (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 (to_CHAR A.TelFeedate, 'mm'), '09', a.factration) AS SEP, SUM (Decode (a.telfeedate, 'mm'), '10', a.factration) AS OCT, SUM Decode (TO_CHAR (A.TELFEEDATE, 'MM'), '11', a.factration) AS NOV, SUM (Decode (a.telfeedate, 'mm'), '12', a.factration) AS Decfrom (SELECT A.USERPER, A.TEL, A.Standfee, B.Telfeedate, B.Factration from Telfeestand A, Telfee B Where a.tel = b.telfax) Agroup by a.userper, a.tel, a.standfee, to_char (a.telfeedate, 'yyyy') Description: Four Table Outasures: SQL: Select * from a left inner Join B on aa = bb Right Inner Join C on aa = cc inner Join D on aa = DD where ..... Description: Get the smallest table Unused ID Number SQL: Select (Select * from Handle B Where B.Handleid = 1) Then Min (HandleId)
