来自不同数据库的行数 [英] count of rows from different database

问题描述

大家好..



我有两个数据库A& B在两个数据库中都有一个名为planner的表..

表结构如下：

Hello everyone..

I have two database A & B in both the database there is a table called "planner"..
table structure is as follow:

Table A :

Id    Topicid         Resourceid              Isdeleted
1       596             3                          false
2       359             5                          false
3       596             8                          true
3       596             10                         true
3       596             12                         true

Table B:

Id    Topicid         Resourceid              Isdeleted
1       596             3                          false
2       359             5                          false
3       596             8                          true
3       596             10                         true
3       596             12                         false

I need a query for the isdeleted="true" which gives me a result like this


Topicid         isDeletedCountA           isDeletedCountB
359                0                          0
596                3                          2


I have tried this thing , but its not working perfectly..

Query :


select A.TopicId,Count(A.isdeleted),count(B.isdeleted) from
A inner join B on A.TopicId=B.TopicId
where A.isdeleted='true'
and B.isdeleted='true'
group by A.TopicId
order by A.topicid

请帮帮我...





问候，

Krunal

Please help me out...


Regards,
Krunal

推荐答案

试试这个：

Try this:

DECLARE @tableA TABLE (Id INT, Topicid INT, Resourceid INT, Isdeleted VARCHAR(10))

INSERT INTO @tableA (Id, Topicid, Resourceid, Isdeleted)
SELECT 1 AS Id, 596 AS Topicid, 3 AS Resourceid, 'false' AS Isdeleted
UNION ALL SELECT 2, 359, 5, 'false'
UNION ALL SELECT 3, 596, 8, 'true'
UNION ALL SELECT 3, 596, 10, 'true'
UNION ALL SELECT 3, 596, 12, 'true'

DECLARE @tableB TABLE (Id INT, Topicid INT, Resourceid INT, Isdeleted VARCHAR(10))

INSERT INTO @tableB (Id, Topicid, Resourceid, Isdeleted)
SELECT 1 AS Id, 596 AS Topicid, 3 AS Resourceid, 'false' AS Isdeleted
UNION ALL SELECT 2, 359, 5, 'false'
UNION ALL SELECT 3, 596, 8, 'true'
UNION ALL SELECT 3, 596, 10, 'true'
UNION ALL SELECT 3, 596, 12, 'false'

SELECT Topicid, Isdeleted, [A], [B]
FROM(
    SELECT Topicid, 'A' AS TableName, Isdeleted
    FROM @tableA
    UNION ALL
    SELECT Topicid, 'B' AS TableName, Isdeleted
    FROM @tableB
) AS DT
PIVOT(COUNT([Tablename]) FOR Tablename IN([A],[B]))AS PT
--WHERE Isdeleted = 'true'

结果：

Result:

Topicid Isdeleted	A	B
359	false		1	1
596	false		1	2
596	true		3	2

您好，



检查下面的脚本.....

Hi,

Check the below Script.....

SELECT T.Topicid,
	(SELECT ISNULL(COUNT(Resourceid),0) FROM TableA WHERE Topicid=T.Topicid AND Isdeleted=1) 'isDeletedCountA',
	(SELECT ISNULL(COUNT(Resourceid),0) FROM TableB WHERE Topicid=T.Topicid AND Isdeleted=1) 'isDeletedCountB'
FROM (SELECT Topicid FROM TableA
	  UNION 
      SELECT Topicid FROM TableB) T

问候，

GVPrabu

Regards,
GVPrabu

参见，例如，在这里：如何查询多个数据库并生成报告 [ ^ ]。

See, for instance, here: "How To Query Multiple Databases and Generate Reports"[^].

这篇关于来自不同数据库的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！