无法在连接上使用 GROUP_CONCAT 连接值

发布于 2024-12-12 02:03:55 字数 686 浏览 0 评论 0原文

有人可以帮助我吗？

通过此查询，我获取了 id，但当subscriber_data.fieldid 为空时，它不会放置分隔符。例如，当 4 的值为 null 时，不是 2,,12，而是 2,12...

我认为问题出在与子查询的连接上，但我无法在主查询中使用两个左连接来实现它另外...

这是我使用的查询：

SELECT 
    list_subscribers.emailaddress, 
    (SELECT 
         GROUP_CONCAT(IFNULL(customfields.fieldid,'') SEPARATOR '","') 
     FROM customfields 
     LEFT JOIN subscribers_data 
         ON subscribers_data.fieldid = customfields.fieldid 
     WHERE 
         customfields.fieldid IN (2,4,12,13,14,15,17,19,20,21,22,23,16,26,27) 
             AND 
         list_subscribers.subscriberid = subscribers_data.subscriberid
    ) AS data FROM list_subscribers

谢谢大家。

原文

Could anybody help me?

With this query I am getting the ids, but it is not putting the separators when subscriber_data.fieldid is null. For example instead of coming 2,,12 it comes 2,12 when the value for 4 is null...

I think the problem is on the Join with subquery, but i couldn't make it with two left joins in the main query also...

This is the query im using:

SELECT 
    list_subscribers.emailaddress, 
    (SELECT 
         GROUP_CONCAT(IFNULL(customfields.fieldid,'') SEPARATOR '","') 
     FROM customfields 
     LEFT JOIN subscribers_data 
         ON subscribers_data.fieldid = customfields.fieldid 
     WHERE 
         customfields.fieldid IN (2,4,12,13,14,15,17,19,20,21,22,23,16,26,27) 
             AND 
         list_subscribers.subscriberid = subscribers_data.subscriberid
    ) AS data FROM list_subscribers

Thanks everyone.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

掩耳倾听 2024-12-19 02:03:55

左连接是没有用的。因为 WHERE 子句中的subscriber_data 有一个条件，所以该子查询不会返回那些没有匹配的subscriber_data 的行，因此它的工作方式就像使用INNER JOIN 一样。您应该将该条件添加到左连接条件中，但在此查询布局中这是不可能的。内部查询的连接条件中不允许使用外部查询的值。

您可以更改它，但显然您需要连接三个表，其中中间表，subscriber_data，将它们链接在一起，是可选的。这确实没有意义。

或者，customfields 可能是可选的表，但在这种情况下，您应该反转该表或使用 RIGHT JOIN。

总之，我认为您的意思是这样写：

select
  s.emailaddress,
  GROUPCONCAT(IFNULL(f.fieldid, '') SEPARATOR '","')
from
  list_subscribers s
  inner join subscribers_data d on d.subscriberid = s.subscriberid
  left join customfields f on f.fieldid = d.fieldid
where
  d.fieldid in (2,4,12,13,14,15,17,19,20,21,22,23,16,26,27)
group by
  s.emailaddress

或者您想列出为订阅者填写的字段的 id 吗？在这种情况下，它将是：

select
  s.emailaddress,
  GROUPCONCAT(IFNULL(d.fieldid, '') SEPARATOR '","')
from
  list_subscribers s
  cross join customfields f
  left join subscribers_data d on 
    d.subscriberid = s.subscriberid and
    d.fieldid = f.fieldid
where
  f.fieldid in (2,4,12,13,14,15,17,19,20,21,22,23,16,26,27)
group by
  s.emailaddress

The left join is useless. Because you have a condition on subscriber_data in the WHERE clause, that subquery will not return those rows for which there is no matching subscriber_data, so it effectively works as if you used INNER JOIN. You should add that condition to the left join condition, but it is impossible in this query layout. Values from the outer query are not allowed in join conditions in the inner query.

You could change it, but apparently you need to join three tables, where the middle table, subscriber_data, that links them all together, is optional. That doesn't really make sense.

Or maybe customfields is the table that is optional, but in that case, you should have reversed the table or used a RIGHT JOIN.

In conclusion, I think you meant to write this:

select
  s.emailaddress,
  GROUPCONCAT(IFNULL(f.fieldid, '') SEPARATOR '","')
from
  list_subscribers s
  inner join subscribers_data d on d.subscriberid = s.subscriberid
  left join customfields f on f.fieldid = d.fieldid
where
  d.fieldid in (2,4,12,13,14,15,17,19,20,21,22,23,16,26,27)
group by
  s.emailaddress

Or do you want to list the id's of the fields that are filled for the subscriber(s)? In that case, it would be:

select
  s.emailaddress,
  GROUPCONCAT(IFNULL(d.fieldid, '') SEPARATOR '","')
from
  list_subscribers s
  cross join customfields f
  left join subscribers_data d on 
    d.subscriberid = s.subscriberid and
    d.fieldid = f.fieldid
where
  f.fieldid in (2,4,12,13,14,15,17,19,20,21,22,23,16,26,27)
group by
  s.emailaddress

回复收藏 0 原文

~没有更多了~