YanoRyuichi.com/
Wiki
Blog
GitHub
Sandbox
開始行:
* 複雑なSQL1 [#l0b66f7d]
注文マスタテーブルとその商品明細テーブル（注文された商品I...
があり、具体的には以下のような構成。
*** order [#jdf878e6]
- order_id
- order_date
*** order_goods [#h1e8c6c8]
- goods_id
- quantity
- order_id
直近30日間である商品(ID:100,110,120)がそれぞれ何個売れた...
SELECT og.goods_id, sum(og.quantity) from order_goods og...
on og.order_id = o.order_id
where og.goods_id in (100,110,120)
and now() <= o.order_date + '30 days'
group by og.goods_id;
Perlコードは以下の通り。
my $rs = $c->model('MyappDB::Order_goods');
my $date = ">= now() - interval '30 days'";
$rs = $rs->search( {
'order.order_date' => \$date,
'me.goods_id' => { -in => [@goods_ids] }
}, {
join => 'order',
select => [
'me.goods_id',
{sum => 'me.quantity'}
],
as => [qw/goods_id sum/],
group_by => [qw/me.goods_id/]
});
終了行:
* 複雑なSQL1 [#l0b66f7d]
注文マスタテーブルとその商品明細テーブル（注文された商品I...
があり、具体的には以下のような構成。
*** order [#jdf878e6]
- order_id
- order_date
*** order_goods [#h1e8c6c8]
- goods_id
- quantity
- order_id
直近30日間である商品(ID:100,110,120)がそれぞれ何個売れた...
SELECT og.goods_id, sum(og.quantity) from order_goods og...
on og.order_id = o.order_id
where og.goods_id in (100,110,120)
and now() <= o.order_date + '30 days'
group by og.goods_id;
Perlコードは以下の通り。
my $rs = $c->model('MyappDB::Order_goods');
my $date = ">= now() - interval '30 days'";
$rs = $rs->search( {
'order.order_date' => \$date,
'me.goods_id' => { -in => [@goods_ids] }
}, {
join => 'order',
select => [
'me.goods_id',
{sum => 'me.quantity'}
],
as => [qw/goods_id sum/],
group_by => [qw/me.goods_id/]
});
ページ名:
