mySQL>>将HREF链接添加到不同的GROUP_CONCAT [英] mySQL >> Adding HREF links to a Distinct GROUP_CONCAT

问题描述

我有以下查询:

SELECT *, RES.res_id 'ID', RES.RES_Title 'Title', 
GROUP_CONCAT(DISTINCT T.T_Name separator ', ') 'Topics', 
GROUP_CONCAT(DISTINCT CH.CH_Code separator ', ') 'Chapters' 
FROM Resources RES 
LEFT JOIN topic_to_resource RT ON RT.RT_ResourceID = RES.RES_ID 
LEFT JOIN topics T on T.T_ID = RT.RT_TopicID 
LEFT JOIN topic_to_chapter TCH on TCH.TCH_TopicID = T.T_ID 
LEFT JOIN chapters CH ON CH.CH_ID = TCH.TCH_FrameworkID 
WHERE RES_Status = 'Active' 
GROUP BY RES.RES_ID 
ORDER BY RES_Title ASC, RES_Source DESC LIMIT 0, 10

对于每个GROUP_CONCAT(主题和章节)，我需要将列表转换为链接，同时保持区别.

For each of the GROUP_CONCATs (Topics and Chapters), I need to convert the list into links while keeping the Distinct.

例如，代替上面查询的主题"输出:体育，时事，政治，气候等.

For example, instead of the Topics output of the query above: Sports, Current Events, Politics, Climate, etc.

我需要生成:

<a href="page.asp?topic=Sports" title="Sports">Sports</a>, 
<a href="page.asp?topic=Current%20Events" title="Current Events">Current Events</a>, 
<a href="page.asp?topic=Politics" title="Politics">Politics</a>, 
<a href="page.asp?topic=Climate" title="Climate">Climate</a>

我可以通过在GROUP_CONCAT内嵌套一个CONCAT来实现链接，但随后我失去了与众不同的优势.另外，我需要查询以按资源ID(RES.RES_ID)分组.

I can achieve the links by nesting a CONCAT within the GROUP_CONCAT, but then I lose the Distinct. Also, I need the query to stay grouped by the Resource ID (RES.RES_ID).

关于如何完成不同主题和章节的链接列表的任何想法吗?

Any ideas on how to accomplish a linked list of distinct topics and chapters?

非常感谢！

推荐答案

接着是

Following on from this question, and using that query as the basis for the following example, you can do this with the following:

SELECT 
CONCAT(res_id,': ',res_name) 'Resources', 
GROUP_CONCAT(distinct t_name order by t_id separator ',') 'Topics', 
GROUP_CONCAT(distinct ch_name order by ch_id separator ',') 'Chapters'
FROM (SELECT res_id,
      res_name,
      t_id,
      t_name,
      ch_id, 
      CONCAT("<a href=\"page.asp?topic=",ch_name,"\" title=\"",ch_name,"\">",ch_name,"</a>") as ch_name 
             FROM resources r
             JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
             JOIN topics t on t.t_id = ttr.tr_tid
             JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
             JOIN chapters ch ON ch.ch_id = tch_chid) links
      GROUP BY res_id
      ORDER BY res_id, t_id, ch_id;

基本上，我已经将源数据包装在一个单独的(子)查询中，构建了链接，然后在此之外执行了GROUP_CONCAT.

Basically I have wrapped the source data in a separate (sub) query, building the links, then performing the GROUP_CONCATs outside of this.

这将产生:

<a href="page.asp?topic=CHAPTER #1" title="CHAPTER #1">CHAPTER #1</a>,
<a href="page.asp?topic=CHAPTER #2" title="CHAPTER #2">CHAPTER #2</a>,
<a href="page.asp?topic=CHAPTER #3" title="CHAPTER #3">CHAPTER #3</a>

有关更多详细信息，请参见此小提琴

See this fiddle for more detail

这篇关于mySQL>>将HREF链接添加到不同的GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！