CREATEPROCEDUREdbo.pdirinfo@uservarchar(20),@parentpathvarchar(255)ASSELECT*FROM(SELECTd.[user],d.path,d.parentpath,d.dir,d.createtime,ISNULL(SUM(f.[count]),0)AS[count],ISNULL(SUM(f.[size]),0)AS[size]FROMdbo.tdirectoriesdLEFTO

Question

CREATE PROCEDURE dbo.p_dir_info @user varchar(20),@parent_path varchar(255) AS
SELECT *
FROM (SELECT d.[user],d.path ,d.parent_path,d.dir,d.create_time,
ISNULL(SUM(f.[count]),0) AS [count],ISNULL(SUM(f.[size]),0) AS [size]
FROM dbo.t_directories d LEFT OUTER JOIN
(SELECT path,COUNT([file]) AS [count],SUM([size]) AS [size]
FROM t_files
GROUP BY path) f ON d .path = f.path
WHERE d.[user] = @user and d.parent_path=@parent_path GROUP BY d.path ) dir ORDER BY create_time
其中table为
CREATE TABLE [dbo].[t_directories] (
[id] [int] IDENTITY (1,1) NOT NULL ,
[user] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[path] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[parent_path] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dir] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[create_time] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[t_files] (
[id] [int] IDENTITY (1,1) NOT NULL ,
[user] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[file] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[path] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[size] [bigint] NOT NULL ,
[upload_time] [datetime] NOT NULL
) ON [PRIMARY]

Answer 1

d.[user], d.path , d.parent_path, d.dir, d.create_time,这几个没有出现在group by中,是没有办法取得到的,要想得到就要在group by中加上他们,或者用聚合
你如果原意是只想根据path分组而又想得到上面几个字段的换还是加个游标吧