mysql：公用表表达式中有多个SELECT语句 [英] mysql : multiple SELECT statements in Common Table Expression

问题描述

需要将乘法选择组合到一个查询中。

In need to combine multipe selects into one query.

MariaDB [aix_registry]> SELECT n.name AS WBG from entries e LEFT JOIN nodes n on n.id=e.node_id LEFT JOIN attribs a on a.id=e.attrib_id WHERE  a.name= 'LOCATION' AND e.value='Wienerberg' AND DATE(ts) = CURDATE() LIMIT 5;
+--------------------+
| WBG                |
+--------------------+
| KUG01171_TQLENTW03 |
| AIXSAWBG3          |
| AIXAPPL1EDUC       |
| KUG0114_DDAITAATU  |
| AIXSAPP03C1_HA     |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [aix_registry]> SELECT n.name AS LNZ from entries e LEFT JOIN nodes n on n.id=e.node_id LEFT JOIN attribs a on a.id=e.attrib_id WHERE  a.name= 'LOCATION' AND e.value='Gruberstrasse' AND DATE(ts) = CURDATE() LIMIT 5;
+-------------------+
| LNZ               |
+-------------------+
| ARR5S1P8_OOEGKKPR |
| AIXSAGRU2         |
| AIXSTP11R3DB      |
| STP17T2_SGKKT2    |
| ARR5S1P9_TIC      |
+-------------------+
5 rows in set (0.001 sec)

输出应如下所示，并且该解决方案应可扩展到任意数量的查询。这两个只是
的例子。

output should look like this, and the solution should be expandable to any count of query. the two here are just examples.

+-------------------+--------------------+
| LNZ               | WBG                |
+-------------------+--------------------+
| ARR5S1P8_OOEGKKPR | KUG01171_TQLENTW03 | 
| AIXSAGRU2         | AIXSAWBG3          | 
| AIXSTP11R3DB      | AIXAPPL1EDUC       |
| STP17T2_SGKKT2    | KUG0114_DDAITAATU  |
| ARR5S1P9_TIC      | AIXSAPP03C1_HA     |
+-------------------+--------------------+

推荐答案

WITH
cte AS ( SELECT n.name,
                e.value, 
                ROW_NUMBER() OVER (PARTITION BY e.value 
                                   ORDER BY {expression-1}) AS rn
         from entries e 
         LEFT JOIN nodes n on n.id=e.node_id 
         LEFT JOIN attribs a on a.id=e.attrib_id 
         WHERE  a.name = 'LOCATION' 
           AND e.value IN ('Wienerberg', 'Gruberstrasse')
           AND DATE(ts) = CURRENT_DATE
         ORDER BY {expression-2}
       ),
nums AS ( SELECT 1 rn UNION 
          SELECT 2 UNION 
          SELECT 3 UNION 
          SELECT 4 UNION 
          SELECT 5
        )
SELECT t1.name LNZ, t2.name WBG
FROM nums
LEFT JOIN cte t1 ON nums.rn = t1.rn
LEFT JOIN cte t2 ON nums.rn = t2.rn
WHERE t1.value = 'Gruberstrasse'
  AND t2.value = 'Wienerberg'
--  AND COALESCE(t1.name, t2.name)
ORDER BY nums.rn 

这篇关于mysql：公用表表达式中有多个SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！