代码之家  ›  专栏  ›  技术社区  ›  Ben

基于列值选择另一行

  •  0
  • Ben  · 技术社区  · 14 年前

    如果我有下面的查询选择论坛板,但是 board 表包含列 mirror_board_id mirror_board_id > 0 它会的 SELECT * FROM board WHERE board_id = mirror_board_id 或者别的什么。目前我使用2个查询来完成这项工作,但这有点查询密集。。。

    SELECT * FROM board WHERE category_id = 7 AND board_id = 0
    

    更新

    SELECT * FROM board WHERE category_id = 7 AND
                                (
                                        (
                                            mirror_board_id > 0
                                            AND board_id = mirror_board_id
                                        )
                                    OR board_id = 0
                                ) AND
                                display = 1 ORDER BY `order` ASC
    

    镜像板id>0

    1 回复  |  直到 14 年前
        1
  •  4
  •   D'Arcy Rittich    14 年前

    从逻辑上讲,它是:

    SELECT * 
    FROM board 
    WHERE (mirror_board_id > 0 and board_id = mirror_board_id) 
        or board_id = 0 
    

    SELECT * 
    FROM board 
    WHERE board_id = coalesce(mirror_board_id, 0)