Home / Questions / 124152
Accepted
Pablo Lozano
Asked: 2020-12-13 02:43:05 +0800 CST 2020-12-13 02:43:05 +0800 CST

使用许多嵌套查询按日期范围查询

  • 772

我必须获得一份公司客户的清单，我们在本年度向其开具发票，表明我们每个月开具了多少发票。

我有两个表：客户表（客户）和发票表（发票）。关系很明显：一个客户可以有很多张发票，每张发票都与一个客户相关。

我唯一想到的是以下内容：

select id, 
name as "Nombre",
identity_number as "CIF", 
(select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-02-01' and customer_id=c.id group by customer_id) as "Enero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-02-01' and '2017-03-01' and customer_id=c.id group by customer_id) as "Febrero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-03-01' and '2017-04-01' and customer_id=c.id group by customer_id) as "Marzo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-04-01' and '2017-05-01' and customer_id=c.id group by customer_id) as "Abril",
(select sum(total) 
    from invoice 
    where payment_date between '2017-05-01' and '2017-06-01' and customer_id=c.id group by customer_id) as "Mayo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-06-01' and '2017-07-01' and customer_id=c.id group by customer_id) as "Junio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-07-01' and '2017-08-01' and customer_id=c.id group by customer_id) as "Julio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-08-01' and '2017-09-01' and customer_id=c.id group by customer_id) as "Agosto",
(select sum(total) 
    from invoice 
    where payment_date between '2017-09-01' and '2017-10-01' and customer_id=c.id group by customer_id) as "Septiembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-10-01' and '2017-11-01' and customer_id=c.id group by customer_id) as "Octubre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-11-01' and '2017-12-01' and customer_id=c.id group by customer_id) as "Noviembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-12-01' and '2017-12-31' and customer_id=c.id group by customer_id) as "Diciembre"
from customer c
where c.branding_id=1 and (select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-12-31' and customer_id=c.id group by customer_id) > 0

这可行，但我发现它的效率极低（如果我们在 WHERE 中包含一个子查询，则有 13 个子查询）。有什么方法可以简化此查询，还是获取数据的最佳方法？

sql

2 Answers

  1. 编辑：在写完这个答案（对于 MySQL）后，我意识到问题出在 PostgreSQL 中。正确的语法在 OP 的答案中。

    首先，最后一条（即 2017 年 1 月至 12 月的付款总和大于零）与payment_date2017 年相同。

    其次，假设您的发票表是并且包含：

    create table invoice (customer_id integer, total integer, payment_date datetime);
insert into invoice values (1, 5, now());
insert into invoice values (1, 15, NOW() - INTERVAL 1 DAY);
insert into invoice values (1, 45, NOW() - INTERVAL 41 DAY);
insert into invoice values (1, 12, NOW() - INTERVAL 61 DAY);
insert into invoice values (1, 13, NOW() - INTERVAL 91 DAY);
insert into invoice values (2, 55, now());
insert into invoice values (2, 15, NOW() - INTERVAL 21 DAY);
insert into invoice values (3, 145, NOW() - INTERVAL 71 DAY);
insert into invoice values (3, 1, NOW() - INTERVAL 16 DAY);
insert into invoice values (3, 3, NOW() - INTERVAL 121 DAY);

    您可以在每一列中执行以下操作：

    SUM(IF(MONTH(payment_date)=1, total, 0) as enero

    这将是这样的：

    select customer_id,
  SUM(IF(month(payment_date)=12,total,0)) as diciembre,
  SUM(IF(month(payment_date)=11,total,0)) as noviembre,
  SUM(IF(month(payment_date)=10,total,0)) as octubre,
  SUM(IF(month(payment_date)=9,total,0)) as septiembre,
  SUM(IF(month(payment_date)=8,total,0)) as agosto
from invoice
where year(payment_date)=2017
group by customer_id

    在此处输入图像描述

    我没有包括所有月份或 JOIN 与客户表，我把这部分留给你，但如果你想列出所有客户，包括那些今年没有支付任何费用的客户，FROM它必须是

    FROM customer c LEFT JOIN invoice on customer_id=c.id AND YEAR(payment_date)=2017

    示例小提琴

    • 3
  2. Best Answer

    根据@amenadiel 的回答（这对 MySQL 来说是正确的），我创建了以下查询：

    select c.id, 
c.name as "Nombre",
c.identity_number as "CIF", 
  SUM(CASE WHEN date_part('month',i.payment_date)=12 then total else 0 end ) as diciembre,
  SUM(CASE WHEN date_part('month',i.payment_date)=11 then total else 0 end) as noviembre,
  ...
  SUM(CASE WHEN date_part('year',i.payment_date)=2017 then total else 0 end) as TOTAL
from customer c join invoice i on c.id=i.customer_id
where date_part('year',i.payment_date)=2017 and c.branding_id=2
group by c.id
order by c.id

    出于好奇，问题中的查询需要将近两分钟才能执行，而此解决方案需要大约 300 毫秒才能返回相同的结果

    • 1