我使用phpgrid显示查询数据，然后通过页面上的表单选择进行过滤。在我向查询添加with rollup以对列进行求和之前，这种方法可以正常工作，我想知道是否有其他方法对这些列进行求和（其中12列）？
告诉我无法执行查询是行不通的。where子句中的未知列“i.signedupdate”
SELECT * FROM
(SELECT IFNULL(c.Adviser, 'GRAND TOTAL') AS Adviser,
Sum(Month(i.SignedUpDate) = 1) As Jan,
Sum(Month(i.SignedUpDate) = 2) As Feb,
Sum(Month(i.SignedUpDate) = 3) As Mar,
Sum(Month(i.SignedUpDate) = 4) As Apr,
Sum(Month(i.SignedUpDate) = 5) As May,
Sum(Month(i.SignedUpDate) = 6) As Jun,
Sum(Month(i.SignedUpDate) = 7) As Jul,
Sum(Month(i.SignedUpDate) = 8) As Aug,
Sum(Month(i.SignedUpDate) = 9) As Sept,
Sum(Month(i.SignedUpDate) = 10) As Oct,
Sum(Month(i.SignedUpDate) = 11) As Nov,
Sum(Month(i.SignedUpDate) = 12) As Dece,
i.id,
Count(i.id) As Total
From
tbl_lead i Inner Join
tbl_clients c On i.client_id = c.client_id
Group By
c.Adviser with rollup) As t这个工程，我可以选择年确定，但没有列摘要
SELECT * FROM
c.Adviser AS Adviser,
Sum(Month(i.SignedUpDate) = 1) As Jan,
Sum(Month(i.SignedUpDate) = 2) As Feb,
Sum(Month(i.SignedUpDate) = 3) As Mar,
Sum(Month(i.SignedUpDate) = 4) As Apr,
Sum(Month(i.SignedUpDate) = 5) As May,
Sum(Month(i.SignedUpDate) = 6) As Jun,
Sum(Month(i.SignedUpDate) = 7) As Jul,
Sum(Month(i.SignedUpDate) = 8) As Aug,
Sum(Month(i.SignedUpDate) = 9) As Sept,
Sum(Month(i.SignedUpDate) = 10) As Oct,
Sum(Month(i.SignedUpDate) = 11) As Nov,
Sum(Month(i.SignedUpDate) = 12) As Dece,
i.id,
Count(i.id) As Total
From
tbl_lead i Inner Join
tbl_clients c On i.client_id = c.client_id
Group By
c.Adviser如果我只是删除子查询并保留'with rollup'，它会提醒我order by&with rollup的使用不正确吗？
如果你有什么好主意，只需要把所有的专栏加起来就行了
1条答案
按热度按时间pgky5nke1#
您应该使用这里推荐的sql视图。
当sql很复杂时，建议使用sql视图。它本质上是一个虚拟表，它被定义为一个带连接的sql select查询。因为数据库视图类似于由行和列组成的数据库表，所以您可以像查询实际的数据库表一样查询数据。
另一个好处是可以重用此视图。此外，如果使用视图而不是联接表，将来如果需要更改列，则可以轻松地进行更改，并且只需要更改sql视图。