SQL自连接多次 [英] SQL self join multiple times

问题描述

我有一个存储星期条目的数据库表.

I have a single database table that stores week entries.

Id        Value     WeekId
1         1.0000    1
2         2.0000    1

同一星期最多可以有三个条目.

There can be up to three entries with the same week.

所以我想使用自连接可以解决这个问题

So I figured using a self join would solve this

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

问题:使用最大条目数后，它会正常工作，但不会拉回包含一个或两个条目的行.

The problem: It worls fine with the maximum number of entries however it doesn't pull back a row with one or two entries.

是否可以使用其他类型的联接来拉回仅包含一个或两个条目的行，或者采用不同的方式来实现这一点?

Is there a different type of join I can use to pull back a row with only one or two entries or a different way of approaching this?

推荐答案

这些条目没有返回，因为当联接表返回NULL值时，您的WHERE子句明确地将它们过滤掉.

These entries are not returning because your WHERE clause explicitly filters them out when the joined tables return NULL values.

此解决方案向每个记录添加一个连续的行号，每周重新开始为1.这使您可以在PIVOT语句中使用此顺序号

This solution adds a sequential rownumber to each record, restarting to 1 for each week. This allows you to use this sequential number in a PIVOT statement

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

这篇关于SQL自连接多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！