如何高效查询多条主记录及其一对多记录

发布于 2024-11-16 08:53:41 字数 931 浏览 1 评论 0原文

考虑以下结构 DB 结构。当前的工作语言是Coldfusion 和MSSQL。

Tables
Hotels: - Columns - hotelid, name, company, address, state, city, zip
Media: - Columns - mediaid, label, url
HotelsXMedia: - Columns - xid, hotelid, mediaid

基本上我们有一个包含酒店的表和一个包含图像的表以及一个用于处理多对多关系的交叉表。所需的输出是单个数组（或查询），每个项目都具有酒店表中的所有列以及主行内媒体表的所有行，因此可以将其传递给主题函数，例如。

当前的方法是查询酒店，转换为数组，从结果集中提取 hotelid。根据hotelid查询媒体。将生成的媒体查询转换为索引为 hotelid 的结构。然后循环酒店数组并将其媒体数据分配到酒店数组中。然后将酒店数组传递到它需要去的任何地方。

有没有更有效的方法来做到这一点？有没有一种方法可以在不执行两个 SQL 查询以及所有结果折叠和循环的情况下执行此操作。应用程序中几乎每个模块都利用媒体表来存储其图像，因此对于页面上的几乎每个组件，我们都必须执行这种组合，这看起来相当麻烦。难道就没有更好的办法了吗？此外，虽然增强这一一对多查询组合可能只提供轻微的好处，但因为它在 90% 的组件中是必需的，但增强的好处将传播到所有组件，从而带来可观的收益。

如果结果查询中的列是连接的媒体 URL 的分隔列表，我会认为这是一个可行的解决方案。但不幸的是，在 MSSQL 中我们没有 group_concat()。如果完成此方法，是否有一种简单的方法来获取多个列，例如我想获取 media:url 和 media:label？如果媒体表中的记录有 url 但缺少标签，从而导致 2 个列表不对齐，是否有办法确保空值或 null 值仍然获得分隔符。如果我选择这条路线，我是否必须执行多个 XML 路径才能到达那里？任何指导将不胜感激。

原文

Consider the following structure DB structure. Current working language is Coldfusion and MSSQL.

Tables
Hotels: - Columns - hotelid, name, company, address, state, city, zip
Media: - Columns - mediaid, label, url
HotelsXMedia: - Columns - xid, hotelid, mediaid

Basically we have a table with hotels and a table that contains images and a cross table to handle the many to many relationship. Desired output is a single array (or query), with each item having all of the columns from the hotels table as well as the all of the rows of it's media table inside the primary row so it can be passed on to a theming function, for example.

Current Method is to query hotels, convert to array, extract hotelids from result set. Query media based on hotelid. Convert resulting media query into structure where the index is the hotelid. Then loop over the hotels array and assign in it's media data into the hotels array. Then pass the hotels array on to wherever it needs to go.

Is there a more efficient way of doing this? Is there a way of doing this without doing two SQL queries and all of the resulting folding and looping. Nearly every module in the application utilizes the media table to store it's images so for nearly every component on the page we have to perform this sort of combination and it seems quite cumbersome. Is there no better way? In addition, while it may only provide a slight benefit to enhance this one to many query combination, because it's neccessary in 90% of the components, the enhancement benefit would spread to all of them thus resulting in decent gains.

I would consider it a feasible solution if a column in the result query was a delimited list of joined MEDIA urls. But unfortunately in MSSQL we don't have group_concat(). If this method was done is there an easy way to grab multiple columns, if for example I wanted to grab the media:url and media:label? Is there a way to ensure that empty or null values still get a delimiter if a record in the media table has a url but lacks a label thus causing the 2 lists to be out of alignment. If I went this route would I have to do multiple for XML paths in order to get there? Any guidance would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷路的信 2024-11-23 08:53:41

查看在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数？

回复收藏 0 原文

画▽骨i 2024-11-23 08:53:41

当我遇到这个问题时，我最终做了一个大查询（select * fromhotelsxmedia joinhotelson...joinmediaon...），它返回与交叉表（HotelXMedia）。请务必按 hotelid 订购。然后，当您循环查询结果时，您可以对每个新 hotelid 采取一项操作（例如，追加到数组），并对重复相同 hotelid 的行采取不同的操作（例如，向其中添加新媒体）。

回复收藏 0 原文

迷爱 2024-11-23 08:53:41

正如 @krubo 建议的那样，我会执行一个大型查询，然后使用 group="hotelid" 属性执行 cfoutput 并在其中创建数组和结构。

回复收藏 0 原文

不喜欢何必死缠烂打 2024-11-23 08:53:41

您可以在按 hotelid 排序的一个查询中完成这一切，并使用分组嵌套 CFOUTPUT 构建数据结构。

<cfquery name="myHotels" datasource="HotelStuff" >
  SELECT H.*, M.*
  FROM Hotels H
      JOIN HotelsXMedia HXM ON H.hotelid = HXM.hotelid
      JOIN Media M ON M.mediaid = HXM.mediaid
  ORDER BY H.hotelid
</cfquery>

CFOUTPUT 的 group="" 属性导致循环根据 hotelid 字段值的更改进行分组。

<cfoutput query="myHotels" group="hotelid">
  <cfset hotels = {
     hotelid = myHotels.hotelid,
     name = myHotels.name,
     company = myHotels.company,
     address = myHotels.address,
     state = myHotels.state,
     city = myHotels.city,
     zip = myHotels.zip,
     media = arrayNew(1)
  } />

  <cfoutput>
     <cfset thisMedia = { label = myHotels.label, url = myHotels.url } />
     <cfset arrayAppend(hotels.media, thisMedia) />
  </cfoutput>
</cfoutput>

您可以通过 Hotels.name 进行预订，有时也可以侥幸成功；您需要确保没有两家酒店具有完全相同的名称，否则它们将被合并为一个组。使用基于主键列的组通常更安全。

You can do this all in one query ordered by hotelid and build the data structure by using a grouped nested CFOUTPUT.

<cfquery name="myHotels" datasource="HotelStuff" >
  SELECT H.*, M.*
  FROM Hotels H
      JOIN HotelsXMedia HXM ON H.hotelid = HXM.hotelid
      JOIN Media M ON M.mediaid = HXM.mediaid
  ORDER BY H.hotelid
</cfquery>

The group="" attribute to CFOUTPUT causes the loop to be grouped by changes in the hotelid field value.

<cfoutput query="myHotels" group="hotelid">
  <cfset hotels = {
     hotelid = myHotels.hotelid,
     name = myHotels.name,
     company = myHotels.company,
     address = myHotels.address,
     state = myHotels.state,
     city = myHotels.city,
     zip = myHotels.zip,
     media = arrayNew(1)
  } />

  <cfoutput>
     <cfset thisMedia = { label = myHotels.label, url = myHotels.url } />
     <cfset arrayAppend(hotels.media, thisMedia) />
  </cfoutput>
</cfoutput>

You could order by Hotels.name and sometimes get away with it; you'd need to make sure that no two hotels ever have exactly the same name or they will be collapsed into a single group. It's generally safer to use a group based on your primary key column.

回复收藏 0 原文

~没有更多了~