具有正确顺序的两个查询的联合结果 [英] Union Result of two queries with proper order

问题描述

我的数据库中有两个表表 A 和表 B -

I have two tables in my database Table A And Table B -

                   Table `A`
    SN   |    Order1    |   Order2   |   Text
 (INT)   |   (TINYINT)  |  (TINYINT) |  (VARCHAR)
   1001  |      1       |      1     |    ABC
   1001  |      1       |      2     |    DEF
   1001  |      1       |      3     |    GHI
   1001  |      2       |      1     |    IOU
   1001  |      3       |      1     |    JKL     <--
   1001  |      3       |      2     |    LMO
   1001  |      3       |      3     |    UTF
   ....
   1021  |      1       |      1     |    ZXC
   1021  |      1       |      2     |    QWE
   1021  |      2       |      1     |    JKL     <--
   1021  |      3       |      1     |    YOU

在另一个表中

                   Table `B`
    SN   |    Order1    |     rSN    |   rOrder1
   1021  |      2       |     1001   |      3

现在当我查询 1021 的数据时，结果应该是:-

Now when I query for the data of 1021 the result should be like :-

         Result Needed

    1    |      1     |    ZXC
    1    |      2     |    QWE
    2    |      1     |    JKL
    2    |      2     |    LMO
    2    |      3     |    UTF
    3    |      1     |    YOU

<小时>

目前我正在尝试这样的事情 -

---

Presently I am trying somthing Like this -

SELECT `SN`, `Order1`, `Order2`, `Text` FROM `Table A` 
  WHERE `SN`=1012 
UNION  
SELECT `SN`, `Order1`, `Order2`, `Text` FROM `Table A`  
  WHERE `Table A`.`SN` 
  IN (SELECT  `rSN` FROM `Table B` WHERE `SN`=1021) 
  AND `Table A`.`Order1` 
  IN (SELECT  `Order1` FROM `Table B` WHERE `SN`=1021) 

给出这样的结果:-

             Result
  SN | Order1 |   Order2   |    Text
 1021|   1    |      1     |    ZXC
 1021|   1    |      2     |    QWE
 1021|   2    |      1     |    JKL
 1021|   3    |      1     |    YOU
 1001|   3    |      1     |    JKL
 1001|   3    |      2     |    LMO
 1001|   3    |      3     |    UTF

我应该怎么做才能使结果的最后三行的 Order1 和 Order2 与引用行相同，即 1001 |2 |2 |改性活生物体

What should I do to get the Order1 and Order2 of the last three rows of the result be same as the referring Row i.e. like 1001 | 2 | 2 | LMO

编辑---

这里我想使 1001 的 Order1 值与 1012 的 Order1 相同当查询给出数据输出时.

Here I am Thinking to get the Order1 values of the 1001 to be same as Order1 of 1012 when query gives the data output.

Text 的顺序很重要.

Order2 文本与其在该 Group In Order1 中对应的第一个值相关而Table B 存储对数据库中已经输入的重复相关Text的引用，并定义其在对应的SN

The Order2 Text is related to its corresponding first value in this Group In Order1 And Table B Stores reference to the already entered duplicate related Text in the database and defines its position in the corresponding SN

推荐答案

我自己找到了解决方案 :-

I found the solution myself : -

解决方案

SELECT `Order1`, `Order2`, `Text` FROM `Table A` WHERE `SN` = 1012
UNION DISTINCT
SELECT `Table B`.`Order1`,  `Table A`.`Order2`, `Table A`.`Text` 
FROM  `Table A` JOIN  `Table B` 
WHERE  `Table B`.`rSN` =  `Table A`.`SN` 
      AND  `Table B`.`Order1` =  `Table A`.`Order1` 
      AND `Table B`.`SN` = 1021
ORDER BY `Order1`, `Order2`

说明

第一个 SELECT 选择与 1021

在第二部分查询中，我想要被引用的SN即1001的行的Text具有 Order1 值 3(见表 B).
但作为参考SN 即1021 有它的 Order1 值 2，它的 Text 是 JKL(见表 A，指出具有 1021 SN 的行 ("<--")
所以我们想要与 1001 关联的 Text 的 Order1 的所有值 |3 | 格式化为 1001 |2 |
对于上述问题 JOIN 有效，Joining 给出了一个表，如:-

In Second Part of query, I wanted the Text Of the referred SN i.e. 1001's row's having Order1 value 3(See Table B).
But as the referring SN i.e. 1021 have its Order1 value 2, WHERE its Text is JKL (See Table A, Pointed Row ("<--") with 1021 SN)
So We want All the values of Order1 of Text associated with 1001 | 3 | Formatted As 1001 | 2 |
For this above problem JOIN Works, Joining gives a table like :-

SELECT *  FROM  `Table A` JOIN  `Table B` 
WHERE  `Table B`.`rSN` =  `Table A`.`SN` 
  AND  `Table B`.`Order1` =  `Table A`.`Order1` 
  AND `Table B`.`SN` = 1021

            Table `B`                            Table `A`
 SN  | Order1 |  rSN | rOrder1     ||    SN | Order1 | Order2 | Text
1021 |   2    | 1001 |    3        || 1001  |    3   |    1   | JKL 
1021 |   2    | 1001 |    3        || 1001  |    3   |    2   | LMO
1021 |   2    | 1001 |    3        || 1001  |    3   |    3   | UTF

并且从这个结果中我们可以选择结果集.
理解 2, 2, LMO 是如何来的 -> 它在这里 现在我们从 JOIN 结果中取出 Table B.Order1 而不是像早些时候 我们选择了 1001 中的 Order1，然后导致我们不想要的排序.

And From this result we can Pick up the result set.
Understanding how 2, 2, LMO comes -> It is here Now we are picking up the Table B.Order1 From the JOIN result Instead like earlier we were Picking up the Order1 Of the 1001 instead, which then results into ordering which we didn't wanted.

为什么不需要 1、3、GHI 作为结果的一部分 -> 相关信息，
具有相同Order1的信息相互显示相关的词/信息，并且由于1021没有任何与1,3,GHI相关的信息因此，结果部分不需要.

why isn't 1, 3, GHI required as a part of result -> As Its Like Related Information,
The Information with same Order1 show the related words/information to each other and since 1021 Do not have any related information to 1, 3, GHI Hence It is not required in the result part.

现在我们已经获得了第二个查询的 Order1 应该是 2 而不是 3 我们现在可以轻松地订购它们按订单条款

And as Now we already getting the Order1 of the second query same as it should be 2 not 3 We can now easily Order Them by Order clause

我希望这也能澄清这个问题.还是谢谢大家:)

I hope this will clarify the question also. Still Thanks everyone :)

------ 编辑-------

DISTINCT 在 UNION 中会从最终结果中去除 JKL 的 Duplicate Entry

DISTINCT in UNION will remove the Duplicate Entry of JKL From the final results

这篇关于具有正确顺序的两个查询的联合结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！