如何计算两个表中的值

本文介绍了如何计算两个表中的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两张桌子（家庭，家庭成员）。我想要一个查询来按城市计算家庭数和总家庭成员数。我有以下查询，但它只给全家人。 TotalFamilies，TotalIndiviuals，City 6,6，abc， 268,268，Peshawar

选择 COUNT（Fam.FamilyID） as TotalFamilies，COUNT（*） as TotalIndiviuals，Fam.City 来自 Family as Fam inner join FamilyMembers FMem FMem.FamilyID = Fam.FamilyID group by Fam.City

我将感谢您的帮助。 Usama

解决方案

您需要在加入中使用子查询

选择 COUNT（Fam.FamilyID） as TotalFamilies，Sum（CountFamilyMembers.totalFMem） as TotalIndiviuals，Fam.City From 家庭 as Fam inner join （选择 FamilyID，Count（FamilyMemberId） as totalFMem 来自 FamilyMembers 组 按 FamilyId） as CountFamilyMembers On fam.FamilyId = CountFamilyMembers.FamilyId group by Fam.City

你需要不同的成员;）

DECLARE @ Family TABLE （FamilyID INT IDENTITY （ 1 ， 1 ），FamilyName VARCHAR （ 50 ），City VARCHAR （ 50 ）） INSERT INTO @ Family （FamilyName，City） SELECT ' FamilyW1'， Warsaw' UNION ALL SELECT ' FamilyW2'，' Warsaw' UNION ALL SELECT ' FamilyW3'，' Warsaw' UNION ALL SELECT ' FamilyK1'，' Katowice' UNION ALL SELECT ' FamilyK2'，' Katowice' UNION 所有 SELECT ' FamilyK3' ，' Katowice' UNION ALL SELECT ' FamilyK4'，' Katowice' UNION ALL SELECT ' FamilyB1'，' Bialystok' UNION ALL SELECT ' FamilyB2'，' Bialystok' UNION ALL SELECT ' FamilyB3'，' Bialystok' DECLARE @ FamilyMembers TABLE （MemberID INT IDENTITY （ 1 ， 1 ）， FamilyID INT ） INSERT INTO @ FamilyMembers （FamilyID） SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION 所有 SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION 所有 SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION 所有 SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 8 UNION ALL SELECT 8 UNION 所有 SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 10 UNION ALL SELECT 10 UNION ALL SELECT 10 UNION ALL SELECT 10 SELECT Fam.City，COUNT（ DISTINCT Fam.FamilyID） AS CountOfFamilies ，COUNT（ DISTINCT Mem.MemberID） AS NumberOfMembers FROM @ Family AS Fam INNER JOIN @ FamilyMembers AS Mem ON Fam.FamilyID = Mem.FamilyID GROUP BY Fam.City

结果：

Bialystok 3 12 Katowice 4 12 华沙3 20

试试这个剧本

声明 @ Family table （FamilyID varchar （ 10 ），City varchar （ 100 ）） 声明 @ FamilyMembers 表（FamilyID varchar （ 10 ），MemberName varchar （ 100 ）） insert 进入 @ Family 选择 ' F1'，' C1' union 选择 ' F2'，' C1' union 选择 ' F3'，' C2' union 选择 ' F4' ，' C3' insert 进入 @ FamilyMembers 选择 ' F1'，' M1' union 选择 ' F1'，' M2' union 选择 ' F1'，' M3' union 选择 ' F3'，' M4' union 选择 ' F3'，' M5' SELECT COUNT（ distinct （Fam.FamilyID）） as TotalFamilies， COUNT（FMem.FamilyID） as TotalIndiviuals，Fam.City FROM @家庭 AS Fam INNER 加入 @ FamilyMembers FMem 开启 FMem.FamilyID = Fam.FamilyID GROUP BY Fam.City

Hi, I have two tables (Family, Family Members). I want a query to get count of Families and Count of Total Family Members Group by city. I have the following query but its only giving total family members. TotalFamilies, TotalIndiviuals,City 6,6,abc, 268,268,Peshawar

select COUNT(Fam.FamilyID) as TotalFamilies, COUNT(*) as TotalIndiviuals, Fam.City from Family as Fam inner join FamilyMembers FMem on FMem.FamilyID=Fam.FamilyID group by Fam.City

I shall be thankful for your help. Usama

解决方案

You will need to use subquery in the join

select COUNT(Fam.FamilyID) as TotalFamilies, Sum(CountFamilyMembers.totalFMem) as TotalIndiviuals, Fam.City From Family as Fam inner join (Select FamilyID,Count(FamilyMemberId) as totalFMem from FamilyMembers Group By FamilyId) as CountFamilyMembers On fam.FamilyId=CountFamilyMembers.FamilyId group by Fam.City

You need to distinct members ;)

DECLARE @Family TABLE (FamilyID INT IDENTITY(1,1), FamilyName VARCHAR(50), City VARCHAR(50)) INSERT INTO @Family (FamilyName, City) SELECT 'FamilyW1', 'Warsaw' UNION ALL SELECT 'FamilyW2', 'Warsaw' UNION ALL SELECT 'FamilyW3', 'Warsaw' UNION ALL SELECT 'FamilyK1', 'Katowice' UNION ALL SELECT 'FamilyK2', 'Katowice' UNION ALL SELECT 'FamilyK3', 'Katowice' UNION ALL SELECT 'FamilyK4', 'Katowice' UNION ALL SELECT 'FamilyB1', 'Bialystok' UNION ALL SELECT 'FamilyB2', 'Bialystok' UNION ALL SELECT 'FamilyB3', 'Bialystok' DECLARE @FamilyMembers TABLE (MemberID INT IDENTITY(1,1), FamilyID INT) INSERT INTO @FamilyMembers (FamilyID) SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 8 UNION ALL SELECT 8 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 10 UNION ALL SELECT 10 UNION ALL SELECT 10 UNION ALL SELECT 10 SELECT Fam.City, COUNT(DISTINCT Fam.FamilyID) AS CountOfFamilies, COUNT(DISTINCT Mem.MemberID) AS NumberOfMembers FROM @Family AS Fam INNER JOIN @FamilyMembers AS Mem ON Fam.FamilyID = Mem.FamilyID GROUP BY Fam.City

Result:

Bialystok 3 12 Katowice 4 12 Warsaw 3 20

Try this script

declare @Family table(FamilyID varchar(10) , City varchar(100)) declare @FamilyMembers table(FamilyID varchar(10), MemberName varchar(100)) insert into @Family select 'F1', 'C1' union select 'F2', 'C1' union select 'F3', 'C2' union select 'F4', 'C3' insert into @FamilyMembers select 'F1', 'M1' union select 'F1', 'M2' union select 'F1', 'M3' union select 'F3', 'M4' union select 'F3', 'M5' SELECT COUNT(distinct(Fam.FamilyID)) as TotalFamilies, COUNT(FMem.FamilyID) as TotalIndiviuals, Fam.City FROM @Family AS Fam INNER JOIN @FamilyMembers FMem ON FMem.FamilyID=Fam.FamilyID GROUP BY Fam.City