Common ASP database programming SQL skills

1. How to delete columns with repeated fields in a table? For example:
Table [table1]
Id name
1 aa
2 bb
3 cc
1 aa
2 bb
3 cc
I think the last table is like this.
Id name
1 aa
2 bb
3 cc
Answer:
Save the record to the temporary table # t, save only one record, and save the record in the temporary table # t back to the original table. Note: "select distinct id, class, "name" must contain all the fields you need. Otherwise, some fields will be deleted.
Run the following code in the query Manager:
Select distinct id, name
INTO # t
FROM table1 DELETE table1
INSERT
INTO table1
SELECT *
FROM # t
2. Find someone who can use both VB and PHP
The table is as follows:
ID employee skills
1 1 VB
2 1 PHP
3 1 ASP
4 2 PHP
5 3 ASP
6 4 VB
7 4 ASP
I want to find out from this table the person who knows both VB and PHP. How should I write SQL?
Answer:
SELECT employee FROM [Table] WHERE employee IN (SELECT employee FROM [Table] WHERE skill = 'vb ') AND skill = 'php'
Iii. database merging
To merge the contents of two tables in access
The structure of table [a] is as follows:
[Id] id: Automatic id
[Name] name text
[Price] price
[Guige] specification text
[Changjia] manufacturer text
[Baozhuang] packaging text
[Danwei] Unit text
A total of 900 records, except the id and name fields, can be empty
The structure of table [B] is as follows:
[Id] id: Automatic id
[Name] name text
[Price] price
[Changjia] manufacturer text
[Danwei] Unit text
[Xingzhi] Text
There are a total of 800 records. Apart from the id and name fields, there are several fewer fields than table [a], but there is also a [xingzhi] character, which can be left blank
Now we want to generate a new table [c] with the following structure and the content is the sum of the content of the two tables.
[Id] id: Automatic id
[Name] name text
[Price] price
[Guige] specification text
[Changjia] manufacturer text
[Baozhuang] packaging text
[Danwei] Unit text
[Xingzhi] Text
You can also use SQL statements, manual operations, or xml statements. Don't worry about it. How can we achieve it? You have to be depressed. Let's enter another 800 records, I am suspended.
Answer:
1.
Insert into c (id, name ,.....)
Select id, name ,.....
From
Insert into c (id, name ,.....)
Select max (id) + 1, name ,.....
From B
2. Correction:
If you execute the following directly in the query Analyzer:
Insert into c (name ,.....)
Select name ,.....
From
Insert into c (name ,.....)
Select name ,.....
From B
3. Use the union method
Insert into [c] ([id], number, automatic number)
Select [id], number, automatic number from [a]
Union
Select [id], number, automatic number from [B]
4. asp Solution
<% 'Loop detection Table
Set rs = Server. CreateObect ("ADODB. RECORDSET ")
Rs. open "select * from a order by id", conn, 1, 1
Do while not rs. eof
Call actAdd (rs ("name") 'calls the function that adds 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 rs1_conn.exe cute ("select id, name, price, guige, changjia, baozhuang, danwei from ")
Arr_temp1 = rs. getrows
Rs. close
Set rs = nothing
Set rs1_conn.exe cute ("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 content of the two arrays.
This part is written for two cycles.
And then save it to the database.
%>
Finally, convert some classic SQL statements:
1. Frog frog recommendation: some exquisite SQL statements
Note: copy a table (only copy structure, source table name: a new table name: B)
SQL: select * into B from a where 1 <> 1
Description: copy a 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, Submitter, and 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: External join query (table name 1: Table a 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
Note: Five minutes in advance of the schedule
SQL: select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 5
Note: Two associated tables are used to delete information that is already unavailable to the primary table in the secondary table.
SQL:
Delete from info where not exists (select * from infobz where info. infid = infobz. infid)
Note :--
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
Note :--
SQL:
Select * from studentinfo where not exists (select * from student where studentinfo. id = student. id) and Department name = '"& strdepartmentname &"' and major name = '"& strinclusionname &" 'order by gender, student source, total score of the College Entrance Examination
Note:
Go to the telephone fee statistics of each unit from the database for one year (two sources of the fixed telephone fee congratulation fertilizer List)
SQL:
SELECT a. userper, a. tel, a. standalone, TO_CHAR (a. telfeedate, 'yyyy') AS telyear,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '01', a. factration) as jan,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '02', a. factration) as fri,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '03', a. factration) as mar,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '04 ', a. factration) as apr,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '05 ', a. factration) as may,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '06', a. factration) as jue,
SUM (decode (TO_CHAR (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 (TO_CHAR (a. telfeedate, 'mm'), '10', a. factration) as oct,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '11', a. factration) as nov,
SUM (decode (TO_CHAR (a. telfeedate, 'mm'), '12', a. factration) AS DEC
FROM (SELECT a. userper, a. tel, a. standalone, B. telfeedate, B. factration
From telfeestand a, telstmb
WHERE a. tel = B. telfax)
Group by a. userper, a. tel, a. standalone, TO_CHAR (a. telfeedate, 'yyyy ')
Notes: Four-table join query problems:
SQL: select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where .....
Note: Obtain 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)
2. Delete duplicate data
I. Primary Key
A. Unique field id (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 for you to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same.
B. Having a federated primary key
Suppose col1 + ',' + col2 + ','... col5 is the Union primary key.
Select * from table where col1 + ',' + col2 + ','... col5 in (
Select max (col1 + ',' + col2 + ','... col5) from table
Where having count (*)> 1
Group by col1, col2, col3, col4
)
The field followed by the group by clause is the condition for you to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same.
C: determine all fields
Select * into # aa from table group by id1, id2 ,....
Delete table
Insert into table
Select * from # aa
2. No primary key
A: Use a temporary table.
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: It is implemented by changing the table structure (adding a unique field ).
Alter 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 drop column newfield