从一列中分组页面 [英] Group pages from one column

问题描述

我在 $ 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 <= 2 
 
 
 
 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.

                        
这篇关于从一列中分组页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！