mysql的on和in用法_数据库中in、on、with的用法及示例。

with用法：

创建一个表：

create table regr (pid integer,id integer, name char(20))

alter table regr alter id set not null add primary key(id)

insert into regr values(-1,1,'library'),(1,2,'news'),(2,3,'world news'),(2,4,'politics'),(2,5,'bussiness')

(2,6,'science'),(2,7,'technology'),(1,8,'sports'),(8,9,'local'),(8,10,'collegiate'),(8,11,'professional')

(9,12,'soccer'),(10,13,'soccer'),(11,14,'soccer'),(9,15,'football'),(10,16,'football'),(11,17,'football'

使用with得到数据树

WITH RPL (PID, ID, name) AS

(SELECT ROOT.PID, ROOT.ID, ROOT.NameFROM regr ROOT

WHERE ROOT.PID = 8

UNION ALL

SELECT CHILD.PID, CHILD.ID, CHILD.Name

FROM RPL PARENT, regr CHILD

WHERE PARENT.ID = CHILD.PID)

SELECT DISTINCT PID, ID, Name

FROM RPL

ORDER BY PID, ID, Name