问题描述
我在 $ b
<$ c $的$页面表中有这些信息 c> page_id page_title
1 xy {a}
2 kp {a}
3 xyz {b}
4 mno {b}
5 abc {c}
6 mno {c}
7 qwe {c}
我使用大括号对页面进行分组，例如页面 xy 和 kp 属于 a 。
所以我想在这个信息中返回结果，
group_title page_title
a xy
a kp
b xyz
b mno
c abc
c mno
但每组最多只有两页。
我可以使用SQL查询来实现这个还是PHP？
这就是我使用REGEXP工作的内容... p>
SELECT
p.page_title REGEXP'{[az]}'AS groupTitle
FROM pages AS p
ORDER BY p.page_created DESC
我得到这个结果，
groupTitle
0
0
0
0
0
0
有什么想法？
编辑：
这是我的查询，
SELECT *
FROM
SELECT
p.page_id AS page_id，
p.page_url AS page_url，
SUBSTRING（p.page_title，LOCATE（'{'，p.page_title）， LOCATE（'}'，p.page_title））AS group_title，
p.page_created AS page_created，
@row_number：= @row_number + 1 AS row_number
FROM root_pages AS p
WHERE p.parent_id ='8'
AND p.page_id！='8'
AND p.page_hide！='1'
AND p.category_id ='1'
ORDER BY p.page_backdate DESC
）a
其中row_number
p>
page_id page_url group_title page_created row_number
44 abc {a} 2011-10-21 ... NULL
43 def {a} 2011-10-21 ... NULL
42 qwe {b} 2011-10-21 ... NULL
41 rty {b} 2011-10-21 ... NULL
40 tyu {c} 2011-10-21 ... NULL
39 ghj {c} 2011-10-21 ... NULL
59 sss {c} 2011-10-21。 .. NULL
但我仍然有问题限制每组的行数，任何想法？
解决方案在您的页面表中添加一个名为group_id（或类似名称）的列。
如果想要选择一个组或者 ORDER BY group_id ，则从数据库中选择 WHERE group_id = 以及按组排序。
编辑
如果你必须坚持这个概念，你可以将page_title分成相关的组件。我不会给你提供一个正在工作的查询和排序例程，而是让你开始：
$ page_title_separated = preg_split（ / [\ {\}] + /，$ page_title）;
其中 $ page_title 是来自你的db。
按照你的例子（第一行记录），这将导致
$ page_title_separated [0] => xy
$ page_title_separated [1] =>一个
好了，为了完整起见，让我们提一下， code> $ page_title_separated [2] ，这将是空的。此外，真正的标题，即 $ page_title_separated [0] 会以空格结尾。 我假设你知道如何在PHP中对数组进行排序，以及稍后如何处理这些数据。
有很多这样做的很好用的方法，但是 - 我无法强调这一点 - 光泽依然是第三列。
I have these information in a page table in my db,
page_id page_title
1 xy {a}
2 kp {a}
3 xyz {b}
4 mno {b}
5 abc {c}
6 mno {c}
7 qwe {c}
I use curly brackets to group pages, such as page xy and kp belong to the group of a.
So I want to return the result in this information,
group_title page_title
a xy
a kp
b xyz
b mno
c abc
c mno
But maximum of two pages in each group.
Can I use SQL query to achieve this or PHP?
This is what I am working on by using REGEXP...
SELECT
p.page_title REGEXP '{[a-z]}' AS groupTitle
FROM pages AS p
ORDER BY p.page_created DESC
I get this result,
groupTitle
0
0
0
0
0
0
Any ideas?
EDIT:
Here is my query,
SELECT *
FROM(
SELECT
p.page_id AS page_id,
p.page_url AS page_url,
SUBSTRING(p.page_title,LOCATE('{',p.page_title),LOCATE('}',p.page_title)) AS group_title,
p.page_created AS page_created,
@row_number := @row_number + 1 AS row_number
FROM root_pages AS p
WHERE p.parent_id = '8'
AND p.page_id != '8'
AND p.page_hide != '1'
AND p.category_id = '1'
ORDER BY p.page_backdate DESC
) a
where row_number <= 2
result,
page_id page_url group_title page_created row_number
44 abc {a} 2011-10-21... NULL
43 def {a} 2011-10-21... NULL
42 qwe {b} 2011-10-21... NULL
41 rty {b} 2011-10-21... NULL
40 tyu {c} 2011-10-21... NULL
39 ghj {c} 2011-10-21... NULL
59 sss {c} 2011-10-21... NULL
But I still have the problem to limit the number of row from each group, any ideas?
解决方案 Add a column called group_id (or the like) to your page table.
Select from the db with WHERE group_id = if wanting to select one group or ORDER BY group_id to, well, order by group.
EDIT
If you must stick to this concept, you could split the page_title into relevant components. I'm not going present you a working query and sorting routine, but to get you started:
$page_title_separated = preg_split("/[\{\}]+/", $page_title);
where $page_title is a page title from your db.
Following your example (1st row entry), this would result in
$page_title_separated[0] => xy
$page_title_separated[1] => a
well, and for the sake of completeness, let it be mentioned that there'd be a $page_title_separated[2], which would be empty. Also, the real title, i.e. $page_title_separated[0] would end in a whitespace.
I assume you know how to sort arrays in php and what to do with this data later on.
There's a multitude of slicker ways of doing this, but - I can't stress this enough - the slickest remains a third column.
这篇关于从一列中分组页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！
09-05 10:10