group by的问题!

[sdweihailh]

SELECT CustomerID, ItemNumber, ShippedQuantity
FROM dbo.FS_HistoryShipment
WHERE (OrderType = 'C') AND (IssueType = 'I') AND (UserID = 'C14') AND (Stockroom = 'FG') AND (CONVERT(NVARCHAR(10), TransactionDate, 120)
= CONVERT(NVARCHAR(10), GETDATE() - 1, 120))

SELECT CustomerID, ItemNumber, sum(ShippedQuantity) as qty
FROM dbo.FS_HistoryShipment
WHERE (OrderType = 'C') AND (IssueType = 'I') AND (UserID = 'C14') AND (Stockroom = 'FG') AND (CONVERT(NVARCHAR(10), TransactionDate, 120)
= CONVERT(NVARCHAR(10), GETDATE() - 1, 120))
GROUP BY CustomerID, ItemNumber


聚合时候,ItemNumber和CustomerID一样了。
但是其他的可以；搞不懂！SQL2005

[sdweihailh]

SELECT CustomerID ,ItemNumber , ShippedQuantity into #bb FROM dbo.FS_HistoryShipment WHERE (OrderType = 'C') AND (IssueType = 'I') AND (UserID = 'C14') AND (Stockroom = 'FG') AND (CONVERT(NVARCHAR(10), TransactionDate, 120) = CONVERT(NVARCHAR(10), GETDATE() - 1, 120)) select CustomerID ,ItemNumber , sum(ShippedQuantity) as qty from #bb group by CustomerID ,ItemNumber 这样插入临时表，再 group by 可以;但视图的问题出在哪？

[sdweihailh]

select A,B,sum(c) as c from ( select convert(NVARCHAR(10),CustomerID) AS A,CONVERT(NVARCHAR(20),ItemNumber) AS B,ShippedQuantity as c from dbo.FS_HistoryShipment where (OrderType = 'C') AND (IssueType = 'I') AND (UserID = 'C14') AND (Stockroom = 'FG') AND (CONVERT(NVARCHAR(10), TransactionDate, 120) = CONVERT(NVARCHAR(10), GETDATE() - 1, 120)) )z group by A,B

[sdweihailh]

这样可以，此视图从另一个视图来的，另一个视图存在很多自定义函数， ERP后台的，反正没找到原因，问题从侧面解决了。

[Andy__Huang]

SELECT rtrim(CustomerID), rtrim(ItemNumber), sum(ShippedQuantity) as qty
FROM dbo.FS_HistoryShipment
WHERE (OrderType = 'C') AND (IssueType = 'I') AND (UserID = 'C14') AND (Stockroom = 'FG') AND (CONVERT(NVARCHAR(10), TransactionDate, 120)
= CONVERT(NVARCHAR(10), GETDATE() - 1, 120))
GROUP BY rtrim(CustomerID), rtrim(ItemNumber)

可能前后有空格，你这样试一下

[--小F--]

显然是你自己SELECT 的列填成和CUSTOMERID一样了啊 SQL SERVER不至于这样。

[sdweihailh]

不是啊；上面的是执行的代码；没错啊；有点奇怪啊！

[sdweihailh]

前后空格去掉后，还是一样。

[开着拖拉机泡妞]

不至于吧！！ SQL Server不可能犯这种错误的。你好好检查你的代码

[silwol]

是不是你机器上开的东西太多了。我有的时候因为开的东西太多，好像内存不够了，就会出一些莫名其妙的东西，比如一个select不显示结果，只给一句"Command(s) completed successfully."，之类的，一般多关些程序，重启sms就好了

[韶之鱼]

我似乎记得，群组之前是不是要先排序啊，你order下啊

[sdweihailh]

新建表执行没问题；我是在视图上执行的；按理也不至于这样啊。

[zbdzjx]

如果dbo.FS_HistoryShipment是视图，就有可能会这样了。 将这个视图打开，随便修改一下（如加个空格），再保存。再试上面的语句。

[XMPP]

去除前后空格，试试

SELECT     ltrim(rtrim(CustomerID)), ltrim(rtrim(ItemNumber)), sum(ShippedQuantity) as qty
FROM         dbo.FS_HistoryShipment
WHERE     (OrderType = 'C') AND (IssueType = 'I') AND (UserID = 'C14') AND (Stockroom = 'FG') AND (CONVERT(NVARCHAR(10), TransactionDate, 120) 
                      = CONVERT(NVARCHAR(10), GETDATE() - 1, 120))
GROUP BY  ltrim(rtrim(CustomerID)), ltrim(rtrim(ItemNumber))