Question

MySql，查询字段赋值，如下所示：

假设有如下数据：
CREATE TEMPORARY TABLE temp_test SELECT rs.* FROM (
SELECT 'A' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'B' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'C' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'D' title, 2 TYPE FROM DUAL UNION ALL
SELECT 'E' title, 2 TYPE FROM DUAL
)rs;
现在需要select 结果如下(type一样的时候，title加上序号)：
title type
A 1
B 1
C 1
1. D 2
2. E 2
title type
1.A 1
2.B 1
3.C 1
1.D 2
2.E 2
这样的sql该如何写?

Answer 1

直接查询出来结果我没想出来如何做
我给你看下如何分别查type=1和type=2的吧
set @i := 0;
select concat(@i := @i + 1,'.',title) as `title`,type
from (SELECT 'A' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'B' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'C' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'D' title, 2 TYPE FROM DUAL UNION ALL
SELECT 'E' title, 2 TYPE FROM DUAL) a
where type=1
order by title asc;
set @i := 0;
select concat(@i := @i + 1,'.',title) as `title`,type
from (SELECT 'A' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'B' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'C' title, 1 TYPE FROM DUAL UNION ALL
SELECT 'D' title, 2 TYPE FROM DUAL UNION ALL
SELECT 'E' title, 2 TYPE FROM DUAL) a
where type=2
order by title asc;

这样将两个结果集放在一起就是你那个样子了
但是我水平有限。。。不知道这种带参数设定的结果集怎么弄一起