1.子查询
select t1.U_ID as 编号,t1.U_Username as 姓名,
性别=(select t2.v_value from user_value t2 where t2.U_ID=t1.U_ID and t2.V_Name='性别'),
年龄=(select t2.v_value from user_value t2 where t2.U_ID=t1.U_ID and t2.V_Name='年龄'),
Email=(select t2.v_value from user_value t2 where t2.U_ID=t1.U_ID and t2.V_Name='Email')from users t1
-------------------------------------
select t1.U_ID as 编号,t1.U_Username as 姓名,
性别=(select t2.v_value from user_value t2 where t2.U_ID=t1.U_ID and t2.V_Name='性别'),
年龄=(select t2.v_value from user_value t2 where t2.U_ID=t1.U_ID and t2.V_Name='年龄'),
Email=(select t2.v_value from user_value t2 where t2.U_ID=t1.U_ID and t2.V_Name='Email')from users t1
-------------------------------------
As
Select A.U_Id,U_UserName,V_Name, V_Value
From Users A,User_Value B
Where A.u_Id=B.U_ID
你这样没有用的,没有把表2的行转成列。
into temp_table from usersupdate temp_table
set 性别=(case when User_Value.V_Name='性别' then User_Value.V_Value
),年龄=(case when User_Value.V_Name='年龄' then User_Value.V_Value
),Email=(case when User_Value.V_Name='Email' then User_Value.V_Value
) from User_Value where temp_table.编号=User_Value.U_IDselect * from temp_table
select a._UID, a.U_Username , b.V_Value , c.V_Value , d.V_Value from Users a , User_Value b , User_Value c , User_Value d where
a.U_ID = b.U_ID and b.V_name = '性别' and
a.U_ID = c.U_ID and c.V_name = '年龄' and
a.U_ID = d.U_ID and d.V_name = 'EMail' 这样应该可以,不知道有没有更好的方法。
select distinct b.v_name from users a, user_value b where a.v_id = b.v_id;
那在语句中就不用写死了。不知可否行得通?只是想法,还没实现。
select t1.U_ID as 编号,t1.U_Username as 姓名,
性别=(select t2.v_value from (select u.v_value from x,user_value u where x.u_id=u.u_id) t2 where t2.U_ID=t1.U_ID and t2.V_Name='性别'),
年龄=(select t2.v_value from (select u.v_value from x,user_value u where x.u_id=u.u_id) t2 where t2.U_ID=t1.U_ID and t2.V_Name='年龄'),
Email=(select t2.v_value from (select u.v_value from x,user_value u where x.u_id=u.u_id) t2 where t2.U_ID=t1.U_ID and t2.V_Name='Email')from users t1
另外
若你你的user_value标仅仅有id或仅仅用id的话,而且user_value也有u_id的话,那么user_value表就不要用了,用X就行了