如何查找由联合选择的行的计数

本文介绍了如何查找由联合选择的行的计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何查找由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')