/>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
>
>
Attachment: [text/html]
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
