如何在单个查询中使用主表列获取子表行数 [英] How to get child table row count with master table columns in single query

问题描述

我正在使用MS SQL Server 2012

DB有一个主表和一个子表。

我需要master中的一些列，其中有活动（Status = 1）和不活动（Status = 0）子记录计数。请参考下面给出的示例数据表



我通过编写存储过程得到了结果。但我正在寻找一个更好的查询或具有良好性能的CTE。

主表中的近似行数（表A）：500

子表中的近似行数（表B）：300000且大小正在增加





表A

********************** ****

<前lang =HTML> Col1 Col2 Col3
1 A1 A11
2 A2 A12
3 A3 A13

表B

*********************** ****

 Col1 Col2 Col3状态
 1 1 B1 1 
 1 2 B2 1 
 2 3 B3 0 
 2 4 B4 1 
 3 5 B5 1 

结果

**** **********************

 A.Col1 A.Col2 ActiveCount InactiveCount 
 1 A1 2 0 
 2 A2 1 1 
 3 A3 1 0 

任何帮助?? ..





这是我现有的查询

  SELECT  T1。*，
（ SELECT  COUNT（Col2） FROM  B 
  WHERE  B.Col1 = T1.Col1 
  AND  B.Status = '  A'） AS  ActiveCount，
（ SELECT  COUNT（Col2） FROM  B 
  WHERE  B.Col1 = T1。 Col1 
  AND  B.Status = '  R '） AS  InactiveCount，
（ SELECT  COUNT（Col2） FROM  B 
  WHERE  B.Col1 = T1.Col1 
  AND  B.Status = '  B'） AS  BlockedCount，
（ SELECT  COUNT（Col2） FROM  B 
  WHERE  B.Col1 = T1.Col1 
  AND  B.Status = '  C'） AS  CancelledCount， 
（ SELECT  COUNT（Col2） FROM  B 
  WHERE  B.Col1 = T1.Col1 
  AND  B.Status = '   D'） AS  DeactivatedCount 
  FROM （
  SELECT  Col1，Col2，Col3  FROM  A  WHERE  A.Col1  IN  
（ SELECT   DISTINCT  B.Col1  FROM  B  WHERE  B.ColXX = '  TRUE'）
） AS  T1 

解决方案

如果你加入Col1上的表格，例如

 选择 A.Col1  AS  Col1，A.Col2  as  Col2，[status] 
  From  TableA A 
  left  外部  join  TableB B   A.Col1 = B.Col1 

你得到以下......

 1 A1 1 
 1 A1 1 
 2 A2 0 
 2 A2 1 
 3 A3 1 
 4 A4 NULL 

（我添加了一行额外的行没有子表条目的父表只是为了测试我的查询）。即在父表的每行结果中得到0,1或2行。



您可以使用PIVOT的输出来获取您提供的数据它

 选择 Col1，Col2，[ 1 ]  as  ActiveCount，[ 0 ]  as  InactiveCount 
  FROM  
（
 选择 A.Col1 < span class =code-keyword> AS  Col1，A.Col2  as  Col2，[status] 
 来自 TableA A 
  left   outer   join  TableB B  on  A.Col1 = B.Col1 
） AS  src 
 PIVOT 
（
 count（[status]） for  [status]  （[ 0 ]，[ 1  ]）
） as  pvt 

它的效率相当高如果有适当的主键和外键定义，可以做得更多



如需进一步参考，请参阅

SQL联接的可视化表示 [ ^ ]

SQL Wizardry第七部分 - PIVOT和任意数据列表 [ ^ ]

数据库性能优化第1部分（索引策略） [ ^ ]

I am using MS SQL Server 2012
DB has one master and one child table.
I need some columns from master with number of active(Status=1) and inactive(Status=0) child records count. Please refer sample data table given below

I got the result by writing stored procedure. But I am looking for a better query or CTE with good performance.
Approximate Row count in Master Table (Table A) : 500
Apporximate Row Count in Child Table (Table B) : 300000 and size is increasing


Table A
**************************

Col1 Col2 Col3
1    A1   A11
2    A2   A12
3    A3   A13

Table B
***************************

Col1 Col2 Col3 Status
1    1    B1   1
1    2    B2   1
2    3    B3   0
2    4    B4   1
3    5    B5   1

Result
**************************

A.Col1 A.Col2 ActiveCount InactiveCount
1      A1     2           0
2      A2     1           1
3      A3     1           0

Any help??..


Here is my existing query

SELECT T1.* , 
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='A') AS ActiveCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='R') AS InactiveCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='B') AS BlockedCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='C') AS CancelledCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='D') AS DeactivatedCount
			FROM (
			SELECT  Col1 ,Col2 ,Col3 FROM A WHERE A.Col1  IN 
			(SELECT DISTINCT B.Col1 FROM B WHERE B.ColXX='TRUE')
			) AS  T1

解决方案

If you join the tables on Col1 e.g.

select A.Col1 AS Col1, A.Col2 as Col2, [status]
From TableA A
left outer join TableB B on A.Col1 = B.Col1

you get the following ...

1	A1	1
1	A1	1
2	A2	0
2	A2	1
3	A3	1
4	A4	NULL

(I added an extra row to the parent table for which there were no child table entries just to test my query). I.e. you get 0,1, or 2 rows in the results per row on the parent table.

You can use that output with PIVOT to get the data as you have presented it

select Col1, Col2, [1] as ActiveCount,[0] as InactiveCount
FROM
(
	select A.Col1 AS Col1, A.Col2 as Col2, [status]
	From TableA A
	left outer join TableB B on A.Col1 = B.Col1
) AS src
PIVOT
(
	count([status]) for [status] in ([0],[1])
) as pvt

It's reasonably efficient and can be made more so if there are appropriate primary and foreign keys defined

For further reference see
Visual Representation of SQL Joins[^]
SQL Wizardry Part Seven - PIVOT and arbitrary lists of data[^]
Database performance optimization part 1 (Indexing strategies)[^]

这篇关于如何在单个查询中使用主表列获取子表行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！