select Dish.Dish_Name as '菜品名称',count(DishID) as '当天售出量' from DishStat
left join Dish on Dish.Dish_ID=DishStat.DishID
group by DishID order by '当天售出量' desc
总是说:列 'dish.Dish_Name' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
去掉Dish.Dish_Name as '菜品名称',就行了,但是 Dish.Dish_Name这个字段在Dish表中是确实存在的啊?请教一下大家这是什么原因?
left join Dish on Dish.Dish_ID=DishStat.DishID
group by DishID order by '当天售出量' desc
总是说:列 'dish.Dish_Name' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
去掉Dish.Dish_Name as '菜品名称',就行了,但是 Dish.Dish_Name这个字段在Dish表中是确实存在的啊?请教一下大家这是什么原因?
left join Dish on Dish.Dish_ID=DishStat.DishID
group by Dish.Dish_Name order by '当天售出量' desc--这样呢
left join Dish on Dish.Dish_ID=DishStat.DishID
group by DishID,Dish_Name order by '当天售出量' desc
[Dish_ID] [char](2) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Dish_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Dish_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
CREATE TABLE [dbo].[DishStat](
[DishID] [char](2) COLLATE Chinese_PRC_CI_AS NOT NULL,
[NNNN] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[DishID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
--测试
select Dish.Dish_Name as '菜品名称',count(DishID) as '当天售出量' from DishStat
left join Dish on Dish.Dish_ID=DishStat.DishID
group by Dish_Name order by '当天售出量' desc
成功的执行了查询
问题:
你使用Group by 时候,一定要注意,group by的字段是你select的中的但不是count的字段。