结构如下,只有3个字段mysql> select * from vtable;
+----+--------+-------+
| id | field | value |
+----+--------+-------+
| 1 | author | K |
| 1 | book | cpp |
| 1 | price | $60 |
| 2 | author | W |
| 2 | book | PHP |
| 2 | price | $50 |
+----+--------+-------+
6 rows in set (0.00 sec)mysql> select author_table.id, author, book from
-> (select id, `value` as `author` from vtable where `field`='author') as author_table,
-> (select id, `value` as `book` from vtable where `field`='book') as book_table
-> where author_table.id=book_table.id;
+----+--------+------+
| id | author | book |
+----+--------+------+
| 1 | K | cpp |
| 2 | W | PHP |
+----+--------+------+
2 rows in set (0.00 sec)mysql>
比如我想查询id, author, book sql语句用上面的例子当然可以,不知道有没有更好的。或者更通用的,我想把price也查出来就必须再加一条sql另外这种数据库有没有什么专有名词? 谢谢
+----+--------+-------+
| id | field | value |
+----+--------+-------+
| 1 | author | K |
| 1 | book | cpp |
| 1 | price | $60 |
| 2 | author | W |
| 2 | book | PHP |
| 2 | price | $50 |
+----+--------+-------+
6 rows in set (0.00 sec)mysql> select author_table.id, author, book from
-> (select id, `value` as `author` from vtable where `field`='author') as author_table,
-> (select id, `value` as `book` from vtable where `field`='book') as book_table
-> where author_table.id=book_table.id;
+----+--------+------+
| id | author | book |
+----+--------+------+
| 1 | K | cpp |
| 2 | W | PHP |
+----+--------+------+
2 rows in set (0.00 sec)mysql>
比如我想查询id, author, book sql语句用上面的例子当然可以,不知道有没有更好的。或者更通用的,我想把price也查出来就必须再加一条sql另外这种数据库有没有什么专有名词? 谢谢
MAX(IF(field='author',value,null)) as author,
MAX(IF(field='book',value,null)) as book,
MAX(IF(field='price',value,null)) as price
from vtable
group by id
MySQL交叉表