thank you
but after i read it, I try the second example
Pivot table using a JOIN
Problem: You have table user_class(user_id int, class_id char(20), class_value
char(20)) with these rows:
user_id class_id class_value
1 firstname Rogier
1 lastname Marat
2 firstname Jean
2 lastname Smith
and you wish create this resultset:
user_id firstname lastname
1 Rogier Marat
2 Jean Smith
This query accomplishes the required pivot table via an INNER JOIN:
SELECT
t1.user_ID,
class_value AS firstname,
t2.lastname
FROM user_class AS t1
INNER JOIN (
SELECT
user_ID,
class_value AS lastname
FROM user_class
WHERE class_id='lastname'
) AS t2
ON t1.user_ID=t2.user_ID AND t1.class_id='firstname'
but,the query cannot run on my mysql 4.0.23
it returns:
SQL 查询 :
SELECT t1.user_ID, class_value AS firstname, t2.lastname
FROM user_class AS t1
INNER JOIN (
SELECT user_ID, class_value AS lastname
FROM user_class
WHERE class_id = 'lastname'
) AS t2 ON t1.user_ID = t2.user_ID AND t1.class_id = 'firstname'
LIMIT 0 , 30
MySQL 返回:
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'SELECT user_ID, class_value AS lastname
FROM user_class
WHERE c
How can i fix it? and thank you for your help.
---------------------------------
DO YOU YAHOO!?
雅虎免费G邮箱－中国第一绝无垃圾邮件骚扰超大邮箱
