| id | name |
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 1 | aa |
| 2 | bb |
| 3 | cc |
| id | name |
| 1 | aa |
| 2 | bb |
| 3 | cc |
| SELECT DISTINCT id,, name INTO #t FROM table1 DELETE table1 INSERT INTO table1 SELECT * FROM #t |
| ID | Staff | Skills |
| 1 | 1 | VB |
| 2 | 1 | PHP |
| 3 | 1 | ASP |
| 4 | 2 | PHP |
| 5 | 3 | ASP |
| 6 | 4 | VB |
| 7 | 4 | ASP |
| SELECT employee FROM [Table] WHERE employee IN(SELECT employee FROM [Table] WHERE skill='VB' ) AND skill='PHP' |
3. Database merger issues
There are two tables in access. I want to merge the contents of the two tables.
Table [a] has the following structure:
| [id] | number | automatic number |
| [name] | name | text |
| [price] | price | number |
| [guige] | specification | text |
| [changjia] | manufacturer | text |
| [baozhuang] | packaging | text |
| [danwei] | unit | text |
There are 900 records in total. Except for the id and name fields, all others can be empty.
Table [b] has the following structure:
| [id] | number | automatic number |
| [name] | name | text |
| [price] | price | number |
| [changjia] | manufacturer | text |
| [danwei] | unit | text |
| [xingzhi] | property | text |
There are 800 records in total. Except for the id and name fields, there are a few fields less than table [a], but there is one more [xingzhi] character. All others can be empty.
Now I want to generate a new table [c] with the following structure, and the content is the sum of the contents of the two tables.
| [id] | number | automatic number |
| [name] | name | text |
| [price] | price | number |
| [guige] | specification | text |
| [changjia] | manufacturer | text |
| [baozhuang] | packaging | text |
| [danwei] | unit | text |
| [xingzhi] | nature | text |
You can use sql statements, manual operations, or xml. Don't worry about how to implement it. I'm going to be very depressed. If we really have to input 800 records, I will die.
answer:
1. This way
| insert into c(id,name,.....) select id,name,..... from a insert into c(id,name,.....) select max(id)+1,name,..... from b |
2.Correction:
If executed directly in Query Analyzer:
| insert into c(name,....) select name,..... from a insert into c(name,....) select name,..... from b |
3. Use 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 of table a Set rs = Server.CreateObject("ADODB.RECORDSET") rs.open "select * from an order by id",conn,1,1 Do while not rs.eof Call actAdd(rs("name")) 'Call a function like adding content to table b! rs.MoveNext Loop rs.Close Setrs=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 starts processing redim arr_data(ubound(arr_temp1,2)+ubound(arr_temp2,2),7) rem copies the contents of the two arrays in. I wrote this part myself, made two loops, and then stored it in the database. %> |
Finally convert some classic SQL statements:
1. Recommended by Wawa: Some exquisite SQL statements
Description: Copy the table (only copy the 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: Display 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: Outer join query (table name 1: a table name 2: b)
SQL: select aa, ab, ac, bc, bd, bf from a LEFT OUT JOIN b ON aa = bc
Description: Schedule reminder five minutes in advance
SQL: select * from schedule where datediff('minute',f start time,getdate())>5
Description: Two related tables, delete the information in the main table that is not in the secondary table
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
illustrate:--
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
illustrate:--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and department name='"&strdepartmentname&"' and major name='"&strprofessionname&"' order by gender, student place of origin, total college entrance examination score
illustrate:
Get the phone bill statistics of each unit for one year from the database (telephone bill quota, congratulations, telegram and fertilizer list, two tables source)
SQL:
SELECT a.userper, a.tel, a.standfee, 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.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')
Description: Four-table joint query problem:
SQL: select * from a left inner join b on aa=bb right inner join c on aa=cc inner join d on aa=dd 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. Remove duplicate data
1. The case of having a primary key
a. Unique field id (unique primary key)
delete table
where id is not in
(
select max(id) from table group by col1,col2,col3...
)
The field following the group by clause is the condition you use to determine duplication. For example, if there is only col1, then as long as the contents of the col1 field are the same, it means that the records are the same.
b.Have a joint primary key
Assume col1+','+col2+','...col5 is the joint 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 following the group by clause is the condition you use to determine duplication. For example, if there is only col1, then as long as the contents of the col1 field are the same, it means that the records are the same.
c: Judge all fields
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa
2. Situation without primary key
a: Implemented using temporary tables
select identity(int,1,1) as id,* into #temp from ta
delete #temp
where id is not in
(
select max(id) from # group by col1,col2,col3...
)
delete table ta
inset into ta(...)
select ..... from #temp
b: 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