MySQL结合了两个表的结果

this is my situation:

这是我的情况:

i have two tables A and B and want to combine the results from A and B. probably with word is complicated to explain but using table result to illustrate it is more simple to understand.

我有两个表A和B,并希望结合A和B的结果。可能用单词很难解释,但使用表结果来说明它更容易理解。

Code for table A

表A的代码

CREATE TABLE `A` (
  `id_doc_a` int(11) NOT NULL AUTO_INCREMENT,
  `subject_a` varchar(100) DEFAULT NULL,
  `doc_nr_a` varchar(100) NOT NULL,
  `doc_type_a` varchar(50) NOT NULL,
  `doc_date_a` date NOT NULL,
  `value_IN` varchar(50) NOT NULL,
  PRIMARY KEY (`id_doc_a`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Code for table B

表B的代码

CREATE TABLE `B` (
  `id_doc_b` int(11) NOT NULL AUTO_INCREMENT,
  `subject_b` varchar(100) DEFAULT NULL,
  `doc_nr_b` varchar(100) NOT NULL,
  `doc_type_b` varchar(50) NOT NULL,
  `doc_date_b` date NOT NULL,
  `value_OUT` varchar(50) NOT NULL,
  PRIMARY KEY (`id_doc_b`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I want to select all subjects_a and subjects_b with value (ex. JOHN) and combine in a single table with columns:

我想选择所有subject_a和subject_b的值(例如JOHN),并在一个表中与列组合:

| SUBJECT | DOC_NR | DOC_TYPE | DOC_DATE    | TBL_A_VALUE_IN | TBL_B_VALUE_OUT |
|---------|--------|----------|-------------|----------------|-----------------|
| JOHN    | 1      | PDF      | 20018-03-16 | 50.00          | NULL OR 0.00    |
| JOHN    | 33     | PDF      | 20018-08-09 | 25.00          | NULL OR 0.00    |
| JOHN    | 13     | PDF      | 20018-07-29 | 32.50          | NULL OR 0.00    |
| JOHN    | 4      | PDF      | 20018-06-06 | 41.30          | NULL OR 0.00    |
| JOHN    | 12     | JPEG     | 20018-04-22 | NULL OR 0.00   | 78.20           |
| JOHN    | 31     | JPEG     | 20018-03-21 | NULL OR 0.00   | 6.50            |
| JOHN    | 33     | JPEG     | 20018-05-01 | NULL OR 0.00   | 26.99           |

where the first 4 rows are the result from Table A and the last 3 rows are result of Table B where the condition is SUBJECT (subject_a = subject_b) = JOHN.

其中前4行是表A的结果,最后3行是表B的结果,其中条件是SUBJECT(subject_a = subject_b)= JOHN。

Hope to have explained well, thanks for the help.

希望能够解释得很好,谢谢你的帮助。

Solution: Thanks to @scaisEdge

解决方案:感谢@scaisEdge

 SELECT 
     t.SUBJECT_NAME, 
     t.DOCUMENT_NR, 
     t.DOCUMENT_TYPE, 
     t.DOCUMENT_DATE, 
     t.TBL_A_VALUE_IN, 
     t.TBL_B_VALUE_OUT
FROM (
     SELECT 
           SUBJECT_a AS SUBJECT_NAME, 
           DOC_NR_a AS DOCUMENT_NR, 
           DOC_TYPE_a AS DOCUMENT_TYPE, 
           DOC_DATE_a AS DOCUMENT_DATE,
           VALUE_IN AS TBL_A_VALUE_IN, 
           NULL AS TBL_B_VALUE_OUT
     FROM a 
  UNION ALL
     SELECT  
           SUBJECT_b AS SUBJECT_NAME, 
           DOC_NR_b AS DOCUMENT_NR, 
           DOC_TYPE_b AS DOCUMENT_TYPE, 
           DOC_DATE_b AS DOCUMENT_DATE,
           NULL AS TBL_A_VALUE_IN,
           VALUE_OUT AS TBL_B_VALUE_OUT
     FROM b
) T 
  WHERE t.SUBJECT_NAME = 'john'
  ORDER BY t.DOCUMENT_DATE

i removed 0 tab_order and 1 and use aliases so it can be more readable. For order can be used one of the columns on the first select and to add more condition's just use AND or if dont need NULL we can use 0.00 AS instead of NULL AS in both selects.

我删除了0 tab_order和1并使用别名,因此它更具可读性。对于顺序可以使用第一个选择中的一个列,并添加更多条件只需使用AND或如果不需要NULL,我们可以在两个选择中使用0.00 AS而不是NULL AS。

Thanks again to scaisEdge for help and patience.

再次感谢scaisEdge的帮助和耐心。

2 个解决方案

#1

you could use UNION ALL

你可以使用UNION ALL

   select   t.SUBJECT, t.DOC_NR, t.DOC_TYPE, t.DOC_DATE 
       , t.TBL_B_VALUE_IN,t.TBL_B_VALUE_OUT
    from (

    select   SUBJECT_a subject, DOC_NR_a DOC_NR, DOC_TYPE_a DOC_TYPE, DOC_DATE_a  DOC_DATE
         , VALUE_IN TBL_B_VALUE_IN, null TBL_B_VALUE_OUT , 0 tab_order 
    from table_a 
    union all
    select   SUBJECT_b, DOC_NR_b, DOC_TYPE_b, DOC_DATE_b , null,VALUE_OUT , 1  
    from table_b
    ) T 
    order by t.tab_order

#2

You can just JOIN the tables.

你可以加入表格。

SELECT *
FROM A
    JOIN B on A.subject_a = B.subject_b
WHERE A.subject_a = 'JOHN'

That will combine both datasets into one result table. Of course, if you don't want duplicate values (i.e. id_doc_a and id_doc_b) you can specify which ones you want instead of the wildcard *

这会将两个数据集合并为一个结果表。当然,如果你不想要重复的值(即id_doc_a和id_doc_b),你可以指定你想要的那些而不是通配符*

#1