日期:2014-05-18  浏览次数:20595 次

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 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
    )
)t
where 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()你都可以这么搞。