mysql把一个字段多条记录_mysql 一个字段多条记录整合成一条记录多个字段(新字段)...

匿名用户

1级

2013-12-14 回答

如果选出记录个数不固定，那就得动态拼出sql再执行，如果是固定返回3个，可以

with t as (SELECT t.ASSIGNEE_,t.END_TIME_, row_number() over(order by t.ASSIGNEE_) r

FROM act_hi_taskinst t WHERE t.PROC_INST_ID_ = 10621)

select t1.ASSIGNEE_ ASSIGNEE_1,t1.END_TIME_ END_TIME_1,

t2.ASSIGNEE_ ASSIGNEE_2,t2.END_TIME_ END_TIME_2,

t3.ASSIGNEE_ ASSIGNEE_3,t3.END_TIME_ END_TIME_3

from (select * from t where r=1) t1

cross join (select * from t where r=2) t2

cross join (select * from t where r=3) t3

追问：

mysql不支持with...as...

追答：

写是写得出来，不过这样的代码效率很低，因为你的那句sql可能要执行3次

既然结果肯定是3行，那应该在外围代码中将行转列，这样效率要高很多

追问：

我尝试下面的语句 还是不行，临时表貌似不能被多次查询

DROP TEMPORARY TABLE IF EXISTS tr;

create temporary table tr (select t.ASSIGNEE_,t.END_TIME_,(select count(*) from act_hi_taskinst where ID_<=t.ID_) as r

追答：

文档中有描述，临时表不能在一次查询中使用多次

• You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;

ERROR 1137: Can't reopen table: 'temp_table'

This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

• The SHOW TABLES statement does not list TEMPORARY tables.

• You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:

mysql> ALTER TABLE orig_name RENAME new_name;

我估计只能产生多个临时表才能满足你的要求