合并SQL查询 [英] Merging SQL queries

问题描述

我有我从C＃的winform运行两个SQL查询，我想将它们合并

I have two SQL queries that I'm running from a C# winform, and I want to merge them.

第一个是：

SELECT * FROM ES_TOOL INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE 
                INNER JOIN ES_PAYMENT on ES_payment.espay_id = es_TOOL.estool_payment

这给出了所有的工具和它们相关的支付和硬件要求列表。二是对每个记录运行

This gives a list of all the tools and their associated payment and hardware requirements.

和第一查询返回，由环AC＃方式：

And the second is run on each record that the first query returns, by means of a c# for loop:

SELECT avg(a.esmrk_value) from ES_MARK a Inner Join ES_TOOL_CHAPTER b 
on  a.esmark_tool_chapter= b.estch_id WHERE b.estch_tool=@tool

这回报，每个工具，平均品位，他们已经被授予在es_mark表。

This returns, for each tool, the average grade they've been awarded in the es_mark table.

NB商标被授予个人的工具章。因此，要找到一个工具，我们需要看到ES_TOOL_CHAPTER表的工具章节列表中的所有标记。工具分会是什么收到在ES_MARK表标记。

N.B. Marks are awarded for individual "chapters" of tools. So, to find all the marks for a tool we need to see the ES_TOOL_CHAPTER table for a list of tool-chapters. Tool-chapters are what receive marks in the ES_MARK table.

我试过分组和多连接，但我只是按分钟越来越糊涂了，所以我倒是欣赏这方面的任何帮助。
我基本上找一个查询将返回第一个查询的所有结果与平均分的附加列。

I've tried grouping and more joins, but I'm just getting more confused by the minute, so I'd appreciate any help on the matter. I'm basically looking for one query that will return all the results of the first query with an additional column for average mark.

感谢。

编辑：@tool表示工具ID

the @tool represents the tool id.

推荐答案

如果你想要的。与主查询一起检索平均成绩的：

If you want to retrieve the average grade's together with the main query:

SELECT *
FROM ES_TOOL
  INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE 
  INNER JOIN ES_PAYMENT on ES_payment.espay_id = es_TOOL.estool_payment
  LEFT JOIN (
    SELECT b.estch_tool, avg(a.esmrk_value) AvgValue
    FROM ES_MARK a
      Inner Join ES_TOOL_CHAPTER b 
        ON a.esmark_tool_chapter = b.estch_id
    GROUP BY b.estch_tool
  ) g ON ES_TOOL.ToolColumn =  g.estch_tool

替换 ToolColumn 通过正确的列名。

请注意： SELECT * 不建议，请阅读的这个问题。

Note: SELECT * is not recommended, please read This question.

这篇关于合并SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！