SQL选择列表中的列 'PB_Station.ID' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中,该如何处理

SQL选择列表中的列 'PB_Station.ID' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

SQL code

ALTER procedure [dbo].[PB_GetStationList]@StaionName nvarchar(50),@AreaID int,@StartDate datetime,@EndDate datetime,@StartIndex int,@PageSize intasselect * from(    select top (@StartIndex+@PageSize)     ROW_NUMBER() over (order by PB_Station.ID ASC) as XUEHAO,    Area.AreaName,    PB_Station.StationName,PB_Station.StationNo,    SUM(case when LeaseOut.TimeIn is null then 1 else 0 end) as OutNum,    SUM(case when LeaseIn.TimeIn is not null then 1 else 0 end) as InNum    from PB_Station    inner join PB_Area Area on Area.ID=PB_Station.Area    inner join PB_MgrBox MgrBox on MgrBox.Station=PB_Station.ID    inner join PB_BPillar BPillar on BPillar.MgrBox=MgrBox.ID    left join PB_Lease LeaseOut on LeaseOut.BPillarIDOut=BPillar.ID    left join PB_Lease LeaseIn on LeaseIn.BPillarIDIn=BPillar.ID    where    (        PB_Station.StationName like '%'+@StaionName+'%' and         Area.ID=@AreaID and        DATEDIFF(day,LeaseOut.TimeOut,@StartDate)<=0 and DATEDIFF(day,LeaseOut.TimeOut,@EndDate)>=0 or        DATEDIFF(day,LeaseIn.TimeIn,@StartDate)<=0 and DATEDIFF(day,LeaseIn.TimeIn,@EndDate)>=0    ))twhere XUEHAO>@StartIndex

这个存储过程我应该怎样修改? 貌似将sum()那两句注释掉就没有错误了,可是我必须要统计数量,写在外面吗?怎样写?求帮助...

[解决办法]

ALTER procedure [dbo].[PB_GetStationList]
@StaionName nvarchar(50),
@AreaID int,
@StartDate datetime,
@EndDate datetime,
@StartIndex int,
@PageSize int
as
select * 
from
(
select top (@StartIndex+@PageSize) 
ROW_NUMBER() over (order by PB_Station.ID ASC) as XUEHAO,
Area.AreaName,
PB_Station.StationName,PB_Station.StationNo,
SUM(case when LeaseOut.TimeIn is null then 1 else 0 end) as OutNum,
SUM(case when LeaseIn.TimeIn is not null then 1 else 0 end) as InNum
from PB_Station
inner join PB_Area Area on Area.ID=PB_Station.Area
inner join PB_MgrBox MgrBox on MgrBox.Station=PB_Station.ID
inner join PB_BPillar BPillar on BPillar.MgrBox=MgrBox.ID
left join PB_Lease LeaseOut on LeaseOut.BPillarIDOut=BPillar.ID
left join PB_Lease LeaseIn on LeaseIn.BPillarIDIn=BPillar.ID
where
(
PB_Station.StationName like '%'+@StaionName+'%' and 
Area.ID=@AreaID and
DATEDIFF(day,LeaseOut.TimeOut,@StartDate)<=0 and DATEDIFF(day,LeaseOut.TimeOut,@EndDate)>=0 or
DATEDIFF(day,LeaseIn.TimeIn,@StartDate)<=0 and DATEDIFF(day,LeaseIn.TimeIn,@EndDate)>=0
)
group by Area.AreaName,PB_Station.StationName,PB_Station.StationNo
)t
where XUEHAO>@StartIndex

sum为聚合函数，和它一起出现的列必须包含在group by 后面
[解决办法]

探讨

引用:
ALTER procedure [dbo].[PB_GetStationList]
@StaionName nvarchar(50),
@AreaID int,
@StartDate datetime,
@EndDate datetime,
@StartIndex int,
@PageSize int
as
select *
from……

[解决办法]

探讨

SQL code
group by Area.AreaName,PB_Station.StationName,PB_Station.StationNo

把Area.AreaName放进group by里可以,因为只有一个Area.AreaName.
可是把PB_Station.StationName,PB_Station.StationNo 这两个放进group by里,
这样su……

[解决办法]
SUM(case when LeaseOut.TimeIn is null then 1 else 0 end) OVER (PARTITION BY Area.AreaName)
[解决办法]
两句SUM()你都可以这么搞。

[解决办法]
（小鸟飞过..）我表示你这些代码太复杂了，看的懂的都是大鸟...

查看更多 下一篇