下面是我的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 = 'res' 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_01 C070100030 1 k2360 ...
KEE0201 NC_01 C070100030 2 k2360 ...
KEE0201 NC_01 C070100030 3 k2360 ...
KEE0201 NC_01 C070100030 4 k2360 ...
KEE0201 NC_01 C070100030 5 k2360 ...
KEE0201 NC_01 C070100030 6 k2360 ... KEE0201 NC_01 C070100030 7 k2299 ...
KEE0201 NC_01 C070100030 8 k2299 ...
KEE0201 NC_01 C070100030 9 k2299 ...
KEE0201 NC_01 C070100030 10 k2299 ...
KEE0201 NC_01 C070100030 11 k2299 ...
KEE0201 NC_01 C070100030 12 k2299 ... KEE0201 NC_01 C070100030 13 k2360 ...
KEE0201 NC_01 C070100030 14 k2360 ...
KEE0201 NC_01 C070100030 15 k2360 ...
KEE0201 NC_01 C070100030 16 k2360 ...
KEE0201 NC_01 C070100030 17 k2360 ...
KEE0201 NC_01 C070100030 18 k2360 ...
KEE0201 NC_01 C070100030 19 k2299 ...
KEE0201 NC_01 C070100030 20 k2299 ...
KEE0201 NC_01 C070100030 21 k2299 ...
KEE0201 NC_01 C070100030 22 k2299 ...
KEE0201 NC_01 C070100030 23 k2299 ...
KEE0201 NC_01 C070100030 24 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 = 'res' 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_01 C070100030 1 k2360 ...
KEE0201 NC_01 C070100030 2 k2360 ...
KEE0201 NC_01 C070100030 3 k2360 ...
KEE0201 NC_01 C070100030 4 k2360 ...
KEE0201 NC_01 C070100030 5 k2360 ...
KEE0201 NC_01 C070100030 6 k2360 ... KEE0201 NC_01 C070100030 7 k2299 ...
KEE0201 NC_01 C070100030 8 k2299 ...
KEE0201 NC_01 C070100030 9 k2299 ...
KEE0201 NC_01 C070100030 10 k2299 ...
KEE0201 NC_01 C070100030 11 k2299 ...
KEE0201 NC_01 C070100030 12 k2299 ... KEE0201 NC_01 C070100030 13 k2360 ...
KEE0201 NC_01 C070100030 14 k2360 ...
KEE0201 NC_01 C070100030 15 k2360 ...
KEE0201 NC_01 C070100030 16 k2360 ...
KEE0201 NC_01 C070100030 17 k2360 ...
KEE0201 NC_01 C070100030 18 k2360 ...
KEE0201 NC_01 C070100030 19 k2299 ...
KEE0201 NC_01 C070100030 20 k2299 ...
KEE0201 NC_01 C070100030 21 k2299 ...
KEE0201 NC_01 C070100030 22 k2299 ...
KEE0201 NC_01 C070100030 23 k2299 ...
KEE0201 NC_01 C070100030 24 k2299 ...
我想将color相同的放一起,意思是如上将k2360的放一起,将k2299的放一起,在co_line中要从小到大排,请问我的sql语句要如何修改?
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 = 'res' 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
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 = 'res' 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
select * from test a
order by (select min(col2) from test where col3=a.col3) asc