Sql连接，内连接或按查询分组问题。 [英] Sql join, inner join or group by query issue.

问题描述

我有两个表，一个是批次

批次

I have two tables one is batches
Batches

Batch_Name      ||           Scan_Folder
EMPO-002-091015	||   \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\          
EMPO-003-091015	||   \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\          

发票

Invoices

IRN          ||      Customer Name      ||     DTL_Status      || Batch_Name
1111         ||       SAM               ||        NI           || EMPO-002-091015
2222         ||       CLIFF             ||        NI           || EMPO-002-091015
3333         ||       MARY              ||        CO           || EMPO-003-091015
4444         ||       SANJAY            ||        NI           || EMPO-003-091015

我想要那些在NI状态下拥有所有发票的Batch_Name，Scan_Folder。

答：对于上表应该是

EMPO-002-091015 || \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\



请帮我解决这个问题我无法理解如何启动它。





谢谢&此致，

Sunil Mali。



我的尝试：



从批次b中选择b。*，发票i

其中i.Batch_name = b.Batch_name

和b.DTL ....

我很困惑......



选择不同的batch_name，count（DTL_Status），DTL_Status
来自invpagebkp的


group by Batch_Name，DTL_Status

批次名称='MRAB-0010-011116'按批次名称排序

I want Batch_Name, Scan_Folder of those batches who has all invoices in NI Status.
Ans: for above table should be
EMPO-002-091015 || \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\
because EMPO-002-091015 has all invoices against it in NI Status.


Please help me for this query i am not able to understand how to initiate it.


Thanks & Regards,
Sunil Mali.

What I have tried:

select b.* from batches b,invoice i
where i.Batch_name=b.Batch_name
and b.DTL....
I am confused...

select distinct batch_name,count(DTL_Status),DTL_Status
from invpagebkp
group by Batch_Name,DTL_Status
having Batch_name='MRAB-0010-011116' order by batch_name

推荐答案

有两种方法可以做到这一点...

此查询将获得每个batch_name的dtl_status数量

There are a couple of ways you can do this...
This query will get the number of dtl_status for each batch_name

SELECT Batch_Name, DTL_Status, Count(*) as NIcount from invoices
where DTL_Status = 'NI'
group by Batch_Name, DTL_Status

这个将获得每个batch_name的发票总数

and this one will get the total number of invoices per batch_name

SELECT Batch_Name, Count(*) as AllCount from invoices
	group by Batch_Name

您可以合并这两个查询（我在下面使用两个公用表格 - 请参阅 CTE在SQL Server中 [< a href =http://www.codeproject.com/Articles/275645/CTE-In-SQL-Servertarget =_ blanktitle =New Window> ^ ]），并加入回当NI状态的计数与发票总数相同时，您的批次表将获取其余数据...

You can combine these two queries (I'm using two Common Table Expressions below - see CTE In SQL Server[^]), and join back to your Batches table to get the rest of the data when the count of NI status is the same as the total number of invoices...

;with t1 as (
	SELECT Batch_Name, DTL_Status, Count(*) as NIcount from invoices
	where DTL_Status = 'NI'
	group by Batch_Name, DTL_Status
)
, t2 as (SELECT Batch_Name, Count(*) as AllCount from invoices
	group by Batch_Name)
select b.Batch_Name, b.Scan_Folder
from t1
join t2 on t1.Batch_Name = t2.Batch_Name
join batches b on t1.Batch_Name = b.Batch_Name
where t1.NICount = t2.AllCount

通过使用 OVER，可以更加整洁地完成这项工作（如果您有SQL Server 2005或更高版本） a> [ ^ ]这样的条款

This can be done even more neatly (if you have SQL Server 2005 or later) by using the OVER[^] clause like this

;with temp as 
(
	Select Distinct Batch_Name, Count(*) OVER(PARTITION BY Batch_Name) AS Total
		, count(*) OVER(PARTITION BY Batch_Name, DTL_Status) AS NIStatus
	from invoices
)
SELECT b.*
FROM batches b 
join temp on b.Batch_Name = temp.Batch_Name
where Total = NIStatus

这篇关于Sql连接，内连接或按查询分组问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！