The company is engaged in receiving recyclables and has several reception points. Each item receives money for their delivery to deliverers in exchange for raw materials. In fact, the diagram shows two databases. Each task for this scheme uses only one pair of tables (either with the suffix “_o” or without it). In the Income_o and Outcome_o tables, the primary key is the pair of attributes {point, date} - the number of the receiving point and the date. This key should simulate the situation when information about the receipt of money at the receiving point and its issuance to deliverers are recorded in the database no more than once a day. Database

Query: Determine the leader in the amount of payments in the competition between each pair of points with the same numbers from two different tables - outcome and outcome_o - for each day when recycling was received on at least one of them. Conclusion: Item number, date, text: - "once a day", if the amount of payments is more for a company with reporting once a day; - "more than once a day", if - for a company with reporting several times a day; - "both" if the payout amount is the same.

For a very long time I have been thinking how to make it without using Full Join and Intersect. The network has a Transact-SQL solution:

select case when o1.point is null then o2.point else o1.point end, case when o1.date is null then o2.date else o1.date end, case when o1.out is null and o2.out is not null then 'more than once a day' when o2.out is null and o1.out is not null then 'once a day' when o2.out is null and o1.out is null then 'both' when o1.out > o2.out then 'once a day' when o1.out < o2.out then 'more than once a day' when o1.out = o2.out then 'both' else 'both' end from (select point, date, out from outcome_o where (point in (select distinct point from outcome intersect select distinct point from outcome_o))) as o1 full join (select point, left(convert(varchar, date, 121), 10) as date, sum(out) as out from outcome where (point in (select distinct point from outcome intersect select distinct point from outcome_o)) group by point, left(convert(varchar, date, 121), 10)) as o2 on left(convert(varchar, o1.date, 121), 10) = o2.date and (o1.point = o2.point)
  • Comments are not intended for extended discussion; conversation moved to chat . - Nicolas Chabanovsky ♦

1 answer 1

The one who wrote it for MS SQL has become too smart. intersect is extremely rarely needed at all, and in this case it is replaced by the usual join . As for full join , it is not in MySQL, often union used instead of it with the representation of data from different tables in different columns and the subsequent group by . In our case, it can also be combined with the calculation of the amount by date.

 select A._point, dt, sum(_out) o1, sum(out_o) o2, case when sum(_out) > sum(out_o) then 'once a day' when sum(_out) < sum(out_o) then 'more than once a day' else 'both' end from ( select _point, date(_date) as dt, _out, 0 as out_o from Outcome union all select _point, date(_date) , 0, _out from Outcome_O ) A join (select distinct a._point from Outcome a, Outcome_O b where a._point=b._point) B on A._point=B._point group by A._point, dt