如何将两个SQL SELECT语句结合
我有两个SQL SELECT语句,说如何将两个SQL SELECT语句结合
SELECT TOP (150) FoodID, COUNT(*) AS NumberOfFood FROM FoodTable WHERE FoodID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY FoodID ORDER BY NumberOfFood DESC
我也有另外一个SQL语句
SELECT FoodSellerID, Market1, SellerLastName, SellerFirstName, PrimaryAddress1, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryZip FROM SellerTable, MarketTable
WHERE Market1= MarketTable.MarketID
几个音符指出,FoodID查找到SellerTable,市场1查找到MarketTable。我的问题是我怎样才能结合并返回表格,如
FoodID |食物数量| SellerLastName | SellerFirstName | PrimaryAddress1 | PrimaryAddress2 | PrimaryCity | PrimaryState |基于FoodID查找您的模糊描述SellerTable PrimaryZip
曼尼,你将无法加入这两个没有共同领域的两个查询。如果你有一个共同的领域,那么你可以建立每个临时表并加入第三个查询。它看起来像FoodTable中的'Food ID'(来自第一个查询)。如果存在于SellerTable上,那么您可以加入这两个(并且加入MarketTable以从该表中获取行)。
这将是这个样子:
create temp table food_nums_temp as
SELECT TOP (150) FoodID, COUNT(*) AS NumberOfFood
FROM FoodTable
WHERE FoodID IS NOT NULL
AND FoodDate >= '2015-10-01'
GROUP BY FoodID
ORDER BY NumberOfFood DESC
;
Create temp table seller_temp as
SELECT FoodSellerID, FoodID,
Market1,
SellerLastName,
SellerFirstName,
PrimaryAddress1,
PrimaryAddress2,
PrimaryCity,
PrimaryState,
PrimaryZip
FROM SellerTable, MarketTable
WHERE Market1= MarketTable.MarketID
;
select a.*, b.SellerLastName
, b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2
, b.PrimaryCity, b.PrimaryState, b.PrimaryZip
from food_nums_temp a
inner join seller_temp b on a.FoodID = b.FoodID
编辑:明知完全DB访问(创建临时表)是不允许的,允许使用“与”(又名公共表表达式或CTE),以现阶段的数据在我们所需的查询之前:
with
food_nums_temp as (
SELECT TOP (150) FoodID, COUNT(*) AS NumberOfFood
FROM FoodTable
WHERE FoodID IS NOT NULL
AND FoodDate >= '2015-10-01'
GROUP BY FoodID
ORDER BY NumberOfFood DESC
),
seller_temp as
SELECT FoodSellerID, FoodID,
Market1,
SellerLastName,
SellerFirstName,
PrimaryAddress1,
PrimaryAddress2,
PrimaryCity,
PrimaryState,
PrimaryZip
FROM SellerTable, MarketTable
WHERE Market1= MarketTable.MarketID
)
select a.*, b.SellerLastName
, b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2
, b.PrimaryCity, b.PrimaryState, b.PrimaryZip
from food_nums_temp a
inner join seller_temp b on a.FoodID = b.FoodID
请注意,我没有访问完整的数据库,我只看到,我试图从三个不同的表中拉出数据.... –
曼尼,我编辑答案使用通用表表达式(CTE,完成使用关键字'WITH')在查询之前将数据分级。请参阅上面的内容。 – kltft
感谢您的帮助,最终得到它的工作。 –
,试试这个
SELECT FoodSellerID,
Market1,
SellerLastName,
SellerFirstName,
PrimaryAddress1,
PrimaryAddress2,
PrimaryCity,
PrimaryState,
PrimaryZip
FROM SellerTable ST
INNER JOIN MarketTable MT
ON ST.Market1 = MT.MarketID
inner join
(
SELECT FoodID, COUNT(*) AS NumberOfFood
FROM FoodTable
WHERE FoodID IS NOT NULL
AND FoodDate >= '2015-10-01'
GROUP BY FoodID
) a1
on a1.FoodID = ST.FoodID
感谢评论我最终得到它的工作。 –
尝试这样的:
SELECT TOP (150) a.FoodID, COUNT(*) AS a.NumberOfFood,b.SellerLastName, b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2, b.PrimaryCity, b.PrimaryState, b.PrimaryZipFROM FoodTable a inner join SellerTable b on a.FoodID = b.FoodSellerID WHERE a.Market1= b.MarketTable.MarketID IS NOT NULL AND FoodDate >= '2015-10-01' GROUP BY a.FoodID, b.SellerLastName, b.SellerFirstName, b.PrimaryAddress1, b.PrimaryAddress2, b.PrimaryCity,
b.PrimaryState, b.PrimaryZip ORDER BY a.NumberOfFood DESC
注:可能是另一种解决方案。
请格式化您的查询,现在完全无法读取。 – HoneyBadger
请仅标记相关的DBMS – JohnHC
您应该开始使用ANSI-92样式的连接......它们现在已经使用了超过25年。这意味着使用join关键字,而不是在where语句中使用equals谓词。 –