DECLARE @TheYear int
SET @TheYear = 1998SELECT
CASE GROUPING(o.customerid) WHEN 0
THEN o.customerid ELSE '(Total)' END AS MyCustomerID,
CASE GROUPING(od.orderid) WHEN 0
THEN od.orderid ELSE -1 END AS MyOrderID,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, price
以上可以实现合计的功能,但是我还想显示表中其他的nchar列(不用合计),应该怎么处理阿
有没有处理nchar的聚合函数之类的东西啊
SET @TheYear = 1998SELECT
CASE GROUPING(o.customerid) WHEN 0
THEN o.customerid ELSE '(Total)' END AS MyCustomerID,
CASE GROUPING(od.orderid) WHEN 0
THEN od.orderid ELSE -1 END AS MyOrderID,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, price
以上可以实现合计的功能,但是我还想显示表中其他的nchar列(不用合计),应该怎么处理阿
有没有处理nchar的聚合函数之类的东西啊
--@ID传入的是你的聚合字段CREATE FUNCTION dbo.f_str(@ID varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+Name
FROM tb
WHERE ID=@ID
RETURN(STUFF(@re,1,1,''))
END
ID是指的什么
@ID传给了什么值以至于实现了聚合函数的效果?
但是除了显示聚合的结果,我还需将原来表中nchar字段的内容全部显示
但是现在却都是显示的聚合的结果
那该怎么处理阿?
我只是想在合计的地方显示聚合结果,其余位置还是保持原来的内容