select (case table1.onetest when 'A' then table2.twotest end) A, (case table1.onetest when 'B' then table2.twotest end) B, (case table1.onetest when 'C' then table2.twotest end) C, (case table1.onetest when 'D' then table2.twotest end) D, (case table1.onetest when 'E' then table2.twotest end) E from table1 left join table2 on table1.id = table2.oneid group by table1.onetest order by table1.id limit 4;
........ 假如 我1表onetest里有N个记录 是不是就要写N个 (case table1.onetest when 'A' then table2.twotest end) A, 这样的语句 ?
select b.* from table1 a, table2 b where a.id = b.oneid limit 4;
已知第一张表里的字段是 ID
onetese(第一张表的内容) 第2张表的字段是 ID
oneid(第一张表的ID) twotest(第二张表的内容)
查询第二张表里 列出每种oneid最新发布的4条记录
select b.* from table1 a, table2 b where a.id = b.oneid order by b.id desc limit 4;
ID (1,2,3,4,5)onetese(A,B,C,D,E) 2表
ID(1,2,3,4,5,6,7,8,9,10)oneid(1,1,1,2,2,2,3,3,4,4)twotest(第二张表的内容) 要列出二表的内容 条件是列出1表中的(A,B,C,D,E)对应的最新4条记录 就是要 列出 A的最新4条 B的最新4条 C的最新4条 D的最新4条 E的最新4条
select
(case table1.onetest when 'A' then table2.twotest end) A,
(case table1.onetest when 'B' then table2.twotest end) B,
(case table1.onetest when 'C' then table2.twotest end) C,
(case table1.onetest when 'D' then table2.twotest end) D,
(case table1.onetest when 'E' then table2.twotest end) E
from table1 left join table2 on table1.id = table2.oneid
group by table1.onetest order by table1.id limit 4;