mysql group\u concat with in()函数

idfiyjo8 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(326)

我尝试在mysql上使用group concat with in函数，它没有给我任何语法错误，但我不确定它是否工作。
我试着这样做：

select
     case
         when weekday(ps.date) in (group_concat(vd.valid_days)) then ps.date
         when weekday(ps.date+1) in (group_concat(vd.valid_days)) then DATE_ADD(ps.date, INTERVAL 1 DAY)
         when weekday(ps.date+2) in (group_concat(vd.valid_days)) then DATE_ADD(ps.date, INTERVAL 2 DAY)
         when weekday(ps.date+3) in (group_concat(vd.valid_days)) then DATE_ADD(ps.date, INTERVAL 3 DAY)
         when weekday(ps.date+4) in (group_concat(vd.valid_days)) then DATE_ADD(ps.date, INTERVAL 4 DAY)
         when weekday(ps.date+5) in (group_concat(vd.valid_days)) then DATE_ADD(ps.date, INTERVAL 5 DAY)
     end as valid_date
from
    purchase_shopping  ps
    left join
    purchase_region pr on pr.id = ps.idfk_region
    left join
    valid_weeks_days vd on vd.idfk_region = pr.id
group by ps.id

我需要的基本上是返回一个有效的日期，如果某一天是在一个组concat从左加入。我不知道我的逻辑是错的还是行不通。obs:group concat函数返回如下内容：（1,2,5），因此它应该使用in（）函数。
包含返工代码数据的示例：

ps.date = 2018-10-17
weekday(ps.date) = 2 
group_concat(vd.valid_days) = (1,4)
then valid_date will be 2018-10-19

sql mysql

来源：https://stackoverflow.com/questions/52855082/mysql-group-concat-with-in-function

2条答案

按热度按时间

jaql4c8m1#

也许你只是想：

select (case when uc.id is not null then 'It has items' end)
from shopping_cart sc left join
     user_cart uc
     on uc.id = sc.idfk_cart;

赞(0）回复(0）举报 2021-06-21

w8ntj3qf2#

不能在同一查询中使用组\u concat的结果，因此必须使用子查询

select
     case
         when weekday(date) in valid_days then date
         when weekday(date+1) in valid_days then DATE_ADD(date, INTERVAL 1 DAY)
         when weekday(date+2) in valid_days then DATE_ADD(date, INTERVAL 2 DAY)
         when weekday(date+3) in valid_days then DATE_ADD(date, INTERVAL 3 DAY)
         when weekday(date+4) in valid_days then DATE_ADD(date, INTERVAL 4 DAY)
         when weekday(date+5) in valid_days then DATE_ADD(date, INTERVAL 5 DAY)
     end as valid_date
     from (
     select ps.date, (group_concat(vd.valid_days)) as valid_days
from
    purchase_shopping  ps
    left join
    purchase_region pr on pr.id = ps.idfk_region
    left join
    valid_weeks_days vd on vd.idfk_region = pr.id
group by ps.id
) as sub_query

赞(0）回复(0）举报 2021-06-20

我来回答

mysql group\u concat with in()函数

2条答案

相关问题

热门标签

最新问答