本文介绍了如何计算两个表中的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两张桌子(家庭,家庭成员)。我想要一个查询来按城市计算家庭数和总家庭成员数。我有以下查询,但它只给全家人。 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.CityI 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.CityYou 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.CityResult:
Bialystok 3 12 Katowice 4 12 Warsaw 3 20Try 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更多推荐
如何计算两个表中的值
发布评论