如何在SQL查询中显示多个表中的列?
我试图显示从我的查询中有3个表中的一个列中的列,但我似乎无法弄清楚如何让它正常工作。无论我尝试什么,它似乎都不起作用。这似乎很容易,但也许我错过了一些非常简单的事情。以下是我有:如何在SQL查询中显示多个表中的列?
SELECT hpg.id_number,
hpg.id_description,
COUNT (hdd.number_of_games) games,
SUM (NVL (price, 0)) total,
SUM (number_of_food) food
--I tried adding column here, but nothing seemed to work
FROM (SELECT number_of_games, price, prints
FROM (hopeful_dog_hops)
WHERE status = 'Done') hdd,
(SELECT SUM (number_of_food) number_of_food, number_of_games
FROM hot_digity_dog
GROUP BY number_of_games) hdd1,
(SELECT id_description, sort_types, id_number
FROM (hot_pick_games)
WHERE disabled = 'TRUE' AND viewable_type = 'OK') hpg
WHERE hdd.prints(+) = hpg.id_number
AND hdd.number_of_games = hdd1.number_of_games
GROUP BY hpg.id_description, hpg.id_number, sort_types
ORDER BY sort_types
这个巨大的查询显示这些列:
ID_NUMBER ID_DESCRIPTION GAMES TOTAL FOOD
我需要增加一个列:从hot_digity_dog表
TEST
。
不要太担心所有事情的名称,但我只需要弄清楚我到底能够在查询中显示另一列。我试着将TEST添加到第一个SELECT语句中,但它给了我一个错误:“TEST:无效标识符”。
我知道查询的工作原理(没有添加TEST部分,所以它不能成为查询中的错误)。它必须是新增加的部分。
任何帮助将不胜感激。提前致谢。
这里是新列的查询:
SELECT hpg.id_number,
hpg.id_description,
COUNT (hdd.number_of_games) games,
SUM (NVL (price, 0)) total,
SUM (number_of_food) food,
hdd.TEST
FROM (SELECT number_of_games, price, prints
FROM (hopeful_dog_hops)
WHERE status = 'Done') hdd,
(SELECT SUM (number_of_food) number_of_food, number_of_games
FROM hot_digity_dog
GROUP BY number_of_games) hdd1,
(SELECT id_description, sort_types, id_number
FROM (hot_pick_games)
WHERE disabled = 'TRUE' AND viewable_type = 'OK') hpg
WHERE hdd.prints(+) = hpg.id_number
AND hdd.number_of_games = hdd1.number_of_games
GROUP BY hpg.id_description, hpg.id_number, sort_types
ORDER BY sort_types
这里是列标题应该是什么样子:
ID_NUMBER ID_DESCRIPTION GAMES TOTAL FOOD TEST
的问题是,你是不是从hot_digity_dog
的选择外部查询。您可以从基于该表的AGGREGATE子查询中进行选择 - 您以别名hdd1
作为别名的子查询。所以,目前尚不清楚如何你想添加那个列。 test
完全由number_of_games
决定?如果是,则可以将test
添加到聚合子查询的select
和group by
子句中,然后可以在外部查询中选择它。
如果test
不是由number_of_games
确定,你要总结number_of_food
由number_of_games
和test
为分组?如果是这样,再次在子查询中添加test
到select
和group by
(但结果将单独由number_of_games
分组)。
如果test
不是由number_of_games
确定,要通过组为number_of_games
的总和,但要显示test
无论如何,你可能需要使用分析sum()
而不是总sum()
。
这只是一个小例子,向您证明您发布的内容不足以让我们来帮助您。请提供(更多)更多细节 - 通过编辑原始问题,而不是在评论中。
我认为你只是缺少在HDD查询测试柱:
SELECT hpg.id_number,
hpg.id_description,
COUNT (hdd.number_of_games) games,
SUM (NVL (price, 0)) total,
SUM (number_of_food) food,
hdd.TEST
FROM (SELECT number_of_games, price, prints, TEST
FROM (hopeful_dog_hops)
WHERE status = 'Done') hdd,
(SELECT SUM (number_of_food) number_of_food, number_of_games
FROM hot_digity_dog
GROUP BY number_of_games) hdd1,
(SELECT id_description, sort_types, id_number
FROM (hot_pick_games)
WHERE disabled = 'TRUE' AND viewable_type = 'OK') hpg
WHERE hdd.prints(+) = hpg.id_number
AND hdd.number_of_games = hdd1.number_of_games
GROUP BY hpg.id_description, hpg.id_number, sort_types
ORDER BY sort_types
请出示您添加其他列的查询。 –
尝试添加适当的数据样本和预期结果 – scaisEdge
您从不选择hdd查询中的TEST列。在那里选择它,以及'number_of_games,price,prints',我怀疑它会起作用。 – Zorkolot