Join
JOIN 和 GROUP BY 與附加列
如果我在我的數據庫上執行以下查詢,我會得到下面粘貼的結果:
SELECT t.id as ID, j.text as Text, l.location as Location, FROM [text] t WITH (nolock) INNER JOIN Text_Location tl WITH (nolock) on tl.textid = t.id INNER JOIN Location l WITH (nolock) on tl.locationId = l.id ID Text Location ---------- ---------- ---------- 1 FC London 1 FC Paris 2 KG Berlin 3 TX1 Roma 3 TX1 Vienna 3 TX1 Dublin
如何獲得按 ID 分組並具有不同位置列的相同結果?我想得到這樣的東西:
ID TEXT LOCATION1 LOCATION2 LOCATION3 ---------- ---------- ---------- ---------- ---------- 1 FC London Paris 2 KG Berlin 3 TX1 Roma Vienna Dublin
似乎您需要像這樣執行一些 CASE 語句以及一些 UNION ALL :
SELECT id, CASE WHEN idcounter <=1 THEN text1 ELSE "" end AS text1, CASE WHEN idcounter <=1 THEN location1 ELSE "" end AS location1, CASE WHEN idcounter <=2 THEN text2 ELSE "" end AS text2, CASE WHEN idcounter <=2 THEN location2 ELSE "" end AS location2, CASE WHEN idcounter <=3 THEN text3 ELSE "" end AS text3, CASE WHEN idcounter <=3 THEN location3 ELSE "" end AS location3 FROM ( SELECT t.id AS id, Count(t.id) AS idcounter group BY t.id ) AS results1 UNION ALL SELECT t.id AS id, j.text AS text1, l.location AS location1, FROM [text] t WITH (nolock) INNER JOIN text_location tl WITH (nolock) ON tl.textid = t.id INNER JOIN location l WITH (nolock) ON tl.locationid = l.id ORDER BY l.location LIMIT 1 UNION ALL SELECT t.id AS id, j.text AS text2, l.location AS location3, FROM [text] t WITH (nolock) INNER JOIN text_location tl WITH (nolock) ON tl.textid = t.id INNER JOIN location l WITH (nolock) ON tl.locationid = l.id ORDER BY l.location LIMIT 1 offset 1 UNION ALL SELECT t.id AS id, j.text AS text3, l.location AS location3, FROM [text] t WITH (nolock) INNER JOIN text_location tl WITH (nolock) ON tl.textid = t.id INNER JOIN location l WITH (nolock) ON tl.locationid = l.id ORDER BY l.location LIMIT 1 offset 2
如果您提供 SQLFiddle 或類似的東西,我們可以使用它。
您可以以行號為軸。不過,這對於大量列來說可能很乏味。
SELECT ID, [Text], [1] AS Location1, [2] AS Location1, [3] AS Location3 FROM ( SELECT t.id as ID, j.text as Text, l.location as Location, rn = ROW_NUMBER() OVER (PARTITION BY ID, [Text] ORDER BY (SELECT 1)) FROM [text] t WITH (nolock) INNER JOIN Text_Location tl WITH (nolock) on tl.textid = t.id INNER JOIN Location l WITH (nolock) on tl.locationId = l.id ) l PIVOT ( MIN(l.Location) FOR rn IN ([1],[2],[3]) ) pvt