如何连接两个表并比较它们?(我问了好几次，但我找不到答案.) [英] How do I join two tables and compare them? (I've asked several times, but I couldn't find the answer.)

问题描述

我只想比较表 1 和表 2 一次.我想在每一面打印与错误金额或数据错误相同的金额.

I want to compare table 1 and table 2 only once. I want to print the same amount as the wrong amount or the data error on each side.

1.表名:hospital_payment_data

1.table name : hospital_payment_data

id    chart_num        chart_name          visit            card_amount_received
1        9740            t1             2019-01-02               1000                        
2        23143           t2             2019-01-02               3000          
3        14220           t3             2019-01-02               3000                        
4        2000            t4             2019-01-02               4000      
5        3000            t5             2019-01-02               5000      
6        3000            t5             2019-01-02               5000      
7        4000            t6             2019-01-02               6000      
8        5000            t7             2019-01-02               6000      

2.表名:credit_card

2.table name : credit_card

id           card_date             advenced_amount
1           2019-01-02                 1000        
2           2020-01-02                 2000   
3           2020-01-02                 3000 
4           2020-01-02                 4000 
5           2020-01-02                 10000 
6           2020-01-02                 6000 
7           2020-01-02                 9000 

我想要结果

在我的查询中:

SELECT
    hospital_payment_data.id,
  chart_num,
    visit,
  chart_name,
  sum(card_amount_received) as card_amount_received,
  credit_card.card_date,
  credit_card.amount as amount,
  if(credit_card.amount = sum(card_amount_received), 'ok', 'error') as result
from hospital_payment_data
left join credit_card on date(credit_card.card_date) = visit AND hospital_payment_data.card_amount_received = credit_card.amount
GROUP BY chart_num, visit

这是我的错误结果:

是否必须添加或修改数据库表才能按需要打印?还是我必须添加关系?指定了所有可用的数据值.

Do I have to add or modify the database table to print as I want? Or do I have to add a relationship? All data values available are specified.

推荐答案

我想这就是你所追求的.让我知道您是否需要其他东西.您可能应该在 credit_card 中有 chart_num，这样您就可以将这些表连接在一起.

I think this is what you are after. Let me know if you need something else. You should probably have chart_num in credit_card so you can join the tables together.

Select hpd.chart_num,
hpd.chart_name
hpd.visit,
hpd.card_amount_received,
cc.card_date, 
CASE WHEN hpd.card_amount_received = SUM(cc.advenced_amount) THEN hpd.card_amount_received  ELSE NULL END AS ‘amount’,
CASE WHEN hpd.card_amount_received > SUM(cc.advenced_amount) THEN ‘error’ ELSE ‘ok’ END AS ‘result’
 from hospital_payment_data hpd
LEFT JOIN credit_card ON cc.chart_num=hpd.chart_num AND 
date(cc.card_date) = date(hpd.visit)
GROUP BY hpd.chart_num, hpd.visit;

最好有一个 client_visit 表，以防客户一天来办公室两次，或者制定了一个他们来办公室当天不付款的付款计划.

It would probably be a good idea to have a client_visit table in case a client comes to the office twice in a day or sets up a payment plan where they aren’t paying on the day they come to the office.

这篇关于如何连接两个表并比较它们?(我问了好几次，但我找不到答案.)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！