mysql - 单行SQL联合

我正在尝试将一行合并到自身。
数据库有一个包含许多列（48列）的表，我想对其进行分组，以便您可以查看结果的列。

该数据库如下所示：

表格：RACEBETS

    RACENO|BETTER1|BET1|STAT1|BETTER2|BET2|STAT2|BETTER3|BET3|STAT3|BETTER4|...
    R01   |BT1    |200 |1    |BT2    |500 |1    |BT3    |500 |0    |BT4    |...

我目前有这个查询：

SELECT
    ITEMS.ITEM43, ITEMS.ITEM44, ITEMS.ITEM45, ITEMS.ITEM46
FROM
    (SELECT
         'TYPE1' AS ITEM43, RACEBETS.BETTER1 AS ITEM44,
         COALESCE(RACEBETS.BET1,'0') AS ITEM45,
         COALESCE(RACEBETS.STAT1,'0') AS ITEM46,
         RACEBETS.RACENO, '1' AS ORD
     FROM
         RACEBETS
     WHERE
         RACEBETS.BETTER1 IS NOT NULL

     UNION

     SELECT
         'TYPE1' AS ITEM43, RACEBETS.BETTER2 AS ITEM44,
         COALESCE(RACEBETS.BET2,'0') AS ITEM45,
         COALESCE(RACEBETS.STAT2,'0') AS ITEM46,
         RACEBETS.RACENO, '2' AS ORD
     FROM
         RACEBETS
     WHERE
         RACEBETS.BETTER2 IS NOT NULL

     UNION

     SELECT
         'TYPE2' AS ITEM43, RACEBETS.BETTER3 AS ITEM44,
          COALESCE(RACEBETS.BET3,'0') AS ITEM45,
          COALESCE(RACEBETS.STAT3,'0') AS ITEM46,
          RACEBETS.RACENO, '3' AS ORD
      FROM
          RACEBETS
      WHERE
          RACEBETS.BETTER3 IS NOT NULL

      UNION
         some more... ) ITEMS, SCHEDULE
INNER JOIN
    RACE ON SCHEDULE.SCHEDCODE = RACE.SCHEDCODE
WHERE
    SCHEDULE.SCHEDCODE = '01'
    AND RACE.RACENO = '001'
    AND ITEMS.RACENO = RACE.RACENO
ORDER BY
    ITEMS.ORD ASC

这显示了我想要的输出：

    ITEM43|ITEM44|ITEM45|ITEM46
    TYPE1 |BT1   |200   |1
    TYPE1 |BT2   |500   |1
    TYPE2 |BT3   |500   |0
    TYPE2 |.....

我相信我当前的查询将检索联合之前的所有行，并且会导致数据库运行缓慢。

问题：


从单一选择中合并是否有技巧，因为我将从单一选择中检索所有需要的文件？
如果在使用并集之前在单独的select语句中添加“ where子句”和“ join子句”会更有效吗？


谢谢！

最佳答案

我不会说这比您做的更好，但是它确实是从racebets中的一个SELECT操作的。

SELECT thebets.ITEM44,thebets.ITEM45,thebets.ITEM46
 FROM
(
SELECT
    CONCAT('TYPE',map.item) ITEM44,

CASE map.item
    WHEN '1' THEN
        racebets.better2
    WHEN '2' THEN
        racebets.better3
    END AS betkey,
CASE map.item
    WHEN '1' THEN
        COALESCE(RACEBETS.BET2,'0')
    WHEN '2' THEN
        COALESCE(RACEBETS.BET3,'0')
    END AS ITEM45,
CASE map.item
    WHEN '1' THEN
        COALESCE(RACEBETS.STAT2,'0')
    WHEN '2' THEN
    COALESCE(RACEBETS.STAT3,'0')
    END AS ITEM46

from racebets
join (
    select '1' item
    union all
    select '2'
) map
) thebets
WHERE thebets.betkey IS NOT NULL

几天前我从SO中获得了“技巧”，但现在找不到该帖子。它联接到派生表，该表用作取消取消枢转结果的映射。

关于mysql - 单行SQL联合，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29005642/