比较两个表后如何输出匹配? [英] How do I output a match after comparing two tables?

问题描述

我想比较两张表.但我不知道该怎么办，因为我不够好.我想知道怎么写查询

no.1_table

id chart_num chart_name 访问 card_amount_received1 4 用户1 2020-04-05 10002 5 用户2 2020-05-05 10003 5 用户2 2020-05-05 10004 5 用户2 2020-06-05 10005 6 用户3 2020-07-05 10006 6 用户3 2020-08-05 10007 7 用户4 2020-09-05 10008 7 用户4 2020-09-05 1000

no.2_table

id card_date advenced_amount1 2020-04-05 17:28:00 10002 2020-05-05 12:12:12 200010 2020-11-05 12:12:12 5000

想要结果

条件和顺序如下.

1. no.1_table，将每个chart_num的值相同的列相加，访问.

2. 将数字1的结果值的visit和card_count_received与no.2_table的card_date和advenced_amount值进行比较.

3. 如果no.1_table的chart_num和visit的值与no.2_table的card_date和no.2_table的added_count值相同，则ok，否则输出错误，处理为null.

如何创建查询语句?

数据库技能不足.请给我答复.

解决方案

1. no.1_table，将每个chart_num的值相同的一列相加，访问.

为此，您要group bychart_num 并访问.任何具有相同chart_num 和访问的行都将在结果中显示为单行.然后您可以sum 收到的金额，这将添加将一个组的所有值加起来.

选择图表编号，访问，总和(card_amount_received)作为 card_amount_received从表 1按chart_num分组，访问

chart_name 是个问题.您无法显示它，因为它不是 group by 的一部分.它是一个字符串，因此将它与 sum 或 count 等函数聚合是没有意义的.虽然在数据中chart_num 具有相同的chart_name，但不能保证.

一种解决方案是使用 group_concat 连接每个在一个组中一起命名.每个组只能有一个名字.

选择图表编号，访问，group_concat(chart_name) 作为chart_name，总和(card_amount_received)作为 card_amount_received从表 1按chart_num分组，访问

但是，正确的解决方案是修复架构.chart_name 是重复的，这是要避免的.相反，将图表列移动到它们自己的表中.然后要获取图表名称，请加入 chart_num.

创建表格图表(id 序列主键，名称 varchar(255) 不为空);插入图表(id，name)值(4, 'user1'), (5, 'user2'), (6, 'user3'), (7, 'user4');alter table table1 drop column chart_name;选择chart.id 作为chart_num，访问，chart.name 作为chart_name，总和(card_amount_received)作为 card_amount_received从表 1在charts.id = chart_num 上加入图表按chart_num分组，访问

<块引用>

1. 将数字1的结果值的visit和card_count_received与no.2_table的card_date和advanced_amount值进行比较.

我们需要一个左连接，第二个表匹配 card_date 和访问.左连接表示左"表中的所有行(即 from 表)将始终出现，即使连接表中没有匹配项.

访问是一个日期.card_date 不是日期而是时间戳.为了匹配它们，我们需要将 card_date 转换为日期.

选择chart.id 作为chart_num，访问，chart.name 作为chart_name，总和(card_amount_received)作为 card_amount_received，table2.card_date,table2.advanced_amount 作为金额从表 1在charts.id = chart_num 上加入图表在日期(table2.chart_date)= 访问左连接表 2按chart_num分组，访问

<块引用>

如果no.1_table的chart_num和visit的值与no.2_table的card_date和no.2_table的added_count值相同，则ok，否则输出错误，处理为null.

我们需要比较advanced_amount 和sum(card_amount_received).如果它们相等:好的.如果不是:错误.在标准 SQL 中，我们会使用 case，但是MariaDB 有一个非标准的 if更紧凑.

选择chart.id 作为chart_num，访问，chart.name 作为chart_name，总和(card_amount_received)作为 card_amount_received，table2.card_date,table2.advanced_amount 作为金额，if(table2.advanced_amount = sum(card_amount_received), 'ok', 'error') 结果从表 1在charts.id = chart_num 上加入图表在日期(table2.chart_date)= 访问左连接表 2按chart_num分组，访问

