数据表中存在这样一个字符串字段 如果才能按长度排序呢?比如:
123456
abcdefg
gea
de实现:
de
gea
123456
abcdefg实现这样的排序!还有就是,
组合查询的,
如:select * from image where title like '%XXXX%' order by id desc
与 select * from image where keyword like '%XXXX%' order by id desc
组合一起,先排完第一条,然后再排第二条的,第二条的记录不包含第一条中的记录,就是不重复的意思呀!如何实现呢? 是用union 吗?
123456
abcdefg
gea
de实现:
de
gea
123456
abcdefg实现这样的排序!还有就是,
组合查询的,
如:select * from image where title like '%XXXX%' order by id desc
与 select * from image where keyword like '%XXXX%' order by id desc
组合一起,先排完第一条,然后再排第二条的,第二条的记录不包含第一条中的记录,就是不重复的意思呀!如何实现呢? 是用union 吗?
2:不是很明白
1,select * from image where order by length(title) asc;2, -- 用and或者用union都可以,如下
-- 2.1
select a.*
from(
select * from image where title like '%XXXX%'
union
select * from image where keyword like '%XXXX%'
)a order by a.id desc;
-- 2.2
select * from image where title like '%XXXX%' and keyword like '%XXXX%' order by id desc
-- 2.1
select a.*
from(
select * from image where title like '%XXXX%'
union
select * from image where keyword like '%XXXX%'
)a order by a.id desc;
-- 2.2
select * from image where title like '%XXXX%' and keyword like '%XXXX%' order by id desc
直接用union不会乱了吧?
不会啊,外面有order by id desc排序啊然后union自动过滤重复数据。
union all 不会过滤。
id title keyword
1 55555 12345
2 66666 45631
3 77777 88888
4 85466 22222比如要查询包5的,最后要得到这样的结果呀,按id排序
id title keyword
1 55555 12345
4 85466 22222
2 66666 45631基本就是这样了呀!
union
SELECT * FROM (select * from image where keyword like '%XXXX%' order by a.id desc) B
;
union
select * from image where keyword like '%XXXX%' order by id desc这个不就可以吗,
select id,title,keyword,if(instr(title,5)>0,1,0) as t_id, if(instr(keyword,5)>0,1,0) as k_id
from asd
order by t_id desc,k_id desc ,id
) aa
补充:
select id,title,keyword from (
select id,title,keyword,if(instr(title,5)>0,1,0) as t_id, if(instr(keyword,5)>0,1,0) as k_id
from asd
order by t_id desc,k_id desc ,id
) aa where t_id<>0 or k_id<>0
呵呵,是的,union 不能和order by 一起使用,以前没注意,现在学习了