select语句where子句二的条件
我在最后的select语句有问题。我想要做的是选择两个级别,但即时通过此声明没有结果。请注意,如果@Level为1,我不想低于1,否则可能会混淆结果,因此可能会出现一个条件以及select语句where子句二的条件
它所在的部分@Level = @Level -1我是具有
FROM c WHERE c.Level = @Level AND c.Level = @Level - 1 ORDER BY Level ASC
或这一个
FROM c WHERE c.Level BETWEEN @Level AND c.Level - 1 ORDER BY Level ASC
SQL问题
DECLARE @Department TABLE
(
Id INT NOT NULL,
Name varchar(50) NOT NULL,
ParentId int NULL
)
INSERT INTO @Department SELECT 1, 'Toys', null
INSERT INTO @Department SELECT 2, 'Computers', null,
INSERT INTO @Department SELECT 3, 'Consoles', 2
INSERT INTO @Department SELECT 4, 'PlayStation 3', 3
INSERT INTO @Department SELECT 5, 'Xbox 360', 2
INSERT INTO @Department SELECT 6, 'Games', 1
INSERT INTO @Department SELECT 7, 'Puzzles', 6
INSERT INTO @Department SELECT 8, 'Mens Wear', null
INSERT INTO @Department SELECT 9, 'Mens Clothing', 8
INSERT INTO @Department SELECT 10, 'Jackets', 9
INSERT INTO @Department SELECT 11, 'Shoes', 9
INSERT INTO @Department SELECT 12, 'Rain Coats', 10
DECLARE @Level int = 4
;WITH c AS
(
SELECT Id, ParentId, Name, 1 AS Level
FROM @Department
WHERE ParentId IS NULL
UNION ALL
SELECT t.Id, t.ParentId, T.Name, c.Level + 1 AS Level
FROM @Department T
INNER JOIN c on t.ParentId = c.Id
)
SELECT Id, ParentId, Name, Level
FROM c WHERE c.Level = @Level AND c.Level = @Level - 1 ORDER BY Level ASC
FROM c WHERE c.Level BETWEEN @Level AND @Level - 1 ORDER BY Level ASC
也许FROM c WHERE c.Level BETWEEN @Level -1 AND @Level ORDER BY Level ASC
这里这段代码是NEVER能工作:
@Level = @Level - 1
我想你的意思是说:
c.Level = @Level - 1
即使在那个......你可能要一个OR
而不是一个AND
。
想想你在问什么......“给我所有的记录,同时水平是4和5。”
你说的意思可能是......“给我所有关于4或5的记录。”
不行,我试了一下 – ONYX 2011-12-22 22:12:00
看一看下面的SQL,它具有4行3级和1回来为4级:
SELECT p.Id as ParentId,
p.Name as Parent,
c.Id,
c.Name,
c.Level
FROM c as c
left join c p
on p.Id = c.ParentId
WHERE c.Level = @Level
ORDER BY p.Name, c.Name ASC
好了,你的递归CTE似乎是正确的。我通常不会把name
放在CTE中(主要是因为这个表只是父/子字段,或者有太多),然后加入,但它仍然可以正常工作。
正如已经指出的那样,你真正的问题是,你的where
子句是无意义的:
-
WHERE c.Level = @Level AND c.Level = @Level - 1
基本上询问是否c.level == c.Level - 1
- 这是总是假。 -
WHERE c.Level BETWEEN @Level AND @Level - 1
要求@Level >= c.Level >= @Level - 1
或@Level >= @Level - 1
- 另一个不可能的条件。
这两种方法中的一种应该工作得很好,如果略作修改:
WHERE c.Level = @Level OR c.Level = @Level - 1
WHERE c.Level BETWEEN @Level - 1 AND @Level
(如果@Level
输入为1
没关系 - 因为你一开始,没有更低的报告级别)。
显示你想要的结果。 – JeffO 2011-12-22 23:26:11
如果你只进入一个级别,你可以加入这个表格,并将你的条件放在on子句中。 – JeffO 2011-12-22 23:26:55