子查询by bypass where子句

Question

早上好，mysql查询，它显示一个日期字段完成的案例&总行数量，我想根据总案例计算这个值，但不确定我是否可以在一个查询中完成所有这些。例如，当前查询输出

Aug Sep Nov Total
10  20  20  50

它的代码是

     SELECT * from(
     Select Count(b.CaseID) As TotDB 
     from tblcontacts a 
     Inner Join  tblcases b 
     On a.ContactID = b.ContactAssignedTo)a
CROSS JOIN
    (Select
  Sum(Month(b.StatusSubmittedDate) = 8) As Aug,
  Sum(Month(b.StatusSubmittedDate) = 9) As Sep,
  Sum(Month(b.StatusSubmittedDate) = 10) As Oct,
  Count(b.CaseID) As Total,
  ROUND (100*Count(b.CaseID)/Count(b.CaseID),2) As Conversion
From
  tblcontacts a Inner Join
  tblcases b On a.ContactID = b.ContactAssignedTo
Where
  b.StatusSubmittedDate > '2012 - 01 - 01'
Group By
  a.ContactFullName With Rollup
Having
  Sum(b.CaseCommission) > 0.01)b 

我需要将下面的输出添加到下面的输出中，所以我添加了上面的TotDB行，以查看这是否有帮助，但没有。

Aug Sep Nov Tot TotDB %Converted
10  20  20  50  100   50%

谢谢

Answer 1

也许你应该这样做：

select Aug,Sep, Nov, Tot,TotDB,(Tot/TotDB*1.0)*100 as '%Converted' 
 from 
(SELECT * from(
     Select Count(b.CaseDate) As TotDB 
     from tblcontacts a 
     Inner Join  tblcases b 
     On a.ContactID = b.ContactAssignedTo)a
CROSS JOIN
    (select  
      Sum(Month(b.StatusSubmittedDate) = 9) As Sep,
      Sum(Month(b.StatusSubmittedDate) = 10) As Oct,
      Sum(Month(b.StatusSubmittedDate) = 11) As Nov,
      Count(b.CaseID) As Total,
    From tblcontacts a 
    Inner Join tblcases b 
    On a.ContactID = b.ContactAssignedTo
    Where
      b.StatusSubmittedDate > '2012-01-01'
    Group By
      a.ContactFullName With Rollup
    Having
      Sum(b.CaseCommission) > 0.01)b)c

Answer 2

您不能在一个简单的查询中做到这一点。

正确的方法是执行第二个查询，不带where子句。真的。

如果必须在一个查询中执行此操作，请至少使用联合：

/* old query */
SELECT a,b,c from t1,t2,t3 where d=e group by a having b>f
UNION
select 'total',COUNT(*),NULL /*need the same amount of rows*/
from t1,t2,t3 

并让您的客户端以不同的方式对待a='total'所在的行。但那只是个小技巧。我建议您使用两个查询。