create table product(
id int,
name varchar(20),
price int,
area varchar(20),
sales int
);
insert into product values(001,'SANXING',3500,'BJ',800);
insert into product values(001,'SANXING',3500,'SH',300);
insert into product values(002,'iPhone',5000,'BJ',600);
insert into product values(002,'iPhone',5000,'SH',1800);
insert into product values(003,'iPad',3000,'BJ',900);
insert into product values(004,'iPad',3000,'SH',1900);
+------+---------+-------+------+-------+
| id | name | price | area | sales |
+------+---------+-------+------+-------+
| 1 | SANXING | 3500 | BJ | 800 |
| 1 | SANXING | 3500 | SH | 300 |
| 2 | iPhone | 5000 | BJ | 600 |
| 2 | iPhone | 5000 | SH | 1800 |
| 3 | iPad | 3000 | BJ | 900 |
| 4 | iPad | 3000 | SH | 1900 |
+------+---------+-------+------+-------+
6 rows in set (0.00 sec)
先显示北京地区,按照销售额排名
要求显示结果为:
+------+---------+-------+------+-------+
| id | name | price | area | sales |
+------+---------+-------+------+-------+
| 1 | iPad | 3000 | SH | 1900 |
| 1 | iPhone | 5000 | SH | 1800 |
| 1 | SANXING | 3500 | SH | 300 |
| 1 | iPhone | 5000 | BJ | 600 |
| 1 | iPad | 3000 | BJ | 600 |
| 1 | SANXING | 3500 | BJ | 600 |
+------+---------+-------+------+-------+
按地区排序?######恩!先按地区,再按每个地区的sales销量######不就是area desc ,sales desc 吗? 不知道是否这样的理解哈。 呵呵!###### select * from (select * from product where area = 'SH' order by sales desc) as t
union
select * from (select * from product where area = 'BJ' order by sales desc) as temp;
######
select * from #product order by case when area = 'SH' then 0 else 1 end , area, sales desc
######@William : YES 成功了######@Missinging静 : 這個sql server 裏的寫放, 你看把後面那兩行放到一個試試? 也就是order by 後面的放一行######这个我测试的不对嘛,最后一行的area前面真的有个逗号吗######你這要求和顯示結果明顯不一樣啊? 先顯示北京地區?
如果是北京地區, 把上面的 area = 'SH' 改成 area = 'BJ'######哦,对,应该换个排序,是我失误了。。。######看这个会不会更简单
select id,name,price,area,sales , (case when area = 'SH' then 0 else 1 end ) as order_sort from product order by order_sort, sales desc
######平时没用过case 这个关键字。。。学习了。。######
select id,name,price,area,sales , (case when area = 'SH' then 0 else 1 end ) as order_sort from product order by order_sort, sales desc
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。