Programming | skills | data | database | How to delete a column in a table repeating columns Yes, for instance.
Table [table1]
ID name1 aa2 bb3 cc1 Aa2 cc
I think the last watch is like this.
ID Name 1 AA2 bb3 cc
Reply:
Save the record in a temporary table #t, save a duplicate record, and then save the record in the temporary table #t back into the original table, noting that "SELECT DISTINCT Id,class,name" contains all the fields you need, otherwise some fields are deleted.
Execute the following code in the query manager:
SELECT DISTINCT ID,, name
Into #t
From table1 DELETE table1
INSERT
into table1
SELECT *
From #t
Two. Find people who will both VB and PHP
The table is like this:
ID Employee Skills 1 1 VB2 1 PHP3 1 ASP4 2 PHP5 3 ASP6 4 VB7 4 ASP
From this table to find both VB and PHP, how to write SQL?
Reply:
Select employee from [table] where employee in (select employee from [table] where skill = ' VB ') and skill = ' PHP ' three. Database merge issues
Two tables in Access that want to merge the contents of two tables
The table [a] structure is as follows:
[ID] number autonumber [name] Name text [prices] price number [guige] specification text [Changjia] manufacturer's text [Baozhuang] Wrapping text [Danwei] unit text
A total of 900 records, except the ID and Name fields, all other can be empty
The table [b] structure is as follows:
[ID] number autonumber [name] Name text [prices] price number [Changjia] manufacturer's text [Danwei] unit text [Xingzhi] Nature text
A total of 800 records, in addition to the ID and name fields, less than table [a] a few fields, but also more than one [Xingzhi] of the word Fuan other can be empty
Now you want to generate a new table [C], which is structured as follows, and content is the sum of the contents of two tables.
[ID] number autonumber [name] Name text [prices] price number [guige] specification text [Changjia] manufacturer's text [Baozhuang] packing text [danwei] unit text [Xingzhi] Nature text
Use SQL statements can also, hand-operated or XML, regardless of how, how to achieve ah, the man to be depressed bad, really want us to enter 800 records, I hung up.
Reply:
1. This
Insert into C (id,name,.....)
Select Id,name,.....
From a
Insert into C (id,name,.....)
Select Max (ID) +1,name,.....
From b
2. Corrections:
If you are executing directly in Query Analyzer:
Insert into C (name,.....)
Select Name,.....
From a
Insert into C (name,.....)
Select Name,.....
From b
3. Using the Union method
insert into [c] ([ID], serial number, AutoNumber)
Select [ID], number, AutoNumber from [a]
Union
Select [ID], number, AutoNumber from [b]
4.asp Solution
<% ' cyclic detection table A
Set rs = Server.createobect ("ADODB.") RECORDSET ")
Rs.Open "SELECT * from ' A ORDER by id", conn,1,1
Do as not rs.eof
Call Actadd (RS ("name")) ' calls functions that add content like Table B!
Rs. MoveNext
Loop
Rs. Close
Set rs = Nothing
Sub actadd (TXT)
Dim ts, sql
sql = "INSERT INTO B" (name) VALUES (' & txt & ')
Set ts = conn.execute (SQL)
Ts. Close
Set ts = Nothing
End Sub
%>
5.asp Solution
<%
Dim arr_temp1,arr_temp2,arr_data
Set Rs=conn.execute ("Select Id,name,price,guige,changjia,baozhuang,danwei from a")
Arr_temp1=rs.getrows
Rs.close
Set rs=nothing
Set Rs=conn.execute ("Select Id,name,price,guige,changjia,danwei,xingzhi from B")
Arr_temp2=rs.getrows
Rs.close
Set rs=nothing
REM Start processing
ReDim Arr_data (UBound (arr_temp1,2) +ubound (arr_temp2,2), 7)
REM Copies the contents of the two arrays.
This part of myself wrote to do two loops
And then save it in the database.
%> finally turn some of the classic SQL statements:
1. Frog Frog Recommendation: Some subtle SQL statements
Description: Copy table (copy only structure, source table name: A new table name: b)
Sql:select * into B from a where 1 <> 1
Description: Copy table (copy data, source table name: A target table name: b)
Sql:insert into B (A, B, c) select d,e,f from B;
Description: Displays the article, the author, and the last reply time
Sql:select a.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b
Description: Outer join 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: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
Description: Two related tables, delete the information in the primary table that has not been in the secondary table
Sql:
Delete from info where NOT exists (Sele
