大家好 如何合并这些查询
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查询合并中需要帮助
发布评论