SQL WITH AS用法实列 - 苏飞论坛

[SQL] 纯文本查看 复制代码

CREATE TABLE UserLog

(

    UserId INT NOT NULL,

    UpdateDate DATETIME NOT NULL DEFAULT GETDATE(),

    [Action] VARCHAR(300) NOT NULL

)

GO

INSERT INTO UserLog(UserId,[Action])VALUES(1,'开始')

INSERT INTO UserLog(UserId,[Action])VALUES(1,'麦仁')

INSERT INTO UserLog(UserId,[Action])VALUES(1,'理事')

INSERT INTO UserLog(UserId,[Action])VALUES(1,'结束')

INSERT INTO UserLog(UserId,[Action])VALUES(2,'开始')

INSERT INTO UserLog(UserId,[Action])VALUES(2,'麦仁')

INSERT INTO UserLog(UserId,[Action])VALUES(3,'理事')

INSERT INTO UserLog(UserId,[Action])VALUES(4,'结束')



INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-99,GETDATE()),'开始')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-98,GETDATE()),'麦仁')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-97,GETDATE()),'理事')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-96,GETDATE()),'结束')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(2,DATEADD(hh,-95,GETDATE()),'开始')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(2,DATEADD(hh,-94,GETDATE()),'麦仁')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(3,DATEADD(hh,-93,GETDATE()),'理事')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(4,DATEADD(hh,-92,GETDATE()),'结束')

GO

SELECT * FROM UserLog ul

GO

--显示数据如下：

[SQL] 纯文本查看 复制代码

--第一步 根据用户分组 取日期最大值

WITH L AS 

(

    SELECT ul.UserId,convert(varchar(10),MAX(ul.UpdateDate),120) UpdateDate FROM UserLog ul GROUP BY ul.UserId

)

--第二步 左外连接 主表 匹配 用户和日期

SELECT ul.* FROM L l LEFT JOIN UserLog ul 

ON l.UserId = ul.UserId AND CONVERT(VARCHAR(10),ul.UpdateDate,120) = l.UpdateDate

GO

--显示数据如下：

[SQL] 纯文本查看 复制代码

CREATE TABLE Menu

(

    Id INT IDENTITY(1,1),

    Parent INT NOT NULL,

    [Name] VARCHAR(20) NOT null

)

GO

INSERT INTO Menu(Parent,[Name]) VALUES(0,'字典')

INSERT INTO Menu(Parent,[Name]) VALUES(1,'人')

INSERT INTO Menu(Parent,[Name]) VALUES(2,'男人')

INSERT INTO Menu(Parent,[Name]) VALUES(2,'女人')



--简化嵌套

WITH people AS 

(

    SELECT * FROM Menu m

)

SELECT * FROM people

go

--递归查询 父子类关系

WITH people AS 

(

    SELECT m.Id,m.Parent, m.[Name] FROM Menu m WHERE m.Id = 2

    UNION ALL

    SELECT m.Id, m.Parent, m.[Name] FROM Menu m INNER JOIN people p ON m.Parent = p.id

)

SELECT * FROM people

go