面向初学者的Sql行到列帮助

Question

我是来学习的。我需要一个用于以下场景的最佳实践。没有sql经验..。

表：

Id   Second_id   Wanted_rows
10   61          Blue
10   65          999-JHD
10   70          Gasoline
11   61          Red
11   65          786-FDX
11   70          Disel

我要去拿这个

Car_id   Color   Engine     Plate
10       Blue    Gasoline   999-JHD
11       Red     Disel      786-FDX

Answer 1

你可以试试这个：

select t1.id, t1.wanted_rows as color, t2.wanted_rows as engine, t3.wanted_rows as Plate  
  from data t1
    inner join data t2 on t2.id = t1.id and t2.second_id = 70
    inner join data t3 on t3.id = t1.id and t3.second_id = 65
  where  t1.second_id = 61
order by t1.id

或

select t1.id, t1.wanted_rows as color,
(select wanted_rows from data where id = t1.id and second_id = 70) as engine,
(select wanted_rows from data where id = t1.id and second_id = 65) as plate
  from data t1 
  where  t1.second_id = 61 
order by t1.id

你必须把你的表名放在哪里。

Answer 2

试一试(我在记事本上把这个搞砸了)..

SELECT Car_id as a.id, Color As a.Wanted_rows, Engine As b.Wanted_rows,  Plate As b.Plate
FROM TableA 
INNER JOIN TableB ON TableA

然后从那里看小组等等。