连接两个具有相同id和日期的表

1l5u6lss 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(253)

我很难提出这个问题，我能得到一些帮助吗？我有一个名为measurements的表，它如下所示：

+----+----------+-------+------+
  | id | cost     | month | year |
  +----+----------+-------+------+
  |  1 |  6860.52 |     5 | 2018 |
  |  1 | 11993.52 |     6 | 2018 |
  |  1 |   3823.2 |     7 | 2018 |
  |  1 |   3557.7 |     8 | 2018 |
  |  1 |  3355.92 |     9 | 2018 |
  |  1 |   357.54 |    10 | 2018 |
  +----+----------+-------+------+

和一个名为payment的表

+------------+---------------+-----------------+
  | id         | period        | payment         |
  +------------+---------------+-----------------+
  |          1 | 2018-05-01    |               0 |
  |          1 | 2018-06-01    |               0 |
  |          1 | 2018-06-01    |           34327 |
  |          1 | 2018-07-01    |             100 |
  |          1 | 2018-07-01    |             500 |
  |          1 | 2018-07-01    |             400 |
  |          1 | 2018-08-01    |               0 |
  +------------+---------------+-----------------+

我在尝试制作返回以下内容的select语句时遇到了麻烦：

+------------+---------------+----------------+-----------------+
  | id         | period        | date           | payment         |
  +------------+---------------+----------------+-----------------+
  |          1 | 2018-05-01    | 2018-05-01     |               0 |
  |          1 | 2018-06-01    | 2018-06-01     |           34327 |
  |          1 | 2018-07-01    | 2018-07-01     |            1000 |
  |          1 | 2018-08-01    | 2018-08-01     |               0 |
  |          1 | 2018-09-01    | NULL           |               0 |
  |          1 | 2018-10-01    | NULL           |               0 |
  +------------+---------------+----------------+-----------------+

日期从concat开始（年，'-'，月，'-'，1）
谢谢您
架构：

CREATE TABLE measurements (id INT, cost FLOAT, month INT, year INT);

INSERT INTO measurements VALUES (1, 6860.52, 5, 2018), 
(1, 11993.52, 6, 2018), (1, 3823.2, 7, 2018), 
(1, 3557.7, 8, 2018), (1, 3355.92, 9, 2018), (1, 357.54, 10, 2018);

CREATE TABLE payment (id INT, period DATE, payment INT);
INSERT INTO payment VALUES (1, '2018-05-01', 0),
(1, '2018-06-01', 0),(1, '2018-06-01', 34327 ),(1, '2018-07-01', 100),
(1, '2018-07-01', 500),(1, '2018-07-01', 400), (1, '2018-08-01', 0);

mysql

来源：https://stackoverflow.com/questions/53440478/join-two-tables-with-same-id-and-date

2条答案

按热度按时间

czfnxgou1#

你可以在下面使用左连接

select id,str_to_date(concat(year,'-',month,'-',1),'%Y-%m-%d') as period,b.period as `date`,sum(payment) as payment
from measurements a left join payment b
on a.id=b.id and str_to_date(concat(year,'-',month,'-',1),'%Y-%m-%d')=b.period
group by str_to_date(concat(year,'-',month,'-',1),'%Y-%m-%d')

赞(0）回复(0）举报 2021-06-18

carvr3hs2#

你在找这个吗？

select measurements.id,
      cast(concat(measurements.year, '-', measurements.month, '-01') as date) as period,
      payment.period as date, sum(payment) payment
from measurements
        left join payment on measurements.id = payment.id and cast(concat(measurements.year, '-', measurements.month, '-01') as date)  =  payment.period
group by measurements.id, measurements.year, measurements.month, payment.period
order by measurements.year, measurements.month;

赞(0）回复(0）举报 2021-06-18

我来回答

连接两个具有相同id和日期的表

2条答案

相关问题

热门标签

最新问答