如何通过“比较”与count/'group进行自连接?

Given the following table (how to format those correctly here?)

给定下面的表(如何在这里正确地格式化它们?)

primary secondary
A            a
A            b
A            b
B            a
B            a
B            b

I'm trying to get comparitive group-by counts using a self join.

我试图通过使用自连接计数来获得比较组。

Getting the following result set is easy:

获得以下结果集很容易:

Primary Secondary     Count
A            a          1
A            b          2
B            a          2
B            b          1

with something like:

可以这样说:

select primary,secondary,count(*) from foobar group by primary,secondary

从foobar组中按主、次、次选择主、次、计数(*)

But what I REALLY want is this:

但我真正想要的是:

Primary  Secondary Count  Primary  Secondary    Count
A        a         1      B        a             2
A        b         2      B        b             1

When counts and group bys aren't involved, self-joins are simple. But I can't seem to navigate my way around doing this.

当计数和bys组不参与时，自连接很简单。但我似乎无法在这方面找到方向。

Does the "self join AFTER group by" make this impossible to do? If I have to play temp table games I'll do it (though I'd rather not) since the real goal is a single block of sql (something I can script), more than a single select statement.

“自我加入后的团队”是否使这一行为变得不可能?如果我必须玩临时表游戏，我就会这么做(尽管我宁愿不这么做)，因为真正的目标是一个sql块(我可以编写脚本)，而不是一个select语句。

At the moment I'm doing the former and manually padiddling the data.

目前，我正在执行前者，并手动分配数据。

Thoughts?

想法吗?

Hmm... Of course all the stuff in my head is obvious to ME ;)

嗯…当然，我头脑中所有的东西对我来说都是显而易见的;

The "business logic" I'm trying to achieve is "compare the count of 'secondary' in 'primary A' to the count of 'secondary' in 'primary B' which is why I didn't write out the B:B result set lines. But I figure any clause that gets them in there can be filtered anyway.

我试图实现的“业务逻辑”是“将‘primary A’中的‘secondary’计数与‘primary B’中的‘secondary’计数进行比较，这就是为什么我没有写出B:B的结果集行。但是我认为任何把它们放在那里的子句都可以被过滤。

2 个解决方案

#1

This should get you close. I'm not sure how you determine that only the "A" primary rows get shown as the first couple of columns, so I can't account for that. Why isn't there a:

这会让你更接近。我不确定如何确定只有“A”主行显示为前两列，因此我无法说明原因。为什么没有一个:

B b 1 B b 1

for example?

例如呢?

SELECT
    SQ1.primary,
    SQ1.secondary,
    SQ1.[count],
    SQ2.primary,
    SQ2.secondary,
    SQ2.[count]
FROM
(
    SELECT
        primary,
        secondary,
        COUNT(*) AS [count]
    FROM
        Foobar
    GROUP BY
        primary,
        secondary
) AS SQ1
LEFT OUTER JOIN
(
    SELECT
        primary,
        secondary,
        COUNT(*) AS [count]
    FROM
        Foobar
    GROUP BY
        primary,
        secondary
) AS SQ2 ON SQ2.primary = SQ1.secondary

#2

If you are using SQL Server you can do this easily using CTE

如果您正在使用SQL Server，那么您可以使用CTE轻松实现这一点

If not, you can do this kind of a select (OTTOMH)

如果没有，可以选择(OTTOMH)

SELECT T1.Col1, T1.Col2, T2.Col3, T2.Col4, MyCount
FROM Table1 T1, 
(
    SELECT Col3, Col4, COUNT (*) as MyCount
    FROM Table2
    Group by Col3, Col4
) as T2
WHERE T1.Col1 = T2.Col3
GROUP BY T1.Col1, T1.Col2, T2.Col3, T2.Col4

As your query gets more complicated, take a look at your execution plan for optimum performance.

随着查询变得越来越复杂，请查看执行计划以获得最佳性能。

#1