I want to compare two tables. But I don't know what to do because I'm not good enough. I want to know how to write query

no.1_table

id  chart_num       chart_name            visit            card_amount_received
1        4            user1             2020-04-05               1000                        
2        5            user2             2020-05-05               1000           
3        5            user2             2020-05-05               1000                        
4        5            user2             2020-06-05               1000              
5        6            user3             2020-07-05               1000        
6        6            user3             2020-08-05               1000                 
7        7            user4             2020-09-05               1000                    
8        7            user4             2020-09-05               1000                    

no.2_table

id           card_date             advenced_amount
1        2020-04-05 17:28:00            1000        
2        2020-05-05 12:12:12            2000   
10       2020-11-05 12:12:12            5000              

want result

The conditions and order are as follows.

1. no.1_table, sum a column with the same value of each chart_num and visit.

2. Compare the visit and card_count_received of the result value of number 1 with the card_date and the advenced_amount values of no.2_table.

3. If the value of chart_num and visit of no.1_table is the same as the card_date of no.2_table and the added_count value of no.2_table, ok otherwise output error with null processing.

How do I create a query statement?

Database skills are insufficient. Please give me a reply.

解决方案

1. no.1_table, sum a column with the same value of each chart_num and visit.

For this you want to group by the chart_num and visit. Any rows with the same chart_num and visit will appear as a single row in the result. Then you can sum the amount received, this will add up all the values for a group.

select
  chart_num,
  visit,
  sum(card_amount_received) as card_amount_received
from table1
group by chart_num, visit

chart_name is a problem. You can't display it as it is not part of the group by. It's a string so it doesn't make sense to aggregate it with functions like sum or count. Though in the data chart_num has the same chart_name, that is not guaranteed.

One solution is to use group_concat to concatenate each name in a group together. There should only be one name per group.

select
  chart_num,
  visit,
  group_concat(chart_name) as chart_name,
  sum(card_amount_received) as card_amount_received
from table1
group by chart_num, visit

However, the proper solution is fix the schema. chart_name is duplicated, and that's to be avoided. Instead, move the chart columns into their own table. Then to get the chart name, join on the chart_num.

create table charts (
  id serial primary key,
  name varchar(255) not null
);

insert into charts (id, name) values
  (4, 'user1'), (5, 'user2'), (6, 'user3'), (7, 'user4');

alter table table1 drop column chart_name;

select
  charts.id as chart_num,
  visit,
  charts.name as chart_name,
  sum(card_amount_received) as card_amount_received
from table1
join charts on charts.id = chart_num
group by chart_num, visit

2. Compare the visit and card_count_received of the result value of number 1 with the card_date and the advanced_amount values of no.2_table.

We need a left join with the second table matching the card_date with the visit. A left join means all the rows in the "left" table (ie. the from table) will always appear even if there is no match in the join table.

visit is a date. card_date is not a date but a timestamp. To match them we'll need to convert card_date to a date.

select
  charts.id as chart_num,
  visit,
  charts.name as chart_name,
  sum(card_amount_received) as card_amount_received,
  table2.card_date,
  table2.advanced_amount as amount
from table1
join charts on charts.id = chart_num
left join table2 on date(table2.chart_date) = visit
group by chart_num, visit

If the value of chart_num and visit of no.1_table is the same as the card_date of no.2_table and the added_count value of no.2_table, ok otherwise output error with null processing.

We need to compare advanced_amount with sum(card_amount_received). If they're equal: ok. If not: error. In standard SQL we'd use a case, but MariaDB has a non-standard if that is much more compact.

select
  charts.id as chart_num,
  visit,
  charts.name as chart_name,
  sum(card_amount_received) as card_amount_received,
  table2.card_date,
  table2.advanced_amount as amount,
  if(table2.advanced_amount = sum(card_amount_received), 'ok', 'error') as result
from table1
join charts on charts.id = chart_num
left join table2 on date(table2.chart_date) = visit
group by chart_num, visit

这篇关于比较两个表后如何输出匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！