如何查找由联合选择的行的计数 [英] how to find count over rows selected by union

问题描述

如何查找由union选择的行数.

我的SQL查询是:

how to find count over rows selected by union .

My sql Query is:

select Table1.ID,Table1.RollNo
from 
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Complusary'
union
select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Elective'

它遍历两行我想要输出的行，如
计数
2

It reurns two rows i want output like
Count
2

推荐答案

with Cte_Test as 
select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Complusary'
union
select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Elective')
select count(1) from cte_test

或类似的东西

or something like this

Select count(1) from (select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Complusary'
union
select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Elective')as  A

尝试如下

Try it as follows

Select count(*) from (
select Table1.ID,Table1.RollNo
from 
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Complusary'
union
select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category='Elective') as test

问候
塞巴斯蒂安(Sebastian)

Regards
Sebastian

实际上，首先不需要使用Union.

Actually there are no need to use Union first of all try this.

select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where (Table1.Subject='Maths' and (Table2.Category='Complusary' or Table2.Category='Elective')

还有这个

and also this

select Table1.ID,Table1.RollNo
from
Table1 inner join Table2 on Table1.RollNo = Table2.RollNo and Table1.ID = Table2.ID
where Table1.Subject='Maths' and Table2.Category in ('Complusary','Elective')

这篇关于如何查找由联合选择的行的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！