sql 行转列、分组、排序,

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/XiaoHanZuoFengZhou/article/details/78742762
select * from (
select 
concat(t2.supplier,'(',t1.from_addr,')') 供应商(矿点),
t3.goods_name 货品,
count(t1.id) 磅总单数,
sum(case when t1.status='1'then 1 else 0 end) 待处理,
sum(case when t1.status='2'then 1 else 0 end) 自动配对,
sum(case when t1.status='3'then 1 else 0 end) 人工配对,
sum(case when t1.status='7'then 1 else 0 end) 手工补单,
sum(case when t1.status not in ('1','2','3','7')then 1 else 0 end) 其他,
(sum(case when t1.status='2'then 1 else 0 end) + 
sum(case when t1.status='3'then 1 else 0 end))
/count(t1.id) 接单率
from 
poundbill.poundbill_${code} t1
inner join 
poundbill.manu_supplier_mine t2
on 
t1.supplier=t2.supplier
inner join 
poundbill.goods_loss_def t3
on 
t3.goods_category = t1.category
inner join 
poundbill.manu_code_def t4
on 
t3.pk_corp = t4.pk_corp

where 
t1.sum_date >= '${beginDate}'
and
t1.sum_date <= '${endDate}'
group by 
t4.name ,t2.supplier,t3.goods_name,t1.from_addr
) A 
order by 
A.接单率
展开阅读全文

请教一个分组排序SQL

06-08

rnDROP TABLE IF EXISTS test;rnCREATE TABLE test (rnrn `taochanid` varchar(100) DEFAULT NULL,rn `taochanvalue` varchar(2000) DEFAULT NULL, rn `num` int(11) DEFAULT NULLrnrn) ENGINE=InnoDB DEFAULT CHARSET=utf8;rnrn-- ----------------------------rnINSERT INTO test VALUES ('bbaa', '页数:10', '6');rnINSERT INTO test VALUES ('bbaa', '封面:玻璃面' ,'1');rnINSERT INTO test VALUES ('aeww', '封面:玻璃面', '1');rnINSERT INTO test VALUES ('aeww', '册芯:无缝', '0');rnINSERT INTO test VALUES ('aeww', '价格:150', '7');rnINSERT INTO test VALUES ('bbaa', '尺寸:8X5', '5');rnINSERT INTO test VALUES ('aeww', '淋膜:超光膜', '2');rnINSERT INTO test VALUES ('bbaa', '淋膜:超光膜', '2');rnINSERT INTO test VALUES ('bbaa', '册芯:无缝', '0');rnINSERT INTO test VALUES ('bbaa', '烫边:黑色', '4');rnINSERT INTO test VALUES ('bbaa', '顺序:有序', '3');rnINSERT INTO test VALUES ('aeww', '页数:10', '6');rnINSERT INTO test VALUES ('bbaa', '递增页价格:12', '8');rnINSERT INTO test VALUES ('aeww', '顺序:有序', '3');rnINSERT INTO test VALUES ('aeww', '烫边:黑色', '4');rnINSERT INTO test VALUES ('aeww', '尺寸:8X5', '5');rnINSERT INTO test VALUES ('bbaa', '价格:150', '7');rnINSERT INTO test VALUES ('aeww', '递增页价格:12', '8');rnrn现在查询的结果是这样:rn select a.taochanid,group_concat(a.taochanvalue) as taochanvalue from test a GROUP BY a.taochanidrn----------------------------------------------------------rntaochanid taochanvalue rnaeww 封面:玻璃面,册芯:无缝,价格:150,淋膜:超光膜,页数:10,顺序:有序,烫边:黑色,尺寸:8X5,递增页价格:12rnbbaa 页数:10,封面:玻璃面,尺寸:8X5,淋膜:超光膜,册芯:无缝,烫边:黑色,顺序:有序,递增页价格:12,价格:150rnrn我想要的结果是把taochanvalue根据num列排序,现在就不知道怎么排序才能得到下面的结果:rnrn[color=#FF0000]----------------------------------------------------------rntaochanid taochanvalue rnaeww 册芯:无缝封面:玻璃面淋膜:超光膜顺序:有序烫边:黑色尺寸:8X5页数:10价格:150递增页价格:12rnbbaa 册芯:无缝封面:玻璃面淋膜:超光膜顺序:有序烫边:黑色尺寸:8X5页数:10价格:150递增页价格:12[/color]rnrn也就是把taochanvalue这一列按num排序相加rnrnrn 论坛

没有更多推荐了,返回首页