SQL - 计算列中的差异

I have a bit of a dilemma.

我有点两难。

In my DB, I have Box Numbers that contain,

在我的数据库中,我有包含的Box号,

“13NR0123-0001, 13NR0123-0002….“

and other box numbers that don’t.

和其他没有的箱号。

“13NR0456”

The reason for the

之所以如此

-0001, -0002

is that these boxes has subs in the boxes and the other don’t have subs.

是这些盒子在盒子里有子,另一个没有子。

I’m trying to ascertain how many boxes I have in my Database, I have written two scripts for each variance but its not giving me the correct answer.

我正在尝试确定我的数据库中有多少个盒子,我为每个方差编写了两个脚本,但它没有给我正确的答案。

The below is for report for sub boxes

以下是子框报告

select distinct substring(FD_E77BE253,1,len(FD_E77BE253) - 5) as BoxNumber,
count(FD_84A4EF1A) As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 like '%-%'
group by FD_E77BE253

The below is for no sub-boxes

以下是没有子框

select distinct (FD_E77BE253) as BoxNumber,
count(FD_84A4EF1A) As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 not like '%-%'
group by FD_E77BE253

The script with no subs work fine, but the one with sub-boxes does not give me accurate figures.

没有子工作的脚本工作正常,但带子框的脚本不能给我准确的数字。

Is there anyway I can combine both??

无论如何我可以将两者结合起来吗?

Expected output must look like the below

预期输出必须如下所示

Thanking you in advance.

提前感谢你。

2 个解决方案

#1

The reason why your first query doesn't work is that you're grouping on FD_E77BE253 but the field on your select is a substring of the whole, so basically you're grouping on some other data and projecting a sub portion of it. the count belongs to the whole thing not the substring, that why.

你的第一个查询不起作用的原因是你在FD_E77BE253上进行分组,但你选择的字段是整体的子字符串,所以基本上你要对其他一些数据进行分组并投影它的子部分。计数属于整个事物而不是子串,这就是原因。

In order to fix that you have to create a temp table containing refined data OR write a common table expression for refined data and use it as the source of your final query.

为了解决这个问题,您必须创建包含精炼数据的临时表,或者为精炼数据编写公用表表达式,并将其用作最终查询的源。

it goes something like this:

它是这样的:

;with cte1 as (
select substring(FD_E77BE253,1,len(FD_E77BE253) - 5) as BoxNumber, FD_84A4EF1A As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 like '%-%'
),
cte2 as (
select distinct (FD_E77BE253) as BoxNumber, FD_84A4EF1A As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
and FD_E77BE253 not like '%-%'
)

    select distinct cte1.BoxNumber, count(cte1.Document_Count)
    from cte1
    group by BoxNumber
union 
    select distinct cte2.BoxNumber, count(cte2.Document_Count)
    from cte2
    group by BoxNumber

#2

Just add '-' and use one query to count:

只需添加' - '并使用一个查询来计算:

select LEFT(FD_E77BE253 + '-', CHARINDEX('-', FD_E77BE253 + '-') - 1) as BoxNumber,
    count(FD_84A4EF1A) As Document_Count
from FD_Documents
where deleted = '0'
and FD_9DAADEC8 is not null
group by FD_E77BE253

#1