有一张男人和女人的桌子。桌子上有字段,性别有价值观,男人或女人。
TABEL-A
ID SEX GRADE-A GRADE=B
1 men 2 4
2 women 4 5
3 women 6 1
4 men 1 3
5 men 3 6
6 men 5 2
我想把表格排序如下;
首先,只列出2名按等级A DESC.排序的男性
其次,按等级B DESC 列出男女订购的所有剩余部分
预期产出;
ID SEX GRADE-A GRADE=B
6 men 5 2
5 men 3 6
2 women 4 5
1 men 2 4
4 men 1 3
3 women 6 1
因为这两个条件有不同的订购要求,所以我不能使用UNION。UNION也很慢。
这是我失败的查询,第1部分
select distinct t.*
(
(
select *
from TABLE-A a
where a.sex = 'men'
order by a.GRADE-A DESC
LIMIT 2
)
UNION ALL
(
select *
from TABLE-A a
order by a.GRADE-B DESC
)
) t
我也尝试了"当案例"逻辑,但一开始不能限制两个人,
这是我失败的查询,第2部分
select *
from TABLE-A a
order by
case when a.sex = 'men' then a.GRADE-A end DESC, a.GRADE-B DESC
有人支持对此进行适当的查询吗?
向致以最诚挚的问候
尝试此查询:
SELECT a.*
FROM `TABLE-A` a
LEFT JOIN (
SELECT m2.ID
FROM `TABLE-A` m2
WHERE m2.sex = 'men'
ORDER BY m2.`GRADE-A` DESC
LIMIT 2
) m2 ON m2.ID = a.ID
ORDER BY
CASE WHEN m2.ID IS NULL THEN 1 ELSE 0 END,
CASE WHEN m2.ID IS NULL THEN a.`GRADE-B` ELSE a.`GRADE-A` END DESC
在SQL Fiddle上测试
解释:
要了解ORDER BY
是如何工作的,请参阅下表-这里是所有TABLE-A
数据加上查询中某些表达式的值(CASE1指第一个CASE表达式,CASE2-指第二个):
ID SEX GRADE-A GRADE=B m2.ID CASE1 CASE2
1 men 2 4 NULL 1 4
2 women 4 5 NULL 1 5
3 women 6 1 NULL 1 1
4 men 1 3 NULL 1 3
5 men 3 6 5 0 3
6 men 5 2 6 0 5
ORDER BY
按CASE1值(升序)排序,然后按CASE2(降序)排序。所以在排序之后,我们有了预期:
ID SEX GRADE-A GRADE=B m2.ID CASE1 CASE2
6 men 5 2 6 0 5
5 men 3 6 5 0 3
2 women 4 5 NULL 1 5
1 men 2 4 NULL 1 4
4 men 1 3 NULL 1 3
3 women 6 1 NULL 1 1
有关排序行
select distinct t.*
(
(
select *
from TABLE-A a
where a.sex = 'men'
order by a.GRADE-A DESC
LIMIT 2
)
UNION ALL
(
select *
from TABLE-A a
LEFT OUTER JOIN
(select *
from TABLE-A a
where a.sex = 'men'
order by a.GRADE-A DESC
LIMIT 2) table1
ON a.ID = table1.ID
order by a.GRADE-B DESC
)
) t
这将为您提供第一个查询结果,并按您想要的顺序添加缺失的行