如何在 SQL 中制定 FOR EACH 循环？
我正在开发一个 SQL 查询，目前适用于一组/模式组合。但我希望此查询适用于所有组和模式。并且每种组合仅包含一次。但在源数据中还有许多其他列，因此每个组合有多个记录。
所以我的源表结构是：
group mode p-val
----- ---- ------
A B 4.567
C D 3.694
如何设置循环来循环每个组/模式组合？我宁愿不使用游标！
I am developing a SQL query that currently works for one group/mode combination. But I want this query to work for all groups and modes. And include each combination one time only. But in source data there are many other columns, so there is more than one record per combination.
So my source table structure is:
group mode p-val
----- ---- ------
A B 4.567
C D 3.694
How can I setup a loop to loop through each group/mode combination? And I'd rather not use cursors!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一个非常广泛和普遍的问题。这是一个广泛而普遍的答案。
首先，编写一个查询来提取您感兴趣的值集，以便每个集都是唯一的。
SELECT DISTINCT...和SELECT...GROUP BY...自行建议。该数据可以存储为临时表，包含为子查询，或者只是作为整个最终查询的一部分。最好的使用方法完全取决于您正在做什么。
接下来，使用该子集作为基础编写“主”处理查询。像
...FROM这样的东西内连接 。如果做得正确（我并不是说这很容易），这种基于集合的方法的效果与通过过程循环实现的结果非常相似。
A very broad and general question. Here's a broad and general answer.
First, write a query to extract the set of the values you are interested in, such that each set is unique.
SELECT DISTINCT...andSELECT... GROUP BY...suggest themselves.This data could be stored as a temp table, included as a subquery, or just made part of the overall final query. The best to use depends entirely on what you're working on.
Next, write your "main" processing query using this subset as a basis. Something like
...FROM <theSubset> INNER JOIN <etc>.Done properly (I'm not saying it'd be easy), the effects of this set-based methodology are quite similar to the results achieved with procedural looping.
这在 sql 中称为
JOIN。即语句结果
现在您拥有 [group,mode] 记录的所有组合。阅读有关来自 Google 的 JOIN 的更多信息。
This is called
JOINin sql. i.e. the statementresults
now you have all combinations of [group,mode] records. Read more on JOINs from Google.