如何在主表中每条记录仅获得一行? [英] How can I get only one row per record in master table?

问题描述

在多表查询中，每条记录只能获得一行吗?

it is possible to get only one row per record in a multitable query?

我有这三个表:

ID | APPLE
----------
1  | RED
2  | YELLOW
3  | GREEN

FARMS

ID  | FARM
--------------------
B1  | GEORGE'S FARM
B2  | JOHN'S FARM

FARM_APPLES

FARM  | APPLE
---------------
B1    | 1
B1    | 2
B1    | 3
B2    | 1
B3    | 3

有了这个表，我需要这个结果:

With this tables I need this result:

FARM_NAME | APPLE_1 | APPLE_2 | APPLE_3
----------------------------------------
B1        | 1       | 2       | 3
B2        |1        |         | 3

非常感谢您的帮助.

编辑

感谢OMG Ponies和Bill，我将尽力尝试您的两种解决方案，这是最后一件事，它有可能获得以下结果:

Thanks both OMG Ponies and Bill, I'll try both of your solutions, just one last thing, its possible to get this result:

FARM          | RED | YELLOW | GREEN
-------------------------------------
GEORGE'S FARM | YES |  YES   |  YES
JOHN's FARM   | YES |  NO    |  YES

推荐答案

Firebird 2.0支持CASE表达式，因此您可以使用:

  SELECT fa.farm AS farm_name,
         MAX(CASE WHEN fa.apple = 1 THEN fa.apple ELSE NULL END AS apple_1,
         MAX(CASE WHEN fa.apple = 2 THEN fa.apple ELSE NULL END AS apple_2,
         MAX(CASE WHEN fa.apple = 3 THEN fa.apple ELSE NULL END AS apple_3,
    FROM FARM_APPLES fa
GROUP BY fa.farm

这篇关于如何在主表中每条记录仅获得一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！