SQL WITH AS用法实列

惜

SQL 查询所有用户最后一天的数据

[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
--显示数据如下：

OK 想要的数据就这样查询出来了。
下面是另一个SQL CTE WITH AS 用法

[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
 

站长苏飞

你敢再说的明白一点吗。

liu

楼主加油，我们都看好你哦。

竹林风

真是难得给力的帖子啊。

Amy

很不错的帖子，继续加油

范范

  需要个详解

范范

楼主加油， sql没有看的太懂，等用到的时候实例测试下好了