如何对记录进行分组后再按字段排序?
有记录如下:
productID producttype productsort
___________________________________
1 caps 6
2 inject 2
3 caps 4
4 caps 2
5 inject 4
6 wheel chair 3
7 caps 5
8 wheel chair 2
9 inject 1
10 wheel chair 1
我想按producttype 分组后再 productsort 排序
如下语句怎么无法实现:
select * from product wher 1=1 group by producttype order by productnewsort desc
[解决办法]
select * from product group by producttype order by productsort desc
[解决办法]
SELECT SUM(productID),producttype SUM(productsort)
FROM Product
ORDER By producttype
Order by productsort
你的要求存在问题,既然要分组，肯定是想进委汇总或处理某项，如果使用SELECT * ，和未分组有什么两样？你的要求有点混乱...
[解决办法]
分组一般意味着统计,你是想productsort要合计起来的吧?
SELECT producttype, SUM(productsort) AS p
FROM product
GROUP BY producttype
ORDER BY p DESC
[解决办法]
group by fieldname order by fieldname
[解决办法]
你的要求不是分组，是按两个字段排序而已：
select * from product order by producttype,productsort DESC
[解决办法]
select * from product order by producttype asc,productsort desc
[解决办法]
同意
[解决办法]
--try
select max(productID) as productID, producttype, max(productsort) as productsort
from product
group by producttype
order by 3 desc
[解决办法]
create table product(productID int, producttype nvarchar(20), productsort int)
insert product select 1, 'caps ', 6
union all select 2, 'inject ', 2
union all select 3, 'caps ', 4
union all select 4, 'caps ', 2
union all select 5, 'inject ', 4
union all select 6, 'wheel chair ', 3
union all select 7, 'caps ', 5
union all select 8, 'wheel chair ', 2
union all select 9, 'inject ', 1
union all select 10, 'wheel chair ', 1
select * from product order by producttype, productsort desc
--result
productID producttype productsort
----------- -------------------- -----------
1 caps 6
7 caps 5
3 caps 4
4 caps 2
5 inject 4
2 inject 2
9 inject 1
6 wheel chair 3
8 wheel chair 2
10 wheel chair 1
(10 row(s) affected)
[解决办法]
是的， Order BY 后面跟2个字段
