在SQL查询合并中需要帮助 [英] Need help in sql query merge

问题描述

大家好

如何合并这些查询

Hi all

How to merge these query

select count(a.pk_id) from test a
union
select count(b.pk_id) from usertesting b
union
select count(c.user_id) from user1 c;

意味着要在单个查询中编写
我尝试了

means wants to write in single query
i tried

select count(a.pk_id),count(b.pk_id),count(c.user_id) from test a ,usertesting b, user1 c;

但这并未显示出真实的结果，因此还尝试使用其他联接.

无法在单个查询中编写上述查询

请帮助我在单个查询中编写以上查询

在此先感谢您.

but this is not showing the true result and also tried using different joining.

Unable to write this above query in a single query

Please help me to write the above query in a single query

Thanks in advance.

推荐答案

第二个查询未显示真实结果的原因是因为您联接的表没有任何链接.这意味着计数将与其他表相乘.测试*用户测试*用户1.

因此，如果您有以下表格:

The reason your second query is not showing a true result is because you''re joining the tables without any kind on link. That means that the counts will be multiplied by the other tables. test * usertesting * user1.

So if you had the following tables:

TableA, TableB, TableC
1       6       a
2       7       b
3       8       c

通过像在第二个查询中一样进行选择，您将获得以下结果:

By selecting as you do in the second query you''d get the following results:

AValue, BValue, CValue
1       6       a
1       6       b
1       6       c
1       7       a
1       7       b
1       7       c
1       8       a
1       8       b
1       8       c
2       6       a
2       6       b
2       6       c
2       7       a
2       7       b
2       7       c
2       8       a
2       8       b
2       8       c
3       6       a
3       6       b
3       6       c
3       7       a
3       7       b
3       7       c
3       8       a
3       8       b
3       8       c

我个人认为，联合选择是获取所需数据的最佳方法，但以下方法应该可以达到您想要的结果.

Personally I think the union select is the best way to get the data you''re after but the following should achieve your desired result.

SELECT cTest, cUserTesting, cUser1 FROM
(SELECT cTest = COUNT(pk_id) FROM test),
(SELECT cUserTesting = COUNT(pk_id) FROM usertesting),
(SELECT cUser1 = COUNT(user_id) FROM user1)

由于列出的每个表只有一行，因此结果不会相乘.

虽然如果您遇到工会问题，则不知道哪个计数可以尝试以下操作.

As each of the tables listed only has one row there won''t be a multiplication of results.

Although if your problem with the union is you don''t know which count is which you could try the following.

select cType = 'test', vCount = count(a.pk_id) from test a
union
select 'usertesting', count(b.pk_id) from usertesting b
union
select 'user1', count(c.user_id) from user1 c;

，您可以通过以下方式使用变量:

You can do it in this way, using variables:

DECLARE @c1 INT
DECLARE @c2 INT
DECLARE @c3 INT

select @c1 = count(a.pk_id) from test a
select @c2 = count(b.pk_id) from usertesting b
select @c3 = count(c.user_id) from user1 c;

SELECT @c1 AS [aPk_id], @c2 AS [bPk_id], @c3 AS [cUs_id]

这篇关于在SQL查询合并中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！