Tips | data | Database Frog Frog recommended: Organize some SQL database skills, paste
How to delete a column in a table that repeats, for example
Table [table1]
ID Name
1 AA
2 BB
3 cc
1 AA
2 BB
3 cc
I think the last watch is like this.
ID Name
1 AA
2 BB
3 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 VB
2 1 PHP
3 1 ASP
4 2 PHP
5 3 ASP
6 4 VB
7 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 Consolidation 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 of the 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] Wrapping text
[Danwei] Unit text
[Xingzhi] nature of the 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 to some 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
