在表中查找重复值时出现SQL输出错误

Question

select
count([Item Nbr]) as [Item Nbr],
count([Item Flags]) as [Item Flags],
count(UPC) as UPC,
count([Store Nbr]) as [Store Nbr] ,
count (RetrievalWeek) as RetrievalWeek,
* from tblStoreItemForecast
GROUP BY
[Item Nbr],[Item Flags],UPC,
[Store Nbr], [Week 01 Forecast], [Week 02 Forecast],
[Week 03 Forecast],[Week 04 Forecast],[Week 05 Forecast],
[Week 06 Forecast],[Week 07 Forecast],[Week 08 Forecast],[Week 09 Forecast],
[Week 10 Forecast],[Week 11 Forecast],[Week 12 Forecast],[Week 13 Forecast],
[Week 14 Forecast],[Week 15 Forecast],RetrievalWeek
having (count(*) > 1)
order by RetrievalWeek desc

I got the message error Msg 8120，

第16层，状态1，第76行

tblStoreItemForecast列"。第16周预报'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

Msg 209, 16层，状态1,91行

列名'RetrievalWeek'不明确。

谁能告诉我为什么会这样?

我尝试了另一种方法

如果我运行下面的代码，它不会给我任何结果，因为我在这里使用了记录no(唯一键)

select
[Item Nbr],[Item Flags],UPC,[Store Nbr],RetrievalWeek,recordno,COUNT(*)
from tblStoreItemForecast
GROUP BY
[Item Nbr],[Item Flags],UPC,[Store Nbr],
RetrievalWeek,recordno
having
count(*) > 1
order by RetrievalWeek desc

，但如果我运行下面的代码没有使用记录没有，因为它是唯一的这里它给结果

select
[Item Nbr],[Item Flags],UPC,[Store Nbr],RetrievalWeek,COUNT(*)
from tblStoreItemForecast
GROUP BY
[Item Nbr],[Item Flags],UPC,[Store Nbr],
RetrievalWeek
having
count(*) > 1
order by RetrievalWeek desc

我想计数的重复使用记录没有怎么写不能弄清楚我也使用内连接，但在日志中获得错误空间不能重用。有解决办法吗?

Answer 1

您有一个与表列同名的输出列别名。尝试添加下划线来区分如下。

select
count([Item Nbr]) as [Item Nbr],
count([Item Flags]) as [Item Flags],
count(UPC) as UPC,
count([Store Nbr]) as [Store Nbr] ,
count (RetrievalWeek) as Retrieval_Week,
* from tblStoreItemForecast
GROUP BY
[Item Nbr],[Item Flags],UPC,
[Store Nbr], [Week 01 Forecast], [Week 02 Forecast],
[Week 03 Forecast],[Week 04 Forecast],[Week 05 Forecast],
[Week 06 Forecast],[Week 07 Forecast],[Week 08 Forecast],[Week 09 Forecast],
[Week 10 Forecast],[Week 11 Forecast],[Week 12 Forecast],[Week 13 Forecast],
[Week 14 Forecast],[Week 15 Forecast],RetrievalWeek
having (count(*) > 1)
order by RetrievalWeek desc