时间戳

首页 > 解决方案 > 如何从两个表中检索数据？

mysql - 如何从两个表中检索数据？

问题描述

我在“hospital_payment_data”表上。我想调出数据个数，cache_account_received sum，total_medical_bills sum的数据，然后从cash_recipit_rowtable中调出mount sum值来表示。我应该怎么办？

  1. 医院付款数据

    在此处输入图像描述

  2. cash_receipt_row

    在此处输入图像描述

我要结果

在此处输入图像描述

但是，发送以下查询会导致以下结果：

SELECT 
  COUNT(*) as total,  
  SUM(cash_amount_received) AS sum_cash_amount_received, 
  COUNT(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , total_medical_bills, NULL)
  ) as obligatory_issue, 
  SUM(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , cash_amount_received, NULL)
  ) as sum_obligatory_issue, 
  SUM(amount) AS sum_amount 
FROM (
  SELECT total_medical_bills, cash_amount_received, amount 
  FROM hospital_payment_data, cash_receipt_row
) AS a

错误的结果

在此处输入图像描述

标签： mysqlsqlmariadb

解决方案

尝试这个。

SELECT 
  COUNT(*) as total,  
  SUM(cash_amount_received) AS sum_cash_amount_received, 
  COUNT(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , total_medical_bills, NULL)
  ) as obligatory_issue, 
  SUM(
    IF(total_medical_bills >= 100000 AND
    cash_amount_received , cash_amount_received, NULL)
  ) as sum_obligatory_issue, 
  SUM(amount) AS sum_amount 
FROM (
  SELECT total_medical_bills, cash_amount_received, amount 
  FROM hospital_payment_data, cash_receipt_row 
WHERE hospital_payment_data.id = cash_receipt_row.id
) AS a

推荐阅读