Learning Asp:sql Common Skills

sql| Technique I. 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	Employees	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	Automatic numbering
[Name]	Name	Text
[Price]	Price	Digital
[Guige]	Specifications	Text
[Changjia]	Production Factory	Text
[Baozhuang]	Packaging	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:

TD style= "BORDER:0;PADDING:0;" > AutoNumber

[id]	number
[name]	name	text
[price	price	number
[changjia]	manufacturer	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	Automatic numbering
[Name]	Name	Text
[Price]	Price	Digital
[Guige]	Specifications	Text
[Changjia]	Production Factory	Text
[Baozhuang]	Packaging	Text
[Danwei]	Unit	Text
[Xingzhi]	Properties	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