Question

我想加入这两个表格,但第二张表格的正文中正出现争执。

page_id     url
1           a
2           c
3           d

。表格

system_id       query
1               page_id=1&content=on&image=on
2               type=post&page_id=2&content=on

您可以看到,<编码>网页_id是querystring in system table。

因此,我如何与他们一道采用以下标准加入表格方法?

SELECT*
FROM page AS p

LEFT JOIN system AS s
ON p.page_id = s.page_id

<>光线>

页: 1 引文如下:

system_id       page_id    query
1               1           page_id=1&content=on&image=on
2               2           type=post&page_id=2&content=on
3               NULL        type=page

但我不希望这样做的原因是,<编码>网页_id不需要很多记录。我不想打一栏,上面有太多的<代码>null。

Answer 1

我猜想你们想要的是这样的东西(MSSQL!):

DECLARE @query VARCHAR(50)
DECLARE @Lenght INT  
DECLARE @PageID INT 

SET @query =  4kkhknmnkpage_id=231&content=on&image=on 
SET @Lenght = PATINDEX( %&% , substring(@query,PATINDEX( %page_id=% , @query),50)) - 9
SET @PageID = CAST(SUBSTRING(@query,PATINDEX( %page_id=% , @query) + 8,@Lenght) AS INT)

SELECT @PageID -- you can do as you please now :)

OR:

SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTRING(query,PATINDEX( %page_id=% , query) + 8,(PATINDEX( %&% , substring(query,PATINDEX( %page_id=% , query),50)) - 9)) AS INT) AS page_id
                FROM system) AS s
ON p.page_id = s.page_id 

-- Do as you please again :)

我猜想你真心想要的东西(MYSQL!):

SET @query :=  4kkhknmnkpage_id=231&content=on&image=on ;
SET @Lenght := POSITION( &  IN (SUBSTR(@query,POSITION( page_id=  IN @query),50))) - 9;
SET @PageID := CAST(SUBSTR(@query,POSITION( page_id=  IN @query) + 8,@Lenght) AS  SIGNED );

SELECT @PageID

www.un.org/Depts/DGACM/index_french.htm

SELECT*
FROM page AS p
LEFT JOIN (SELECT CAST(SUBSTR(query,POSITION( page_id=  IN query) + 8,(POSITION( &  IN (SUBSTR(query,POSITION( page_id=  IN query),50))) - 9)) AS  SIGNED) AS pageID
           FROM system) AS s
ON p.page_id = s.pageID

Answer 2

我肯定会设立以下栏目:page_id ,,image和 字(并编制索引)。然后,数据库将更加简便,并将更快地开展工作。

Answer 3

Joining two tables without the common field and data type, is fundamentally wrong IMO.

我建议你摘录<代码>_id,并在数据库中插入该编码,并使用正常的加入来实现你所寻求的东西。

like

+------------+-----------+---------+
| system_id  |  page_id  |  query  |
------------------------------------

这里是一刀子,请上页。

$query =  page_id=1&content=on&image=on ;
$queryParts = explode( & , $query);

$params = array();
foreach ($queryParts as $param) {
    $item = explode( = , $param);
    $params[$item[0]] = $item[1];
} 
$page_id = $parems[ page_id ];

然后,你可以加入并使用简单的加入书,以适当方式解决问题。

<>Update:

由于你能够把该表变成一个可行的计划。你们坚决需要担心一些有空row的行.。

Joining two tables without the common field and data type, is fundamentally wrong IMO.

友情链接