Sql: Pgsql -> Mysql, Traduction :P Options

[elf]

Salut salut,

Ça fais bien longtemps que je suis plus venu ici, depuis que j'ai perdu le gout du PHP... (La je deviens fan de Ruby On Rails)

Mais la j'ai un gros problème:

J'ai une query SQL écrite pour PgSQL. Est-ce que quelqu'un pourrais m'écrire un equivalent pour MySQL? J'ai essayé mais mes connaisances ne sont pas assez bonnes pour ça...

Le code:

Code

select (select count(*)
                  from orders
                 where status = 'C' and
                       lower(payment_type) != 'free' and
                       current_date - #{days} <= order_time) as orders,
               sum(line_items.unit_price * quantity)
                 - sum(coalesce(coupons.amount, 0))
                 - sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as earned,
               sum(quantity) as q,
               products.name as product

        from orders
             inner join line_items on orders.id = line_items.order_id
             left outer join products on products.id = line_items.product_id
             left outer join coupons on coupons.id = orders.coupon_id

        where status = 'C' and lower(payment_type) != 'free' and current_date - #{days} <= order_time

        group by product

Code

        select date_part('year', orders.order_time) as year,
               date_part('month', orders.order_time) as month,
               date_part('day', orders.order_time) as day,
               sum(line_items.unit_price * quantity) - sum(coalesce(coupons.amount, 0))
                  - sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as earned,
               max(orders.order_time) as last_time

        from orders
             inner join line_items on orders.id = line_items.order_id
             left outer join coupons on coupons.id = orders.coupon_id

        where status = 'C' and payment_type != 'Free'

        group by year, month, day

        order by last_time desc limit 8

Ce message a été modifié par elf - 5 Jul 2007, 00:15.

[elf]

Ah, j'ai trouvé tout seul: C'était

Code

select (select count(*)
                  from orders
                 where status = 'C' and
                       lower(payment_type) != 'free' and
                       current_date - #{days} <= order_time) as orders,
               sum(line_items.unit_price * quantity)
                 - sum(ifnull(coupons.amount, 0))
                 - sum(line_items.unit_price * quantity * ifnull(percentage, 0) / 100) as earned,
               sum(quantity) as q,
               products.name as product

        from orders
             inner join line_items on orders.id = line_items.order_id
             left outer join products on products.id = line_items.product_id
             left outer join coupons on coupons.id = orders.coupon_id

        where status = 'C' and lower(payment_type) != 'free' and current_date - #{days} <= order_time

        group by product

et

Code

select year(orders.order_time) as year,
               month(orders.order_time) as month,
               dayofmonth(orders.order_time) as day,
               sum(line_items.unit_price * quantity) - sum(ifnull(coupons.amount, 0))
                  - sum(line_items.unit_price * quantity * ifnull(percentage, 0) / 100) as earned,
               max(orders.order_time) as last_time

        from orders
             inner join line_items on orders.id = line_items.order_id
             left outer join coupons on coupons.id = orders.coupon_id

        where status = 'C' and payment_type != 'Free'

        group by year, month, day

        order by last_time desc limit 8