问个问题
ID NAME SCOPE
1 AAA 1
2 AAA 8
3 AAA 6
4 bbb 2
5 bbb 1如果这张表我只想把第2行和第4行取出来,怎么写SQL语句啊?也就是取出相同名字的scope的最大值行
希望是取出整行来,而不是这样
select max(scope) from table t group by name;
最好是select * from...怎么样的
谢谢!
ID NAME SCOPE
1 AAA 1
2 AAA 8
3 AAA 6
4 bbb 2
5 bbb 1如果这张表我只想把第2行和第4行取出来,怎么写SQL语句啊?也就是取出相同名字的scope的最大值行
希望是取出整行来,而不是这样
select max(scope) from table t group by name;
最好是select * from...怎么样的
谢谢!
FROM (NAME,SCOPE) IN (SELECT NAME,MAX(SCOPE) FROM TAB GROUP BY NAME )
select * from tab a
where not exists(select 1 from tab b where a.name=b.name and a.scope<b.scope)
FROM TAB
WHERE (NAME, SCOPE) IN SELECT NAME, MAX(SCOPE) FROM TAB GROUP BY NAME
from (select ID,NAME,SCOPE,row_number() over(partition by NAME order by SCOPE desc)rn
from tb) t
where rn=1select * from tb a where exists(select 1 from tb where a.name=name and a.SCOPE>SCOPE)
with test as (
select 1 id, 'AAA' as name, 1 scope from dual union all
select 2 id, 'AAA' as name, 8 scope from dual union all
select 3 id, 'AAA' as name, 6 scope from dual union all
select 4 id, 'BBB' as name, 2 scope from dual union all
select 5 id, 'BBB' as name, 1 scope from dual
)
select id, name, scope from
(
select a.*, rank() over(partition by name order by scope desc) lev from test a
)
where lev = 1
或者自己做个测试表test类同。
package com.testing.Service;import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* description: Used to generate a excel file, which store the latest proxy ID map.
* @author Shawn Xiao
* Jun 29, 2010 15:48:47 PM
*/
public class GenProxyIDMapExtractor {
public void writeProxyIDMapExcel() throws RowsExceededException,
WriteException, SQLException, IOException { String fileName = "c:/ProxyIDMap.csv"; //create the workbook and sheet
WritableWorkbook wwb = Workbook.createWorkbook(new File(fileName));
WritableSheet ws = wwb.createSheet(("Sheet" + "1"), 0); // set the font
WritableFont wf = new WritableFont(WritableFont.TIMES, 11, WritableFont.BOLD, false);
WritableCellFormat wcfF = new WritableCellFormat(wf); wcfF.setAlignment(jxl.format.Alignment.CENTRE);
wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // generate the Header
generateHeader(ws, wcfF); List list = new GenProxyIDMapQuery().getProxyIDMap_temp();
wcfF = new WritableCellFormat();
int index = 1;
for (int i = 0; i < list.size(); i++) {
ProxyIDmapData test = (ProxyIDmapData)list.get(i);
ws.setColumnView(0, 20);
ws.setColumnView(1, 20);
ws.setColumnView(2, 20);
ws.setColumnView(3, 20); Label data1 = new Label(0, index, ("" + test.getAs_of_date()), wcfF);
Label data2 = new Label(1, index, ("" + test.getSec_id()), wcfF);
Label data3 = new Label(2, index, ("" + test.getProxy_id()), wcfF);
Label data4 = new Label(3, index, ("" + test.getData_source()), wcfF);
ws.addCell(data1);
ws.addCell(data2);
ws.addCell(data3);
ws.addCell(data4);
index++;
}
wwb.write();
wwb.close();
}
/**
* generate the Header for the file
*/
private void generateHeader(WritableSheet ws, WritableCellFormat wcfF) throws WriteException, RowsExceededException {
Label labelCF1 = new Label(0, 0, "as_of_date", wcfF);
Label labelCF2 = new Label(1, 0, "sec_id", wcfF);
Label labelCF3 = new Label(2, 0, "proxy_id", wcfF);
Label labelCF4 = new Label(3, 0, "data_source", wcfF); // bond the values
ws.addCell(labelCF1);
ws.addCell(labelCF2);
ws.addCell(labelCF3);
ws.addCell(labelCF4);
}
/**
* Main class used to testing.
*/
public static void main(String[] args) throws RowsExceededException, WriteException, SQLException, IOException {
new GenProxyIDMapExtractor().writeProxyIDMapExcel();
}
}刚好昨天写了个简单的。
select ID,NAME,SCOPE
FROM (NAME,SCOPE) IN (SELECT NAME,MAX(SCOPE) FROM TAB GROUP BY NAME )
最好了,就用这条了,不过,也比较好理解