我在SQLite Management Studio ( 2009版)中运行以下查询,它工作正常并返回所需的结果集,但当我从C#执行时,它给出以下错误:
没有这样的列Q0.IntegrationItemCategoryLevelID。
它似乎不能“看到”子查询中的表别名-我做了一些进一步的测试,它也看不到其他连接的表(例如Q0)。我试着将它分成两个查询,但这绝对会降低性能。有没有人有解决这个问题的好主意?
SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc
FROM ((_Item I
INNER JOIN (_ItemToItemCategory Q0
INNER JOIN _ItemCategory A0 ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID) ON Q0.IntegrationItemID = I.IntegrationItemID)
INNER JOIN (_ItemToItemCategory Q1
INNER JOIN _ItemCategory A1 ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID) ON Q1.IntegrationItemID = I.IntegrationItemID)
WHERE Q0.IntegrationItemCategoryLevelID = 14 AND A0.ShortDesc = 'LG05'
AND Q1.IntegrationItemCategoryLevelID IN (9,4,5,7,10) ORDER BY Q1.IntegrationItemCategoryLevelID
编辑:去掉多余的括号,同样的结果。
SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc
FROM Item I
INNER JOIN (ItemToItemCategory Q0
INNER JOIN ItemCategory A0 ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID) ON Q0.IntegrationItemID = I.IntegrationItemID
INNER JOIN (ItemToItemCategory Q1
INNER JOIN ItemCategory A1 ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID) ON Q1.IntegrationItemID = I.IntegrationItemID
WHERE Q0.IntegrationItemCategoryLevelID ='14' AND A0.ShortDesc = 'LG05'
AND Q1.IntegrationItemCategoryLevelID IN (9,4,5,7,10) ORDER BY Q1.IntegrationItemCategoryLevelID
发布于 2018-05-30 10:05:41
这是您的查询:
SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc
FROM ((_Item I INNER JOIN
(_ItemToItemCategory Q0 INNER JOIN
_ItemCategory A0
ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID
)
ON Q0.IntegrationItemID = I.IntegrationItemID
) INNER JOIN
(_ItemToItemCategory Q1 INNER JOIN
_ItemCategory A1
ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID
)
ON Q1.IntegrationItemID = I.IntegrationItemID
)
WHERE Q0.IntegrationItemCategoryLevelID = 14 AND
A0.ShortDesc = 'LG05' AND
Q1.IntegrationItemCategoryLevelID IN (9, 4, 5, 7, 10)
ORDER BY Q1.IntegrationItemCategoryLevelID;
很难说确切的错误是什么。我认为这是因为对于最后一个JOIN
,I
被定义在一个太多的嵌套级别上。
但是,这些都是内部连接。而且您没有使用MS Access,因此您可以很好地重新排列它们:
SELECT DISTINCT Q1.IntegrationItemCategoryLevelID, A1.ShortDesc
FROM _Item I INNER JOIN
_ItemToItemCategory Q0
ON Q0.IntegrationItemID = I.IntegrationItemID INNER JOIN
_ItemCategory A0
ON A0.IntegrationItemCategoryID = Q0.IntegrationItemCategoryID INNER JOIN
_ItemToItemCategory Q1
ON Q1.IntegrationItemID = I.IntegrationItemID INNER JOIN
_ItemCategory A1
ON A1.IntegrationItemCategoryID = Q1.IntegrationItemCategoryID
WHERE Q0.IntegrationItemCategoryLevelID = 14 AND
A0.ShortDesc = 'LG05' AND
Q1.IntegrationItemCategoryLevelID IN (9, 4, 5, 7, 10)
ORDER BY Q1.IntegrationItemCategoryLevelID;
如果你有外部连接,重新排列它们就不是那么容易了。
https://stackoverflow.com/questions/50595258
复制相似问题