mysql - 连接五个表

Question

好吧，我遇到了一个问题。

我有大约 5 个数据库表。一个表有一个事件列表，另一个有一个广告组列表，另一个有一个广告列表，另一个有一个点击列表，另一个有一个潜在客户列表，另一个有一个潜在客户状态列表。每个表还有其他列。例如，在 clicks 中有一个 ad_id 和列 account，lead status 表有一个 lead id、buyer 和 amount。每个表都有一个列，其 ID 与另一个表 ID 相关联。

所以我正在尝试将我的数据转换成以下格式

campaign   keyword   count(keyword)   sum(amount)   buyer
bing-auto  rap        15                45           david
google-auto honda     10                30           chris

基本上，它是按关键字和广告系列分组的每个成功线索。

但是，我似乎无法解决这个问题。

SELECT keyword, COUNT(keyword) FROM in_clicks AS ic
       INNER JOIN ads AS a ON ic.ad_id = a.id
       INNER JOIN ad_groups AS ag ON a.ad_group_id = ag.id
       INNER JOIN campaigns AS c ON ag.campaign_id = c.id;
       INNER JOIN lead_status AS ls ON l.id = ls.id
       INNER JOIN leads AS l ON ic.in_click_id = l.id  
WHERE ic.create_date LIKE '%2011-08-19%' AND ic.location NOT LIKE '%Littleton%'
GROUP BY ic.keyword ORDER BY COUNT(ic.keyword) DESC

我知道这不是一个很好的解释，我的上述查询也不好，但我对如何进行有点困惑。谁能帮我指出正确的方向。

编辑:这是表架构。

CAMPAIGN
id   name   trafficsource   createdate
20   hip hop   bing           2001-08-16
21   rap       bing           2001-08-18

AD GROUPS
id    name    campaignid    createdate
15    jayz      20            2001-08-16  
16    eminem    21            2001-08-18

ADS
id   headline   adgroupid
13   cool is cool   15
14   wow, great     16

IN CLICKS
id   keyword    adid   createdate
205  his name    13      2001-08-16
206  whoa        14      2001-08-18

LEADS
id   in_click_id    create_date
300    205           2001-08-16   
301    206           2001-08-18

LEAD STATUS
id    lead_id    success   amount   buyer
501    300         0        5        jack
502    300         1        15       jill

Answer 1

是这样的吗？

SELECT 
  c.name as campaign
  , ic.keyword
  , count(ic.keyword) as keycount
  , sum(ls.amount) as total_amount
  , group_concat(ls.buyer) as buyers
FROM in_click ic 
INNER JOIN ads AS a ON (ic.ad_id = a.id)
INNER JOIN ad_groups AS ag ON (a.ad_group_id = ag.id)
INNER JOIN campaigns AS c ON (ag.campaign_id = c.id)
INNER JOIN lead_status AS ls ON (l.id = ls.id)
INNER JOIN leads AS l ON (ic.in_click_id = l.id)
WHERE ic.create_date BETWEEN 2011-08-01 and 2011-08-31
      AND ic.location NOT LIKE 'test%'
GROUP BY ic.keyword
ORDER BY keycount

备注
不要对日期字段使用 LIKE，请使用 BETWEEN startdate AND enddate。
如果您使用 LIKE %....，您将无法使用索引。
尝试使用 LIKE cghxgs%，这样您就可以使用索引。
如果您需要进行索引文本匹配，请使用具有 MATCH AGAINST 语法的全文索引。
然而，这仅适用于 MyISAM 表......

链接:
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between
http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html#function_match
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat