mysql url查找_MySQL查询-使用URL名称来识别数据,其中数据...

如果您的DBMS支持递归查询,则可以这样做：

DROP SCHEMA tmp CASCADE;

CREATE SCHEMA tmp ;

CREATE TABLE tmp.webmeuk

( id INTEGER NOT NULL PRIMARY KEY

, slug VARCHAR

, content_type_id INTEGER NOT NULL

, parent_id INTEGER REFERENCES tmp.webmeuk(id)

);

INSERT INTO tmp.webmeuk( id , slug, content_type_id , parent_id )

VALUES( 0 , 'HTTP://pr0n.mysite.xx', 5 , NULL )

, ( 1 , 'portfolio', 5 , 0 )

, ( 2 , 'about-us', 1 , 0 )

, ( 3 , 'find-us', 1 , 0 )

, ( 4 , 'contact-us', 1 , 2 )

, ( 5 , 'find-us', 1 , 4 )

;

-- a room with a view

CREATE VIEW tmp.reteview AS (

WITH RECURSIVE xx AS (

SELECT w0.id AS id

, w0.slug AS slug

, w0.content_type_id AS content_type_id

, w0.slug AS fullpath

FROM tmp.webmeuk w0

WHERE w0.parent_id IS NULL

UNION

SELECT w1.id AS id

, w1.slug AS slug

, w1.content_type_id AS content_type_id

, xx.fullpath || '/'::text || w1.slug AS fullpath

FROM tmp.webmeuk w1, xx

WHERE w1.parent_id = xx.id

)

SELECT * FROM xx

);

SELECT * FROM tmp.reteview ;

-- Change one row of data

UPDATE tmp.webmeuk

SET slug = 'what-about-us'

WHERE id = 2;

SELECT * FROM tmp.reteview ;

输出：

NOTICE: drop cascades to 2 other objects

DETAIL: drop cascades to table tmp.webmeuk

drop cascades to view tmp.closure

DROP SCHEMA

CREATE SCHEMA

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "webmeuk_pkey" for table "webmeuk"

CREATE TABLE

INSERT 0 6

CREATE VIEW

id | slug | content_type_id | fullpath

----+-----------------------+-----------------+---------------------------------------------------

0 | HTTP://pr0n.mysite.xx | 5 | HTTP://pr0n.mysite.xx

1 | portfolio | 5 | HTTP://pr0n.mysite.xx/portfolio

2 | about-us | 1 | HTTP://pr0n.mysite.xx/about-us

3 | find-us | 1 | HTTP://pr0n.mysite.xx/find-us

4 | contact-us | 1 | HTTP://pr0n.mysite.xx/about-us/contact-us

5 | find-us | 1 | HTTP://pr0n.mysite.xx/about-us/contact-us/find-us

(6 rows)

UPDATE 1

id | slug | content_type_id | fullpath

----+-----------------------+-----------------+--------------------------------------------------------

0 | HTTP://pr0n.mysite.xx | 5 | HTTP://pr0n.mysite.xx

1 | portfolio | 5 | HTTP://pr0n.mysite.xx/portfolio

3 | find-us | 1 | HTTP://pr0n.mysite.xx/find-us

2 | what-about-us | 1 | HTTP://pr0n.mysite.xx/what-about-us

4 | contact-us | 1 | HTTP://pr0n.mysite.xx/what-about-us/contact-us

5 | find-us | 1 | HTTP://pr0n.mysite.xx/what-about-us/contact-us/find-us

(6 rows)