MySQL将视图列从BIGINT更改为BIT [英] MySQL change view column from BIGINT to BIT

问题描述

我有一张这样的桌子.

(BIGINT)     (BIGINT)
PLAYER_ID    FRIEND_ID
----------------------
1            2
2            1
1            3

我认为我可以使用以下方式查看

I think I can make a View as

SELECT * FROM FRIEND F1 LEFT OUTER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID

打印

F1.PLAYER_ID    F1.FRIEND_ID    F2.PLAYER_ID    F2.FRIEND_ID
[BIGINT]        [BIGINT]        [BIGINT]        [BIGINT]
------------------------------------------------------------
1               2               2               1
2               1               1               2
1               3               NULL            NULL

我可以制作一个视图来表示那些F2作为BIT的存在吗?

Can I make a View which represents those F2's existence AS BIT?

F1.PLAYER_ID    F1.FRIEND_ID    BACK
[BIGINT]        [BIGINT]        [BIT]
-------------------------------------
1               2               1     // exist (1 -> 2)
2               1               1     // exist (2 -> 1)
1               3               0     // no backing relation (3 -> 1)

更新---------------------------------------------- --------------------

UPDATE ------------------------------------------------------------------

对不起，上面的查询可能并不完美.

Sorry the above query wan't perfect.

带有@Romil答案的查询必须这样写.

The query, with @Romil 's answer, must be written like this.

SELECT
    F1.PLAYER_ID,
    F1.FRIEND_ID,
    CASE WHEN F2.FRIEND_ID IS NULL THEN 0 ELSE 1 END AS BACK
FROM FRIEND F1
    LEFT OUTER JOIN FRIEND F2
        ON F1.FRIEND_ID = F2.PLAYER_ID AND F1.PLAYER_ID = F2.FRIEND_ID

推荐答案

SELECT F1.PLAYER_ID, 
       F2.PLAYER_ID, 
       CASE 
         WHEN F2.PLAYER_ID IS NULL THEN 0 
         ELSE 1 
       end AS Back 
FROM   FRIEND F1 
       LEFT OUTER JOIN FRIEND F2 
                    ON F1.FRIEND_ID = F2.PLAYER_ID 

这篇关于MySQL将视图列从BIGINT更改为BIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！