MS SQL查询在一个查询中的ColdFusion环境 [英] MS SQL query within a query ColdFusion environment

问题描述

我有两张桌子，一张是Food，另一张是FoodCategory。它们之间的关系是在两个表中包含的FoodCatID。

I have 2 tables, one is called Food while the other is FoodCategory. The relation between them is the FoodCatID which contain in both tables.

我尝试归档的是显示：

• 鸡


• 牛

• tomatoe


• potatoe

我假设我需要查询中的查询？我首先尝试使用Distinct获得2个独特的FoodCatID：

I assume I will need a query within a query? I first try to use Distinct to get the 2 unique FoodCatID:

Select Distinct FoodCategory.FoodCatID, FoodCategoryName
From Food INNER JOIN FoodCategory ON Food.FoodCatID = FoodCategory.FoodCatID

这会给我2个类别，如何使用CatID运行查询的第二部分？

This will give me the 2 categories, but then how can I use the CatID to run the second part of the query?

我在ColdFusion页面上使用它，我应该使用SQL查询存档结果还是可以通过CF代码？

I'm using this on a ColdFusion page, should I archive the result using SQL queries or can I do it through CF code?

推荐答案

应该使用SQL查询归档结果还是可以通过
CF代码？

should I archive the result using SQL queries or can I do it through CF code?

两者。使用单个JOIN来检索类别和食品名称。 SQL Fiddle

Both. Use a single JOIN to retrieve the categories and food names. SQL Fiddle

SELECT fc.FoodCatID
       , fc.FoodCategoryName
       , f.FoodID
       , f.FoodName
FROM   FoodCategory fc INNER JOIN Food f ON f.FoodCatID = fc.FoodCatID
ORDER BY fc.FoodCategoryName, f.FoodName

然后使用分组 cfoutput 。列出所有的食物 - 但只显示类别标题一次。

Then use a "grouped" cfoutput. to list all of the foods - but only display the category headers once.

请注意，结果必须先按类别名称排序，否则无效

Note, the results must be ordered by category name first, or it will not work

  <cfoutput query="yourQuery" group="FoodCategoryName">
      <!--- display header once -->
      #FoodCategoryName#<br><br>
      <cfoutput>
         <!--- display all foods --->
          #FoodName#<br>
      </cfoutput>
  </cfoutput>

这篇关于MS SQL查询在一个查询中的ColdFusion环境的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！