mysql在不同条件下从另一个表加入列
问题描述:
我有一个“价格表”。我试图从另一个名为'rate_cycles'的表中加入一列new_rate,它应该基于某些条件。我有麻烦创建这样的查询。 价格表包含前缀,这里是两个表中的样品。如果来自价格表匹配的国家+地区对,我将从周期加入'new_rate'。mysql在不同条件下从另一个表加入列
如果(在循环表中)前缀为空并通知0我需要从该行加入'new_rate'。如果它被通知(通知= 1),我需要找到,如果存在,通知为0的下一行。如果在该行中前缀为空,则我从该行加入new_rate,但是如果前缀不为null,则对于new_rate值我需要最后一行前缀为空,通知为1(如果存在,如果不是则为空)。
我怎么能做到这一点?谢谢。
表Pricelist
+----+--------------+---------+--------------+---------+-----------+----------+-----------+----------+--------+---------------------+----------------+ | id | pricelist_id | country | region | prefix | is_mobile | is_fixed | is_custom | currency | rate | last_updated | has_rate_cycle | +----+--------------+---------+--------------+---------+-----------+----------+-----------+----------+--------+---------------------+----------------+ | 2 | 1 | Albania | Fixed ALBTEL | 3554249 | 0 | 0 | 0 | USD | 0.0000 | 2014-09-23 09:48:00 | 1 | | 3 | 1 | Albania | Fixed ALBTEL | 3554250 | 0 | 0 | 0 | USD | 0.0000 | 2014-09-23 09:48:00 | 1 | | 4 | 1 | Albania | Fixed ALBTEL | 3554251 | 0 | 0 | 0 | USD | 0.0000 | 2014-09-23 09:48:00 | 1 | | 5 | 1 | Albania | Fixed ALBTEL | 3554252 | 0 | 0 | 0 | USD | 0.0000 | 2014-09-23 09:48:00 | 1 | +----+--------------+---------+--------------+---------+-----------+----------+-----------+----------+--------+---------------------+----------------+
周期表
+----+------------+---------+--------------+----------+---------------------+---------------+---------+----------+---------+ | id | carrier_id | country | region | new_rate | activation_date | update_status | prefix | notified | grouped | +----+------------+---------+--------------+----------+---------------------+---------------+---------+----------+---------+ | 1 | 15 | Albania | Fixed ALBTEL | 1.0000 | 2014-09-30 03:48:00 | NEW | NULL | 0 | 0 | | 2 | 15 | Albania | Fixed ALBTEL | 2.0000 | 2014-10-01 03:48:00 | BLOCKED | 3554250 | 0 | 0 | | 3 | 15 | Albania | Fixed ALBTEL | 3.0000 | 2014-10-02 03:48:00 | DECREASE | NULL | 0 | 0 | | 4 | 15 | Albania | Fixed ALBTEL | 4.0000 | 2014-10-03 03:48:00 | NEW | 3554250 | 0 | 0 | +----+------------+---------+--------------+----------+---------------------+---------------+---------+----------+---------+
答
这是一个艰难的查询和管理,使之做什么,我需要的。如果它可以简化一点,它会很棒。我正在分享我的解决方案。
SELECT p.id,p.country, p.region, p.prefix, p.currency, p.rate, (SELECT CASE WHEN notified = 0 THEN (SELECT CASE WHEN prefix IS NULL THEN new_rate ELSE (SELECT CASE WHEN COUNT(new_rate)>0 THEN (SELECT new_rate FROM pricelist_rates_cycle WHERE country=p.country and region=p.region and notified=1 and prefix is null order by DATE(activation_date) DESC LIMIT 1) ELSE NULL END FROM pricelist_rates_cycle WHERE country=p.country and region=p.region and notified=1 and prefix is null ) END FROM pricelist_rates_cycle WHERE country=p.country and region=p.region and notified=0 LIMIT 1) ELSE ( SELECT CASE WHEN COUNT(new_rate)>0 THEN IF(prefix is null, new_rate, (SELECT new_rate FROM pricelist_rates_cycle WHERE country=p.country and region=p.region and notified=1 and prefix is null order by DATE(activation_date) DESC LIMIT 1) ) ELSE (SELECT new_rate FROM pricelist_rates_cycle WHERE country=p.country and region=p.region and notified=1 and prefix is null order by DATE(activation_date) DESC LIMIT 1) END FROM pricelist_rates_cycle WHERE country=p.country and region=p.region and notified=0 ) END FROM pricelist_rates_cycle WHERE country=p.country and region=p.region LIMIT 1) AS new_rate FROM pricelist_15 AS p ORDER BY country, region ASC
如果你喜欢,可以考虑下列行为这个简单的两步过程:1.如果您还没有这样做,提供适当的DDL(和/或sqlfiddle),这样我们就可以更方便地复制问题。 2.如果您尚未这样做,请提供与步骤1中提供的信息相符的所需结果集。 – Strawberry 2014-09-29 09:08:30
@Strawberry,谢谢,我会的。我将在sqlfiddle中创建一个模式。 – infinite 2014-09-29 09:18:02
不要忘记,这个过程有两个步骤! – Strawberry 2014-09-29 09:24:49