sql查询报错Unknown column 'consume_date' in 'where clause'

[allenwhs]

select a.id, max(b.amount_after) as initial_amount, c.consume_amount from mcc_amount_change_record b 
left join mcc_customer a on a.id = b.customer_id 
left join (select customer_id, min(consume_date) as min_consume_date, consume_amount from mcc_customer_consume group by customer_id ) as c on c.customer_id = b.customer_id 
where register_date > "2017-04-01 00:00:00" 
and create_time < min_consume_date 
and amount_after >= "500" 
and consume_date = min_consume_date
group by id

consume_date 在 mcc_customer_consume表里有
求教

[OwenZeng_DBA]

引用 楼主 qq_28029989 的回复:

select a.id, max(b.amount_after) as initial_amount, c.consume_amount from mcc_amount_change_record b 
left join mcc_customer a on a.id = b.customer_id 
left join (select customer_id, min(consume_date) as min_consume_date, consume_amount from mcc_customer_consume group by customer_id ) as c on c.customer_id = b.customer_id 
where register_date > "2017-04-01 00:00:00" 
and create_time < min_consume_date 
and amount_after >= "500" 
and consume_date = min_consume_date
group by id

consume_date 在 mcc_customer_consume表里有 求教

试试在consume_date 前面加上表名，，变成 mcc_customer_consume.consume_date

[allenwhs]

引用 1 楼 z10843087 的回复:

[quote=引用 楼主 qq_28029989 的回复:]

select a.id, max(b.amount_after) as initial_amount, c.consume_amount from mcc_amount_change_record b 
left join mcc_customer a on a.id = b.customer_id 
left join (select customer_id, min(consume_date) as min_consume_date, consume_amount from mcc_customer_consume group by customer_id ) as c on c.customer_id = b.customer_id 
where register_date > "2017-04-01 00:00:00" 
and create_time < min_consume_date 
and amount_after >= "500" 
and consume_date = min_consume_date
group by id

consume_date 在 mcc_customer_consume表里有 求教

试试在consume_date 前面加上表名，，变成 mcc_customer_consume.consume_date [/quote] 还是不行

[OwenZeng_DBA]

引用 2 楼 qq_28029989 的回复:

[quote=引用 1 楼 z10843087 的回复:] [quote=引用 楼主 qq_28029989 的回复:]

select a.id, max(b.amount_after) as initial_amount, c.consume_amount from mcc_amount_change_record b 
left join mcc_customer a on a.id = b.customer_id 
left join (select customer_id, min(consume_date) as min_consume_date, consume_amount from mcc_customer_consume group by customer_id ) as c on c.customer_id = b.customer_id 
where register_date > "2017-04-01 00:00:00" 
and create_time < min_consume_date 
and amount_after >= "500" 
and consume_date = min_consume_date
group by id

consume_date 在 mcc_customer_consume表里有 求教

试试在consume_date 前面加上表名，，变成 mcc_customer_consume.consume_date [/quote] 还是不行[/quote] 是不是一段代码中的其他部分出差，代码检查下

[allenwhs]

select a.id, max(b.amount_after) as initial_amount, c.consume_amount as first_consume_amount from mcc_amount_change_record b 
left join mcc_customer a on a.id = b.customer_id 
left join (select customer_id, consume_date = min(consume_date) as min_consume_date, consume_amount from mcc_customer_consume group by customer_id ) as c on c.customer_id = b.customer_id 
where register_date > "2017-04-01 00:00:00" 
and create_time < min_consume_date 
and amount_after >= "500" 
group by id

换成这样可以运行，但是不出结果。。。。

[OwenZeng_DBA]

@qq_28029989 仔细看了下，确实是有问题的。 mcc_customer_consume 里面有个consume_date ，但是你的语句中并没有查出consume_date 。所以会报错 ( SELECT customer_id , MIN(consume_date) AS min_consume_date , consume_amount FROM mcc_customer_consume GROUP BY customer_id ) AS c

[OwenZeng_DBA]

@qq_28029989 这样试试呢

SELECT  a.id ,
        MAX(b.amount_after) AS initial_amount ,
        c.consume_amount
FROM    mcc_amount_change_record b
        LEFT JOIN mcc_customer a ON a.id = b.customer_id
        LEFT JOIN ( SELECT  customer_id ,
                            MIN(consume_date) AS min_consume_date ,
                            consume_amount
                    FROM    mcc_customer_consume
                    GROUP BY customer_id
                  ) AS c ON c.customer_id = b.customer_id
        JOIN mcc_customer_consume d ON c.min_consume_date = d.consume_date
WHERE   register_date > "2017-04-01 00:00:00"
        AND create_time < min_consume_date
        AND amount_after >= "500"
GROUP BY id

[RINK_1]

这样试试呢。 select a.id, max(b.amount_after) as initial_amount, c.consume_amount from mcc_amount_change_record b left join mcc_customer a on a.id = b.customer_id left join mcc_customer_consume c on c.customer_id = b.customer_id where register_date > "2017-04-01 00:00:00" and create_time < min_consume_date and amount_after >= "500" and not exists (select 1 from mcc_customer_consume where c.customer_id=customer_id and consume_date<c.consume_date) group by id

[allenwhs]

引用 7 楼 RINK_1 的回复:

这样试试呢。 select a.id, max(b.amount_after) as initial_amount, c.consume_amount from mcc_amount_change_record b left join mcc_customer a on a.id = b.customer_id left join mcc_customer_consume c on c.customer_id = b.customer_id where register_date > "2017-04-01 00:00:00" and create_time < min_consume_date and amount_after >= "500" and not exists (select 1 from mcc_customer_consume where c.customer_id=customer_id and consume_date<c.consume_date) group by id

min_consume_date前面都没有查，报错

[allenwhs]

引用 6 楼 z10843087 的回复:

@qq_28029989 这样试试呢

SELECT  a.id ,
        MAX(b.amount_after) AS initial_amount ,
        c.consume_amount
FROM    mcc_amount_change_record b
        LEFT JOIN mcc_customer a ON a.id = b.customer_id
        LEFT JOIN ( SELECT  customer_id ,
                            MIN(consume_date) AS min_consume_date ,
                            consume_amount
                    FROM    mcc_customer_consume
                    GROUP BY customer_id
                  ) AS c ON c.customer_id = b.customer_id
        JOIN mcc_customer_consume d ON c.min_consume_date = d.consume_date
WHERE   register_date > "2017-04-01 00:00:00"
        AND create_time < min_consume_date
        AND amount_after >= "500"
GROUP BY id

跑不出来

[OwenZeng_DBA]

引用 9 楼 qq_28029989的回复:

[quote=引用 6 楼 z10843087 的回复:] @qq_28029989 这样试试呢

SELECT  a.id ,
        MAX(b.amount_after) AS initial_amount ,
        c.consume_amount
FROM    mcc_amount_change_record b
        LEFT JOIN mcc_customer a ON a.id = b.customer_id
        LEFT JOIN ( SELECT  customer_id ,
                            MIN(consume_date) AS min_consume_date ,
                            consume_amount
                    FROM    mcc_customer_consume
                    GROUP BY customer_id
                  ) AS c ON c.customer_id = b.customer_id
        JOIN mcc_customer_consume d ON c.min_consume_date = d.consume_date
WHERE   register_date > "2017-04-01 00:00:00"
        AND create_time < min_consume_date
        AND amount_after >= "500"
GROUP BY id

跑不出来[/quote] 跑不出来是表太大了吗，你拆分开用个临时表

[二月十六]

consume_date 在 mcc_customer_consume表里有，但是查询的语句没有关联mcc_customer_consume表，所以不存在。