我有一个存储周条目的数据库表。
Id Value WeekId
1 1.0000 1
2 2.0000 1
同一周最多可以有三个条目。
所以我想使用自连接可以解决这个问题
SELECT w1.Value, w2.Value, w3.Value
FROM [List].[dbo].[testWeekEntries] as w1
LEFT OUTER JOIN [List].[dbo].[testWeekEntries] as w2 ON w1.WeekId = w2.weekId
LEFT OUTER JOIN [List].[dbo].[testWeekEntries] as w3 ON w2.WeekId = w3.WeekId
WHERE w1.Id < w2.Id AND w2.Id < w3.Id
问题:它在最大条目数下工作正常,但它不会拉回包含一个或两个条目的行。
是否有不同类型的连接可以用来拉回只有一个或两个条目的行,或者是否有不同的方法来解决这个问题?
这些条目不会返回,因为您的WHERE
当连接表返回时,子句显式过滤掉它们NULL
values.
此解决方案向每条记录添加一个连续的行号,每周重新开始为 1。这允许您在 PIVOT 语句中使用此序列号
SQL 2000 语句
SELECT *
FROM (
SELECT (SELECT COUNT(*)
FROM testWeekEntries
WHERE Id <= we.Id
AND WeekId = we.WeekId) as rn
, Value
, WeekId
FROM testWeekEntries we
) q
PIVOT (MAX(Value) FOR rn IN ([1],[2],[3]) ) AS PVT
SQL 2008 语句
;WITH q AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY WeekId ORDER BY Id)
, Id
, Value
, WeekId
FROM [testWeekEntries] as w1
)
SELECT Value
, (SELECT Value FROM q q1 WHERE q1.rn = q.rn + 1 AND q1.WeekId = q.WeekId)
, (SELECT Value FROM q q2 WHERE q2.rn = q.rn + 2 AND q2.WeekId = q.WeekId)
FROM q
WHERE q.rn = 1
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)