mysql - 使用 UNION 或任何其他解决方案将两个查询合并为一个

Question

查询 1 以获取访问用户的喜欢/不喜欢或其他事件的历史记录:FIDDLE

所有产品记录的查询 2:FIDDLE

第一个查询将为该特定用户生成此结果:

NAME    MODEL   ISBOOKED    USER_MIN    USER_MAX    USER_LIKE_TYPE
Tom     TOYA    1           100         300         0 
Tom     TOYB    1           (null)      (null)      1
Tom     ToyC    (null)      (null)      (null)      (null)

第二个查询会给出所有产品的记录

MODEL   RESALE_USERSNO  AVG_MIN AVG_MAX LIKES   DISLIKES
ToyA    1               100     300     1       0 
ToyB    0               (null)  (null)  1       1
ToyC    1               50      400     (null)  (null)

是否可以将用户记录和一般产品的记录合二为一:

MODEL RESALE_USERSNO AVG_MIN AVG_MAX LIKES DISLIKES NAME ISBOOKED USER_MIN USER_MAX USER_LIKE_TYPE
ToyA    1            100     300     1      0       Tom   1        100       300       0 
ToyB    0            (null) (null)   1      1       Tom   1        (null)    (null)    1
ToyC    1            50      400     (null) (null)  Tom   (null)    (null)    (null)   (null)

这是我使用 UNION 的尝试(FIDDLE)，但它似乎不起作用

查询 1

SELECT a.NAME,b.MODEL,c.ISBOOKED,d.MIN_ AS USER_MIN,
       d.MAX_ AS USER_MAX,e.LIKE_TYPE AS USER_LIKE_TYPE
     FROM `USER_ID` a
     JOIN `MODEL_ID` b
       ON b.MODEL_NUMBER IN ('AAA','BBB','CCC')
LEFT JOIN `BOOKMARK` c 
       ON c.USER_ID = a.USER_ID
      AND c.MODEL_ID = b.MODEL_ID         
LEFT JOIN `RESALE_POLL` d 
       ON d.USER_ID = a.USER_ID
      AND d.MODEL_ID = b.MODEL_ID 
LEFT JOIN `LIKE_DISLIKE` e
       ON e.USER_ID = a.USER_ID
      AND e.MODEL_ID = b.MODEL_ID
    WHERE a.FACEBOOK_ID = 'FB1111'

查询 2

SELECT b.MODEL,COUNT( c.USER_ID ) AS RESALE_USERSNO, 
        ROUND( AVG( c.MIN_ ) , 1 ) AS AVG_MIN, 
        ROUND( AVG( c.MAX_ ) , 1 ) AS AVG_MAX, 
        b.MODEL_ID, a.LIKES, a.DISLIKES
        FROM  `LIKES_TOTAL` a
        RIGHT JOIN  `MODEL_ID` b 
          ON a.MODEL_ID = b.MODEL_ID
        LEFT JOIN  `RESALE_POLL` c 
          ON c.MODEL_ID = b.MODEL_ID
        WHERE b.MODEL_NUMBER IN ('AAA','BBB','CCC')
        GROUP BY b.MODEL_NUMBER

失败的尝试:

SELECT b.MODEL,
       NULL AS MODEL_ID,
       a.NAME,
       NULL AS RESALE_USERSNO,
       NULL AS AVG_MIN,
       NULL AS AVG_MAX,
       NULL AS LIKES,
       NULL AS DISLIKES,
       d.MIN_ AS USER_MIN,
       d.MAX_ AS USER_MAX,
       e.LIKE_TYPE AS USER_LIKE_TYPE,
       c.ISBOOKED
     FROM `USER_ID` a
     JOIN `MODEL_ID` b
       ON b.MODEL_NUMBER IN ('AAA','BBB','CCC')
LEFT JOIN `BOOKMARK` c 
       ON c.USER_ID = a.USER_ID
      AND c.MODEL_ID = b.MODEL_ID         
LEFT JOIN `RESALE_POLL` d 
       ON d.USER_ID = a.USER_ID
      AND d.MODEL_ID = b.MODEL_ID 
LEFT JOIN `LIKE_DISLIKE` e
       ON e.USER_ID = a.USER_ID
      AND e.MODEL_ID = b.MODEL_ID
    WHERE a.FACEBOOK_ID = 'FB1111'

UNION

SELECT  b.MODEL,
        b.MODEL_ID,
        NULL AS NAME,
        COUNT( c.USER_ID ) AS RESALE_USERSNO, 
        ROUND( AVG( c.MIN_ ) , 1 ) AS AVG_MIN, 
        ROUND( AVG( c.MAX_ ) , 1 ) AS AVG_MAX,
        a.LIKES, 
        a.DISLIKES,
        NULL AS ISBOOKED,
        NULL AS USER_MIN,
        NULL AS USER_MAX,
        NULL AS USER_LIKE_TYPE
        FROM  `LIKES_TOTAL` a
        RIGHT JOIN  `MODEL_ID` b 
          ON a.MODEL_ID = b.MODEL_ID
        LEFT JOIN  `RESALE_POLL` c 
          ON c.MODEL_ID = b.MODEL_ID
        WHERE b.MODEL_NUMBER IN ('AAA','BBB','CCC')
        GROUP BY b.MODEL_NUMBER

Answer 1

使用Join 而不是union。将这 2 个结果视为单独的内部选项卡，并将它们加入模型代码以获得您想要的结果。

在这里 fiddle :http://sqlfiddle.com/#!2/6bfcb4/11

SELECT * FROM 
(
SELECT a.NAME,b.MODEL,c.ISBOOKED,d.MIN_ AS USER_MIN,
       d.MAX_ AS USER_MAX,e.LIKE_TYPE AS USER_LIKE_TYPE
     FROM `USER_ID` a
     JOIN `MODEL_ID` b
       ON b.MODEL_NUMBER IN ('AAA','BBB','CCC')
LEFT JOIN `BOOKMARK` c 
       ON c.USER_ID = a.USER_ID
      AND c.MODEL_ID = b.MODEL_ID         
LEFT JOIN `RESALE_POLL` d 
       ON d.USER_ID = a.USER_ID
      AND d.MODEL_ID = b.MODEL_ID 
LEFT JOIN `LIKE_DISLIKE` e
       ON e.USER_ID = a.USER_ID
      AND e.MODEL_ID = b.MODEL_ID
    WHERE a.FACEBOOK_ID = 'FB1111'
)TAB1

JOIN

(SELECT b.MODEL,COUNT( c.USER_ID ) AS RESALE_USERSNO, 
        ROUND( AVG( c.MIN_ ) , 1 ) AS AVG_MIN, 
        ROUND( AVG( c.MAX_ ) , 1 ) AS AVG_MAX, 
        b.MODEL_ID, a.LIKES, a.DISLIKES
        FROM  `LIKES_TOTAL` a
        RIGHT JOIN  `MODEL_ID` b 
          ON a.MODEL_ID = b.MODEL_ID
        LEFT JOIN  `RESALE_POLL` c 
          ON c.MODEL_ID = b.MODEL_ID
        WHERE b.MODEL_NUMBER IN ('AAA','BBB','CCC')
        GROUP BY b.MODEL_NUMBER
)TAB2

ON TAB1.MODEL = TAB2.MODEL