php에서 데이터베이스 데이터를 비교하는 방법

php에서 데이터베이스 데이터를 비교하는 방법

저는 php의 데이터베이스 데이터를 비교하고 싶습니다.

이것은 제 테이블입니다.

병원_지불_자료표

  id      | date  |     cash_amount_received
----------------------------------
  1        2020-04-01      7000
  2        2020-04-29      1000
  3        2020-04-29      2000
  4        2020-04-29      3000
  5        2020-04-29      4000
  6        2020-04-29      5000
  7        2020-04-29      6000
  8        2020-04-29      70000

현금_지불_지불표

  id      |    date  |    amount
----------------------------------
  1             2020-04-29       1000
  2             2020-04-29       2000
  3             2020-04-29       3000
  4             2020-04-29       4000
  5             2020-04-29       5000
  6             2020-04-29       5000
  7             2020-04-29       7000

이것은 나의 sql 구문입니다.

    SELECT ifnull(hospital_payment_data.id,'-') AS pg_id, 
ifnull(hospital_payment_data.date,'-') AS pg_date, 
ifnull(hospital_payment_data.cash_amount_received,'-') AS pg_amount, 
ifnull(cash_receipt_publish.id,'-') AS van_id, 
ifnull(cash_receipt_publish.date,'-') AS van_date, 
ifnull(cash_receipt_publish.amount,'-') AS van_amount
FROM hospital_payment_data
LEFT JOIN cash_receipt_publish ON hospital_payment_data.id = cash_receipt_publish.id
            UNION ALL
            SELECT ifnull(hospital_payment_data.id,'-') AS pg_id, ifnull(hospital_payment_data.date,'-') AS pg_date, ifnull(hospital_payment_data.cash_amount_received,'-') AS pg_amount, 
            ifnull(cash_receipt_publish.id,'-') AS van_id, ifnull(cash_receipt_publish.date,'-') AS van_date, ifnull(cash_receipt_publish.amount,'-') AS van_amount
            FROM hospital_payment_data
            RIGHT JOIN cash_receipt_publish ON hospital_payment_data.id = cash_receipt_publish.id
            WHERE hospital_payment_data.id IS NULL limit 0

이 웹 페이지 결과에서

결과를 원합니다.

날짜와 금액이 일치하거나 금액이 일치하면 cash_receipt_public에 있는 표와 비교하여 False로 표현하고 싶습니다.

id     |  cash_amount_received    |    amount|    result
-----------------------------------------------------
  1          7000                    7000          true
  2          1000                    1000          true
  3          2000                    2000          true
  4          3000                    3000          true
  5          4000                    4000          true
  6          5000                    5000          true
  7          6000                    null          false
  8          10000                   null          false
  9          null                    5000          false

사용할 수 있을 것 같습니다.full join;

select coalesce(hpd.cash_amount_received, cpr.amount),
       coalesce(hpd.date, cpr.date),
       (case when hpd.id is not null and crp.id is not null then 'true' else 'false' end)
from (select hpd.*, 
             row_number() over (partition by date, cash_amount_received order by id) as seqnum
      from hospital_payment_data hpd
     ) hpd full join
     (select crp.*,
             row_number() over (partition by date, amount order by id) as seqnum
      from cash_receipt_publish crp
     ) crp
     on hpd.date = crp.date and
        hpd.cash_amount_received = crp.amount and
        hpd.seqnum = crp.seqnum;

편집:

MariaDB에서 다음을 사용할 수 있습니다.

select amount, min(date),
       (count(*) = 2)
from ((select hpd.date, cash_amount_received as amount,
              row_number() over (partition by cash_amount_received order by id) as seqnum
       from hospital_payment_data hpd
      )  union all
      (select crp.date, amount,
              row_number() over (partition by amount order by id) as seqnum
       from cash_receipt_publish crp
      )
     ) x
group by amount, seqnum

언급URL : https://stackoverflow.com/questions/61583939/how-to-compare-database-data-in-php