请帮我修改下sql语句的排序?
下面是我的SQL语句,
SELECT Ci.co_cust_num, Jr.wc, KDc.Co_Num, KDc.Co_line, Caa.attr_value AS Color,
Ci.due_date, Jr.job, Jr.suffix, J.item, J.description, J.qty_released, J.qty_complete,
Js.start_date, Js.end_date, Jm.item AS JmItem, Jm.description AS JmDes,
Jm.scrap_fact, J1.config_id, Caa1.attr_value
FROM dbo.cfg_attr_all Caa1 INNER JOIN
dbo.coitem Ci INNER JOIN
dbo.KDcoitem KDc ON Ci.co_num = KDc.Co_Num AND Ci.co_line = KDc.Co_line ON
Caa1.config_id = KDc.config_id AND Caa1.attr_name = 'remarks ' INNER JOIN
dbo.job_sch Js INNER JOIN
dbo.job J INNER JOIN
dbo.jobroute Jr ON J.job = Jr.job AND J.suffix = Jr.suffix AND J.type = 'J ' INNER JOIN
dbo.jobmatl Jm ON Jr.job = Jm.job AND Jr.suffix = Jm.suffix INNER JOIN
dbo.job J1 ON J.est_job = J1.job AND J.est_suf = J1.suffix ON Js.job = Jr.job AND
Js.suffix = Jr.suffix INNER JOIN
dbo.cfg_attr_all Caa ON J1.config_id = Caa.config_id AND Caa.attr_name = 'color ' AND
ISNULL(Caa.attr_value, 'T ') <> 'T ' ON KDc.Old_Co_Num = J1.ord_num AND
KDc.Old_Co_Line = J1.ord_line
WHERE (Jr.wc = N 'NC_01 ') AND (J.type = 'J ')and Kdc.co_num= 'C070100030 '
order by KDc.co_num,Kdc.co_line
下面是运行后的结果:
co_cust_num wc Co_Num Co_line Color ...
KEE0201 NC_01C0701000301 k2360 ...
KEE0201 NC_01C0701000302 k2360 ...
KEE0201 NC_01C0701000303 k2360 ...
KEE0201 NC_01C0701000304 k2360 ...
KEE0201 NC_01C0701000305 k2360 ...
KEE0201 NC_01C0701000306 k2360 ...
KEE0201 NC_01C0701000307 k2299 ...
KEE0201 NC_01C0701000308 k2299 ...
KEE0201 NC_01C0701000309 k2299 ...
KEE0201 NC_01C07010003010 k2299 ...
KEE0201 NC_01C07010003011 k2299 ...
KEE0201 NC_01C07010003012 k2299 ...
KEE0201 NC_01C07010003013 k2360 ...
KEE0201 NC_01C07010003014 k2360 ...
KEE0201 NC_01C07010003015 k2360 ...
KEE0201 NC_01C07010003016 k2360 ...
KEE0201 NC_01C07010003017 k2360 ...
KEE0201 NC_01C07010003018 k2360 ...
KEE0201 NC_01C07010003019 k2299 ...
KEE0201 NC_01C07010003020 k2299 ...
KEE0201 NC_01C07010003021 k2299 ...
KEE0201 NC_01C07010003022 k2299 ...
KEE0201 NC_01C07010003023 k2299 ...
KEE0201 NC_01C07010003024 k2299 ...
我想将color相同的放一起,意思是如上将k2360的放一起,将k2299的放一起,在co_line中要从小到大排,请问我的sql语句要如何修改?
[解决办法]
SELECT Ci.co_cust_num, Jr.wc, KDc.Co_Num, KDc.Co_line, Caa.attr_value AS Color,
Ci.due_date, Jr.job, Jr.suffix, J.item, J.description, J.qty_released, J.qty_complete,
Js.start_date, Js.end_date, Jm.item AS JmItem, Jm.description AS JmDes,
Jm.scrap_fact, J1.config_id, Caa1.attr_value
FROM dbo.cfg_attr_all Caa1 INNER JOIN
dbo.coitem Ci INNER JOIN
dbo.KDcoitem KDc ON Ci.co_num = KDc.Co_Num AND Ci.co_line = KDc.Co_line ON
Caa1.config_id = KDc.config_id AND Caa1.attr_name = 'remarks ' INNER JOIN
dbo.job_sch Js INNER JOIN
dbo.job J INNER JOIN
dbo.jobroute Jr ON J.job = Jr.job AND J.suffix = Jr.suffix AND J.type = 'J ' INNER JOIN
dbo.jobmatl Jm ON Jr.job = Jm.job AND Jr.suffix = Jm.suffix INNER JOIN
dbo.job J1 ON J.est_job = J1.job AND J.est_suf = J1.suffix ON Js.job = Jr.job AND
Js.suffix = Jr.suffix INNER JOIN
dbo.cfg_attr_all Caa ON J1.config_id = Caa.config_id AND Caa.attr_name = 'color ' AND
ISNULL(Caa.attr_value, 'T ') <> 'T ' ON KDc.Old_Co_Num = J1.ord_num AND
KDc.Old_Co_Line = J1.ord_line
WHERE (Jr.wc = N 'NC_01 ') AND (J.type = 'J ')and Kdc.co_num= 'C070100030 '
order by KDc.co_num,Caa.attr_value,Kdc.co_line
[解决办法]
SELECT Ci.co_cust_num, Jr.wc, KDc.Co_Num, KDc.Co_line, Caa.attr_value AS Color,
Ci.due_date, Jr.job, Jr.suffix, J.item, J.description, J.qty_released, J.qty_complete,
Js.start_date, Js.end_date, Jm.item AS JmItem, Jm.description AS JmDes,
Jm.scrap_fact, J1.config_id, Caa1.attr_value
FROM dbo.cfg_attr_all Caa1 INNER JOIN
dbo.coitem Ci INNER JOIN
dbo.KDcoitem KDc ON Ci.co_num = KDc.Co_Num AND Ci.co_line = KDc.Co_line ON
Caa1.config_id = KDc.config_id AND Caa1.attr_name = 'remarks ' INNER JOIN
dbo.job_sch Js INNER JOIN
dbo.job J INNER JOIN
dbo.jobroute Jr ON J.job = Jr.job AND J.suffix = Jr.suffix AND J.type = 'J ' INNER JOIN
dbo.jobmatl Jm ON Jr.job = Jm.job AND Jr.suffix = Jm.suffix INNER JOIN
dbo.job J1 ON J.est_job = J1.job AND J.est_suf = J1.suffix ON Js.job = Jr.job AND
Js.suffix = Jr.suffix INNER JOIN
dbo.cfg_attr_all Caa ON J1.config_id = Caa.config_id AND Caa.attr_name = 'color ' AND
ISNULL(Caa.attr_value, 'T ') <> 'T ' ON KDc.Old_Co_Num = J1.ord_num AND
KDc.Old_Co_Line = J1.ord_line
WHERE (Jr.wc = N 'NC_01 ') AND (J.type = 'J ')and Kdc.co_num= 'C070100030 '
order by Caa.attr_value,Kdc.co_line desc
[解决办法]
楼主把以上语句生成视图 test就行了,调用视图
select * from test a
order by (select min(col2) from test where col3=a.col3) asc
