ASP database programming SQL Common Skill _mssql

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
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 (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_char (sysdate, ' 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 does exists (SELECT * from student where studentinfo.id=student.id) and system name = ' "&strdepart mentname& "' and professional name = ' &strprofessionname& ' ORDER by sex, source of students, college entrance examination total score
Description
To go from the database to the unit telephone fee statistics for one year (telephone number quota, telegram, fertilizer list, two table sources)
Sql:
SELECT A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ') as Telyear,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as FRI,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as the MAR,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as APR,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as May,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as Jue,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' modified ', a.factration)) as June,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as AGU,
SUM (Decode (a.telfeedate, ' mm '), ' To_char ', a.factration)) as SEP,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ten ', a.factration)) as OCT,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' one ', a.factration)) as NOV,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a ', a.factration)) as DEC
From (SELECT a.userper, A.tel, A.standfee, B.telfeedate, b.factration
From Telfeestand A, Telfee b
WHERE A.tel = B.telfax) A
GROUP by A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ')
Note: Four table joint Inquiry question:
Sql:select * from a left inner join B in a.a=b.b right inner join C in 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 (Case when EXISTS (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 duplicate data
First, with the primary key situation
A. Unique field IDs (unique primary 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 that you use to determine the repetition, such as only col1, so as long as the content of the Col1 field is the same as that of the record.
B. With a joint primary key
Suppose col1+ ', ' +col2+ ', ' ... col5 for union primary key
SELECT * FROM table where col1+ ', ' +col2+ ', ' ... col5 in (
Select Max (col1+ ', ' +col2+ ', ' ... col5 ') from table
Where has count (*) >1
GROUP BY Col1,col2,col3,col4
)
The field followed by the GROUP BY clause is the condition that you use to determine the repetition, such as only col1, so as long as the content of the Col1 field is the same as that of the record.
C: Judge all the fields
SELECT * into #aa from table group by Id1,id2,....
Delete Table
INSERT INTO table
SELECT * FROM #aa
Second, there is no primary key situation
A: Use a temporary table to implement
Select Identity (int,1,1) as id,* into #temp from TA
Delete #temp
where ID not in
(
Select Max (ID) from # Group by col1,col2,col3 ...
)
Delete Table Ta
Inset into TA (...)
Select ... from #temp
B: Change the table structure (plus a unique field) to implement
ALTER TABLE table add Newfield int identity (1,1)
Delete Table
where Newfield not in
(
Select min (newfield) from table group by all fields except Newfield
)
ALTER TABLE table DROP column Newfield