mysql - GROUP_CONCAT 选择中的条件

Question

我有 3 个表，我正在加入它们以获取一些数据。

-----------------Table Name: users-------------------------------|user_id  | user_name |-------------------------------123      | abc-------------------------------223      | bcd-------------------------------323      | cde------------------------------------------------Table Name: limit-------------------------------user_id  | limit_id-------------------------------123      | 1-------------------------------223      | 2-------------------------------323      | 3-------------------------------323      | 4--------------------------------------------------------Table Name: limit_setting-------------------------------limit_id | date_limit-------------------------------1        | 2016-09-29 12:00:00-------------------------------2        | 2016-09-28 12:00:00-------------------------------3        | 2016-09-27 12:00:00-------------------------------1        | 2016-09-27 12:00:00-------------------------------1        | 2016-09-24 12:00:00-------------------------------4        | 2016-09-25 12:00:00-------------------------------4        | 2016-09-26 12:00:00-------------------------------

I need to get a result like this. I am stuck with the GROUP_CONCAT for the dates column. The date column should have all entries other than the MAX date. If there is only one entry in the limit_setting table for that limit_id then it shouldn't show anything for that user. count_dates : its the number of entries which are there in the limit_setting table.

Desired output----------------------------------------------------------------------user_name | dates                                       | count_dates   ----------------------------------------------------------------------abc       | 2016-09-27 12:00:00 , 2016-09-24 12:00:00   | 3----------------------------------------------------------------------bcd       |                                             | 1 ----------------------------------------------------------------------cde       |                                             | 1 -----------------------------------------------------------------------cde       | 2016-09-26 12:00:00                         | 2 -----------------------------------------------------------------------

SELECT PP.`user_name`, count(ESL.Limit_id) as count_dates,
  GROUP_CONCAT(ESL.date_limit SEPARATOR ',') as dates 
FROM users as PP INNER JOIN `limit` as PAL ON PP.Id = PAL.PlayerId
LEFT JOIN limit_setting as ESL ON ESL.LimitId = PAL.limitId 
GROUP BY PAL.limitId

另外我试过(没有返回)

SELECT ESL.date_limit, MAX(date_limit) as max_date, PP.`user_name`, count(ESL.Limit_id) as count_dates, 
  GROUP_CONCAT(ESL.date_limit SEPARATOR ',') as dates 
FROM users as PP INNER JOIN `limit` as PAL ON PP.Id = PAL.PlayerId 
LEFT JOIN limit_setting as ESL ON ESL.LimitId = PAL.limitId 
GROUP BY PAL.limitId
HAVING ESL.date_limit > max_date

我尝试使用 Find_in_set 但不确定如何有效地使用它。

Answer 1

试试这个:

SELECT user_name,        
       CASE WHEN COUNT(*) > 1 
            THEN SUBSTRING_INDEX(GROUP_CONCAT(date_limit ORDER BY date_limit), 
                                 ',', COUNT(*) - 1) 
            ELSE ''
       END AS dates,             
       COUNT(*) AS count_dates
FROM users as PP 
INNER JOIN `limit` as PAL ON PP.user_id = PAL.user_id
LEFT JOIN limit_setting as ESL ON ESL.limit_id = PAL.limit_id
GROUP BY user_name

查询使用SUBSTRING_INDEX函数以获取 GROUP_CONCAT 返回的所有日期 除了 最后一个日期。在 GROUP_CONCAT 中使用 ORDER BY，我们可以将最大日期放在末尾，以便 SUBSTRING_INDEX 准确截断该日期。

Demo here