大家好，我想将两个查询组合成一个使用子查询的查询 [英] Hi everyone I want to combine two queries into one making use of a sub query

问题描述

我有这个查询

I have this query

SELECT DISTINCT dbo.DRILL_HOLE.HOLE_NUMBER, dbo.DRILL_HOLE.PROJECT_NUMBER
FROM         dbo.DRILL_HOLE INNER JOIN
                      dbo.HOLE_INTERVAL ON dbo.DRILL_HOLE.HOLE_NUMBER <> dbo.HOLE_INTERVAL.HOLE_NUMBER
WHERE     (dbo.DRILL_HOLE.hole_type_code = 'DD') OR
                      (dbo.DRILL_HOLE.hole_type_code = 'CH')

给我这个结果。

表A：

which gives me this results.
TABLE A:

HOLE_NUMBER    PRJ_NAME
AB11111        XXXX2009
AB22222        XXXX2010
AB33333        XXXX2011
AB44444        XXXX2012

和另一个查询

and another query

SELECT DISTINCT dbo.DRILL_HOLE.PROJECT_NUMBER, COUNT(dbo.DRILL_HOLE.HOLE_NUMBER) AS HoleCount
FROM         dbo.A
GROUP BY dbo.DRILL_HOLE.PROJECT_NUMBER

也给了我这个结果。

which also gives me this results.

PROJECT_NUMBER    HoleCount
XXXX2009           1
XXXX2010           1
XXXX2011           1
XXXX2012           1

现在我想结合这两个查询，利用子查询直接生成第二个结果。

Now I want to combine this two queries making use of a subquery to produce the second results straight forward.

推荐答案

首先看，它看起来很简单。

On the first look, it seems to be quite simple.

SELECT DISTINCT dh.HOLE_NUMBER, dh.PROJECT_NUMBER, COUNT(dh.HOLE_NUMBER) AS HoleCount
FROM dbo.DRILL_HOLE AS dh INNER JOIN dbo.HOLE_INTERVAL As hi ON dh.HOLE_NUMBER <> hi.HOLE_NUMBER
WHERE (dh.hole_type_code = 'DD') OR (dh.hole_type_code = 'CH')
GROUP BY dh.HOLE_NUMBER, dh.PROJECT_NUMBER

计算Hole_numbers上的项目：

To count Projects on Hole_numbers:

SELECT dh.PROJECT_NUMBER, COUNT(dh.HOLE_NUMBER) AS HoleCount
FROM dbo.DRILL_HOLE AS dh INNER JOIN dbo.HOLE_INTERVAL As hi ON dh.HOLE_NUMBER <> hi.HOLE_NUMBER
WHERE (dh.hole_type_code = 'DD') OR (dh.hole_type_code = 'CH')
GROUP BY dh.PROJECT_NUMBER

它应该给你想要的结果;）

[/ EDIT]

It should give you desired result ;)
[/EDIT]

这篇关于大家好，我想将两个查询组合成一个使用子查询的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！