在MySQL中使用GROUP_CONCAT进行CONCAT [英] CONCAT with GROUP_CONCAT in mysql

查看:158
本文介绍了在MySQL中使用GROUP_CONCAT进行CONCAT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

因此,我到处都在寻找答案,但是到目前为止还没有运气.任何帮助深表感谢.我是mysql的新手(从filemaker专业背景传输).

So I have looked everywhere for an answer to this, but so far no luck. Any help is much appreciated. I am new to mysql (transferring from a filemaker pro background).

我有一个数据库,用于保存我们业务前景的信息.我有一个有关列出信息供我们用户查看的问题. INQUIRY表保存每个查询的信息(例如查询日期,查询状态等). PROSPECT表保存有关每个潜在客户(潜在客户)的信息(即名字,姓氏,年龄等).第三个表格,INQUIRYNOTES ,用于保存我们与潜在客户的联系信息(即下次联系日期,备注,下一步操作等).这些表中的每一个都与键 inquiryID 相关.

I have a database where I keep information for prospects for our business. I have a question about listing information for our users to see. The INQUIRY table holds the information for each inquiry (i.e. date inquiry came in, status of the inquiry, etc.). The PROSPECT table holds the information on each prospect (potential client) (i.e. first name, last name, age, etc.). A third table, INQUIRYNOTES, hold information on our contacts with the prospect (i.e. next contact date, notes, next action, etc.). Each of these tables are related with the key inquiryID.

每天,用户将来到一个屏幕,在该屏幕上向用户显示他/她将在当天进行跟进的线索/查询,即那些行动日期小于或等于今天的查询.我希望列表包含查询ID ,与该查询ID相关的所有潜在客户的列出的名称(最多2个)和操作日期.

Each day a user will come to a screen where they will be presented with the leads/inquiries that he/she will follow up on for that day which are those inquiries who's action date is less than or equal to today. I want the list to contain the inquiryID, listed names of all prospects related to that inquiryID (max 2), and the action date.

这是我到目前为止尝试过的:

Here is what I've tried so far:

SELECT inquiry.inquiryID,
   CASE WHEN prospect.lastName = '' THEN prospect.firstName
   WHEN prospect.firstName = '' THEN prospect.lastName
   ELSE CONCAT(prospect.lastName, ', ',prospect.firstName) END as fullName, inquiryNote.actionDate
FROM inquiry, prospect, inquiryNote
WHERE inquiry.inquiryID = prospect.inquiryID
AND inquiry.inquiryID = inquirynote.inquiryID
AND inquirynote.actionDate != \"0000-00-00\"
AND actionDate <= \"".date("Y-m-d")."\"
GROUP BY inquiryID ORDER BY actionDate";

这将返回我正在寻找的信息,但它仅列出潜在客户之一(即使有2个要查询的人).

This returns the information that I am looking for, but it only lists one of the prospect (even if there are 2 for the inquiry).

接下来,我尝试将GROUP_CONCAT(firstname)放入其中,这给了我一份潜在客户的名字列表,并以逗号分隔(我越来越近了).然后,我尝试了GROUP_CONCAT(姓氏,,",名字),然后尝试了GROUP_CONCAT(姓氏,姓氏),但是它再次只列出了一个潜在客户,而不是多个以逗号分隔的潜在客户.

Next I tried putting the GROUP_CONCAT(firstname) in this and it gave me a list of the first names of the prospect listed in a row separated by commas (I'm getting closer). Then I tried GROUP_CONCAT(last name, ', ', firstname) and then GROUP_CONCAT(lastname,firstname), but again it would only list one prospect, not the multiple prospects separated by commas.

我希望在上面的代码中将我命名为 fullName 的变量包含在GROUP_CONCAT中,以便在返回的行中看到与以下内容相关的所有潜在客户的列表列出并用逗号分隔的查询.

I want the variable that I named fullName in the code above to be included in the GROUP_CONCAT so that in the rows returned I see a list of all prospect related to an inquiry listed and divided by a comma.

我希望在我创建的网页中的表格包含以下列:

I want the table in the webpage I have created to have these columns:

inquiryID | 潜在客户名称(姓氏在先) | 下一个操作日期

inquiryID | prospects name(s) (last name first) | Next Action Date

到目前为止,我似乎只能得到:

Again so far I can only seem to get:

inquiryID | 1个潜在客户名称 | 下一个操作日期

inquiryID | 1 prospect name | Next Action Date

解决方案可能很简单,但是由于我是mysql的新手,所以我迷路了.

The solution could be something easy, but since I am new to mysql I am lost.

如果您需要更多详细信息,请告诉我.

Please let me know if you need more detail.

使用解决方案进行更新,但最终会出现类似的问题

UPDATE WITH SOLUTION, BUT SIMILAR QUESTION AT THE END

问题在于某些名字和姓氏字段具有NULL值.显然,GROUP_CONCAT现在将使用空值.这是我结束所有NULL值后就可以使用的查询:

The problem was that some of the first name and last name fields had NULL values. Apparently, GROUP_CONCAT will now work with null values. This is the query that I ended with that worked once I got rid of all of the NULL values:

SELECT 
  inquiry.inquiryID, 
  inquiry.initialDate,
  inquiryNote.actionDate,
  GROUP_CONCAT(DISTINCT
    CONCAT(
      CASE WHEN prospect.lastName = '' THEN prospect.firstName
      WHEN prospect.firstName = '' THEN prospect.lastName
       ELSE CONCAT(
        prospect.firstName, ' ',prospect.lastName
      ) END
    ) SEPARATOR ', '
  ) AS prospectList,

GROUP_CONCAT(DISTINCT
    CONCAT(
      CASE WHEN influencer.lastName = '' THEN influencer.firstName
      WHEN influencer.firstName = '' THEN influencer.lastName
      ELSE CONCAT(
        influencer.firstName, ' ',influencer.lastName
      ) END
    ) SEPARATOR ', '
  ) AS influencerList

FROM inquiry, prospect, inquiryNote, influencer WHERE inquiry.inquiryID = prospect.inquiryID
AND inquiry.inquiryID = inquirynote.inquiryID
AND inquiry.inquiryID = influencer.inquiryID
AND inquirynote.actionDate != "0000-00-00" AND actionDate <= "2011-09-22"
GROUP BY inquiryID
ORDER BY actionDate;

所以最后一个问题是,即使遇到NULL值,我也可以使GROUP_CONCAT工作吗?

推荐答案

在包含NULL的情况下,可以使各列合为一体:

You can COALESCE the columns in case the contain NULLs:

如果lastname为null,则COALESCE(lastname,'')将返回一个空字符串,如果不是,则返回lastname的任何值.

COALESCE(lastname, '') would return an empty string if lastname was null, or whatever the value of lastname is if it is not.

这篇关于在MySQL中使用GROUP_CONCAT进行CONCAT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