扩展旧查询 | FContainerPlats

本文介绍了扩展旧查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述在谈到SQL时，我又遇到了另一个问题，这次我想扩展一个我继承的查询，以增加一些功能。我不确定是否有可能以我尝试解决此问题的方式执行此操作但我尝试将此新请求的数据添加为select语句中的子查询。这是我想要添加到查询中的内容。从表格fcontainer中的id我必须做这个小步行来获得产品名称。选择 produkter.Produktnamn 来自 FContainer INNER JOIN $ b $bStämplingar ON FContainer.id =Stämplingar.ID INNER JOIN Tempo ON Stämplingar.temponr= Tempo.temponr INNER JOIN Produkter ON 速度。 produktnr = Produkter.Produktnr 我希望扩展的查询包含fcontainer，但仅限于另一个子查询。 SELECT FContainerHylla.Name，FContainerPlats.HyllId， FContainerPlats.x，FContainerPlats.y， FContainerPlats.enable，FContainerHylla。 Type ， ISNULL（（ SELECT MIN（fcontainer.id） AS id FROM FContainer WHERE （PlatsId = FContainerPlats.id））， 0 ） AS FcId FROM FContainerPlats INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id WHERE （FContainerHylla。类型 IN （ 1 ， 6 ， 10 ）） ORDER BY FContainerPlats.HyllId，FContainerPlats.x，FContainerPlats.y 到目前为止，我最好的结果是加入select语句中的第一个查询被（）包围，形成一个子查询，但是我得到一个错误，我不允许在子查询中有多个值。我需要通过添加where子句来减少可能的数据，我想添加其中 fcontainer.id = .... 我需要将fcontainer.id与isnull语句返回的值进行比较并定义AS id但不确定怎么做。解决方案你可以加入一个子查询，而不是把它放在select中。我想出了这个，但我无法测试它 SELECT FContainerHylla.Name，FContainerPlats.HyllId ， FContainerPlats.x，FContainerPlats.y， FContainerPlats.enable，FContainerHylla。 Type ， ISNULL（（ SELECT MIN（fcontainer.id） AS id FROM FContainer WHERE （PlatsId = FContainerPlats.id））， 0 ） AS FcId ，SubQ.Produktnamn FROM FContainerPlats INNER JOIN FContainerHylla ON FContainerPlats .HyllId = FContainerHylla.Id INNER JOIN （ SELECT FContainer.id AS SubID，produkter。 Produktnamn AS Produktnamn 来自 FContainer INNER JOIN $ b $bStämplingar ON FContainer.id =Stämplingar.ID INNER JOIN Tempo ON Stämplingar.temponr= Tempo。 temponr INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr）SubQ ON SubQ.SubID = FContainerPlats.id WHERE （FContainerHylla。 Type IN （ 1 ， 6 ， 10 ）） ORDER BY FContainerPlats。 HyllId，FContainerPlats.x，FContainerPlats.y 我可能在内部联接上的键SubQ错了。或者你可以将它们设置为两个公用表格表并以这种方式加入它们（再次这是未经测试的，并注意我在连接中使用的id）; WITH CTE1 AS （ SELECT FContainerHylla.Name，FContainerPlats.HyllId， FContainerPlats.x，FContainerPlats.y， FContainerPlats.enable，FContainerHylla.Type， ISNULL（（SELECT MIN（fcontainer.id）AS id FROM FContainer WHERE（PlatsId = FContainerPlats.id）），0）AS FcId FROM FContainerPlats INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id WHERE（ FContainerHylla.Type IN（1,6,10）））， CTE2 AS （ SELECT FContainer.id AS SubID，produkter.Produktnamn AS Produktnamn from FContainer INNERJOINStämplingarONFContainer.id =Stämplingar.ID INNER JOIN TempoONStämplingar.temponr= Tempo.temponr INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr ） SELECT CTE1。*，CTE2.Produktnamn FROM CTE1 INNER JOIN CTE2 ON CTE2.SubID = FContainerPlats.id ORDER BY FContainerPlats.HyllId，FContainerPlats.x，FContainerPlats。 ÿ I've ran in to yet another brick wall when it comes to SQL, this time I'd like to extend a query which I've inherited to add some more functionality.I'm not sure if it's the possible to do this in the way I've tried to go about solving this but I've tried to add this new requested data as a sub query in the select statement.Here is what I want to add to my query. From the id in the table fcontainer I've got to do this little walk to reach a product name.select produkter.Produktnamn from FContainer INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr INNER JOIN Produkter ON Tempo.produktnr = Produkter.ProduktnrThe query which I wish to extend contains fcontainer but only in another sub query.SELECT FContainerHylla.Name, FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y, FContainerPlats.enable, FContainerHylla.Type, ISNULL((SELECT MIN(fcontainer.id) AS id FROM FContainer WHERE (PlatsId = FContainerPlats.id)), 0) AS FcIdFROM FContainerPlats INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.IdWHERE (FContainerHylla.Type IN (1, 6, 10))ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y So far my best results is by adding the first query inside the select statement surrounded by ( ) to form a sub query but I get an error where I'm not allowed to have more than one value in a sub query.I need to reduce the possible data by adding a where clause and I'd like to add where fcontainer.id = ....I need fcontainer.id to be compared to the value returned from the isnull statement and defined AS id but not certain how to do this. 解决方案 You can JOIN onto a sub query rather than having it in the select. I came up with this but I can't test it SELECT FContainerHylla.Name, FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y, FContainerPlats.enable, FContainerHylla.Type, ISNULL((SELECT MIN(fcontainer.id) AS id FROM FContainer WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId,SubQ.Produktnamn FROM FContainerPlats INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.IdINNER JOIN (SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamnfrom FContainer INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr) SubQ ON SubQ.SubID = FContainerPlats.idWHERE (FContainerHylla.Type IN (1, 6, 10))ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.yI might have the key on the inner join to "SubQ" wrong.Alternatively you could set them up as two Common Table Expressions and join them that way (again this is untested and watch out for the id I've used in the join);WITH CTE1 AS(SELECT FContainerHylla.Name, FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y, FContainerPlats.enable, FContainerHylla.Type, ISNULL((SELECT MIN(fcontainer.id) AS id FROM FContainer WHERE (PlatsId = FContainerPlats.id)), 0) AS FcIdFROM FContainerPlats INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.IdWHERE (FContainerHylla.Type IN (1, 6, 10))),CTE2 AS (SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamnfrom FContainer INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr) SELECT CTE1.*, CTE2.ProduktnamnFROM CTE1INNER JOIN CTE2 ON CTE2.SubID = FContainerPlats.idORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y 这篇关于扩展旧查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！