Frog frog recommendation: I sorted out some SQL database skills and posted them.

Frog frog recommendation: I sorted out some SQL database skills and posted them.

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:
Bytes ---------------------------------------------------------------------------------------------
Select employee from [Table] Where employee in (select employee from [Table] Where skill = 'vb ') and skill = 'php'
Bytes ----------------------------------------------------------------------------------------------
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 I 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 got down,
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
Bytes -----------------------------------------------------------------------------------
<%
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.
%>
Bytes ---------------------------------------------------------------------------------------
Finally, convert some classic SQL statements:
1. Frog frog recommendation: some exquisite SQL statements
Bytes -----------------------------------------------------------------------------------------------------------------------
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') & brvbar; '/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)
Bytes -----------------------------------------------------------------------------------------------------------------------
2. Delete duplicate data
Bytes -----------------------------------------------------------------------------------------------------------------------
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, such as only col1,
If the content of the col1 field 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,
If only col1 is available, the records are the same as long as the content of the col1 field 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 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
Bytes -----------------------------------------------------------------------------------------------------------------------