大家好,我想将两个查询组合成一个使用子查询的查询 [英] 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屋!
查看全文