如何按计数和加入分组？

I have two tables, A(ID, Name), B(ID). I want record with group by Name, and count of A(ID) and B(ID)

我有两个表,A(ID,名称),B(ID)。我希望按名称分组记录,以及A(ID)和B(ID)的计数

I am trying this way

我正在尝试这种方式

SELECT LEFT(OD.number, 3)        Terminal, 
       Count(LEFT(OD.number, 2)) OrderCount, 
       Count(OT.orderid) 
FROM   gff_bog_orderlocation.dbo.orderdetail OD, 
       gff_bog_orderlocation.dbo.ordertable OT 
WHERE  OT.orderid IN (SELECT orderid 
                      FROM   gff_bog_orderlocation.dbo.orderdetail 
                      WHERE  LEFT(number, 3) IN(SELECT LEFT(number, 3) 
                                                FROM 
                             gff_bog_orderlocation.dbo.orderdetail 
                                                GROUP  BY LEFT(number, 3))) 
GROUP  BY LEFT(OD.number, 3) 
ORDER  BY terminal 

But I am not getting properly.

但我没有得到适当的。

1 个解决方案

#1

based on your question and not your sql i'd say you'd need to do something like this.

基于你的问题而不是你的sql我会说你需要做这样的事情。

CREATE TABLE TableA (ID INT, NAME VARCHAR(50))
CREATE TABLE TableB (ID INT)
INSERT INTO TableA VALUES (1, 'US'), (2, 'US'), (3, 'Canada'), (4, 'Mexico'), (5, 'Mexico')
INSERT INTO TableB VALUES (1), (1), (1), (2), (2), (3), (3), (3), (4), (4), (4), (5), (5), (5), (5), (5) 

SELECT tA.Name,
       COUNT(*) TableACount,
       TableBCount
FROM   TableA tA
        JOIN (
            SELECT Name,
                   COUNT(*) TableBCount
            FROM   TableA tA
                   LEFT JOIN TableB tB ON tA.ID = tB.ID 
            GROUP BY    
                   Name 
        ) tB ON tA.Name = tB.Name
GROUP BY tA.Name,
        TableBCount

DROP TABLE TableA
DROP TABLE TableB

RESULT: 

Name    TableACount TableBCount
----    ----------- -----------
Canada  1           3
Mexico  2           8
US      2           5