如何在单个查询中的单个行中显示单个字段的多个值？

Table 1:Domain Link Result
======================================================================
||Column1(words)     ||Column2(links)    ||Column3(frequency)       ||
======================================================================
    1                   1                   Any Number
    2                   1                   Any Number
    3                   1                   Any Number
    4                   1                   Any Number
    1                   2                   Any Number
    2                   2                   Any Number
    3                   2                   Any Number
    4                   2                   Any Number


Table 2:Sub Link Result
======================================================================
||Column1(words)     ||Column2(sublinks)    ||Column3(frequency)    ||
======================================================================
    1                   a                   Any Number
    2                   b                   Any Number
    3                   c                   Any Number
    4                   d                   Any Number
    1                   e                   Any Number
    2                   f                   Any Number
    3                   g                   Any Number
    4                   h                   Any Number

And so on.

In the above scenario user entered 4 words and 2 domain links. Now the frequency of 4 keywords is calculated on domain links as well sublinks and stored in separate tables as shown above. I want an aggregate result like below:

在上面的场景中,用户输入了4个单词和2个域链接。现在,4个关键字的频率在域链接和子链接上计算,并存储在单独的表中,如上所示。我想要一个如下的汇总结果:

Table 3:Final Result
==================================================================================
||Column1(words)     ||Column2(Domain links)    ||Column3(Total frequency)      ||
==================================================================================
Row1:    1              1                    Total of frequency in both tables
                        2                    for word "1"   
----------------------------------------------------------------------------------
Row2:    2              1                    Total of frequency in both tables
                        2                    for word "2"   
----------------------------------------------------------------------------------
Row3:    3              1                    Total of frequency in both tables
                        2                    for word "3"   
----------------------------------------------------------------------------------
Row4:    4              1                    Total of frequency in both tables
                        2                    for word "4"   
----------------------------------------------------------------------------------

I tried the following query in MySQL:

我在MySQL中尝试了以下查询:

SELECT t.`keyword`, t.`link` SUM( t.`frequency` ) AS total
FROM (

SELECT `frequency`
FROM `domain_link_result`
WHERE `keyword` = 'national'
UNION ALL
SELECT `frequency`
FROM `sub_link_result`
WHERE `keyword` = 'national'
)t GROUP BY `keyword`

But in Column 2 of the final result I get only first link instead of two links for row 1. How can I get both links or any number of links entered by user in a single row ?

但是在最终结果的第2列中,我只得到第一个链接,而不是第1行的两个链接。如何在一行中获得用户输入的链接或任意数量的链接?

Words and Links have VARCHAR as type and frequency has INT type.

单词和链接具有VARCHAR作为类型,并且频率具有INT类型。

1 个解决方案

#1

If you want to collapse several rows into one and still be able to see the information, you have to use GROUP_CONCAT

如果要将多行折叠为一行并仍然能够查看信息,则必须使用GROUP_CONCAT

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

This outputs the collapsed values separated by commas, i.e.: a string. In your programming language you can split this string if you need individual values.

这将输出以逗号分隔的折叠值,即:一个字符串。在您的编程语言中,如果需要单独的值,则可以拆分此字符串。

Your query would look somehow like this

您的查询看起来会像这样

SELECT keyword, GROUP_CONCAT(links), SUM(frequency)

FROM (subquery)

GROUP BY keyword

Which would output something like this:

哪个会输出这样的东西:

==================================================================================
||Column1(words)     ||Column2(Domain links)    ||Column3(Total frequency)      ||
==================================================================================
Row1:    1              1,2                     sum of freq.
----------------------------------------------------------------------------------
Row2:    2              1,2                     sum of freq.
----------------------------------------------------------------------------------
Row3:    3              1,2                     sum of freq.
----------------------------------------------------------------------------------
Row4:    4              1,2                     sum of freq.

EDIT: Extra help for your query

编辑:您的查询的额外帮助

Your query looks a little bit confusing to me. Try with a JOIN approach:

您的查询对我来说有点混乱。尝试使用JOIN方法:

SELECT domain_link_results.word AS word,
       GROUP_CONCAT(domain_link_results.links) AS domain_links,
       domain_link_results.frequency + sub_link_results.frequency AS total_frequency

FROM domain_link_results

INNER JOIN sub_link_results
        ON domain_link_results.word = sub_link_results.word

WHERE domain_link_results.word = "national"

GROUP BY domain_link_results.word

On the other hand, it might be better to have all the links in the same table, and an extra field to determine if it's a domain link or a sublink. Without knowing more about your system it is hard to say.

另一方面,最好在同一个表中包含所有链接,并使用额外的字段来确定它是域链接还是子链接。在不了解您的系统的情况下,很难说。

#1