>but,the query cannot run on my mysql 4.0.23
See http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html
for how to get round the unavailability of subqueries before version
4.1.
PB
-----
维斯 苏 wrote:
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邮箱－中国第一绝无垃圾邮件骚扰超大邮箱
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 8/15/2005
|
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.11/74 - Release Date: 8/17/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
