min是求最小值
case
when...then...
when...then...
end
是代表多分支,即什么条件执行什么语句
case
when...then...
when...then...
end
是代表多分支,即什么条件执行什么语句
解决方案 »
- 使用commons-net 的 ftp上传文件速度怎么这么慢?
- Struts Ec标签 导出问题
- struts2的默认ACTION会执行execute()方法完成数据交互吗?还是只是个静态页面?
- 看下面程序,这个firstName和 lastName是怎么回事?
- The value for the useBean class attribute xnc.db.query.DBQuery is invalid.
- struts 怎么用 LookupDispatchAction?
- 初学JAVA,请前辈介绍些经典书籍,谢谢!
- 一道面试题。。感觉挺好玩的 有没有简单的解决方法
- 如何允许匿名访问网站?
- 在apache的网站上如何下载tomcat的源代码?分低,不好意思,但很好回答
- 请问,哪位兄弟有孙卫琴编写的精通Hibernate Java对象持久化技术详解的随书光碟呀。能不能传我一份呀。
- java能访问MS的MSMQ吗?在百度搜索一下,没发现相关资料。多谢指教!
原码如下:
public ResultSet getrs(int i){
try{
sql = "select Name from tb_Basic2 where Home2="+i;
rs = data.getrs(sql);
rs.last();
sum = rs.getRow();
rs.beforeFirst();
while (rs.next()) {
if (rs.isLast()) {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "'";
}
else {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "',";
}
}
rs.close();
sql = "select tb_Values.Logo," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and tb_Values.Type="+i+" group by tb_Values.Logo";
rs = data.getrs(sql);
str="";
return rs;
}
catch(Exception e){
e.printStackTrace();
}
return null;
}
还有,这样得出来的str是什么啊,代表的又是什么,一头雾水啊
它的类型要看tb_Basic2这张表中的设计
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "'";
}
else {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "',";
}意思就是在SQL语句中加入对name的判定,如果是这个name就取tb_Values.Price 只不过else中多了个‘,’连接符你可以把整个的SQL 写出来看看
如下:package cars;import java.sql.*;
/**
* <p>Title: 华奥汽车销售集团网站</p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2005</p>
* <p>Company: 明日科技有限公司</p>
* @author not attributable
* @version 1.0
*/public class CrossTable {
Bean1 data=new Bean1();
ResultSet rs;
ResultSet rs1;
int sum=0;
String sql;
String str="";
int homo1=0;
int homo=0;
int homo2=0;
String rla="";
int str1=0;
int str2=0;
public CrossTable() {
} public ResultSet getrs(int i){
try{
sql = "select Name from tb_Basic2 where Home2="+i;
rs = data.getrs(sql);
rs.last();
sum = rs.getRow();
rs.beforeFirst();
while (rs.next()) {
if (rs.isLast()) {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "'";
}
else {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "',";
}
}
rs.close();
sql = "select tb_Values.Logo," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and tb_Values.Type="+i+" group by tb_Values.Logo";
rs = data.getrs(sql);
str="";
return rs;
}
catch(Exception e){
e.printStackTrace();
}
return null;
} public ResultSet getrs(int i,int logo){
try{
sql = "select Name from tb_Basic2 where Home2="+i;
rs = data.getrs(sql);
rs.last();
sum = rs.getRow();
rs.beforeFirst();
while (rs.next()) {
if (rs.isLast()) {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "'";
}
else {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "',";
}
}
rs.close();
sql = "select tb_Values.Logo," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and tb_Values.Logo="+logo+" group by tb_Values.Logo";
rs = data.getrs(sql);
str="";
return rs;
}
catch(Exception e){
e.printStackTrace();
}
return null;
} public ResultSet getrs(int i,int logo,int type){
try{
sql = "select Name from tb_Basic2 where Home2="+i+" and Homo1="+type;
rs = data.getrs(sql);
rs.last();
sum = rs.getRow();
rs.beforeFirst();
while (rs.next()) {
if (rs.isLast()) {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "'";
}
else {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "',";
}
}
rs.close();
sql = "select tb_Values.Logo," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and tb_Values.Logo="+logo+" group by tb_Values.Logo";
rs = data.getrs(sql);
str="";
return rs;
}
catch(Exception e){
e.printStackTrace();
}
return null;
} public ResultSet getrs(String name1,String name2,int logo){
try{
sql="select * from tb_Basic2 where Name='品牌' and Home2="+logo;
rs=data.getrs(sql);
rs.beforeFirst();
if(rs.next()&&rs!=null){
homo1 = rs.getInt("ID");
}
sql="select * from tb_Basic2 where Name='型号' and Home2="+logo;
rs=data.getrs(sql);
rs.beforeFirst();
if(rs.next()&&rs!=null){
homo2 = rs.getInt("ID");
}
sql="select * from tb_Values where Homo1="+homo1+" and Price like '%"+name1+"%'";
rs=data.getrs(sql);
rs.last();
int row1=rs.getRow();
int[] ary1=new int[row1];
rs.beforeFirst();
while(rs.next()&&rs!=null){
ary1[str2]=rs.getInt("Logo");
str2=str2+1;
}
rs.close();
data.close();
Bean1 data1=new Bean1();
sql="select * from tb_Values where Homo1="+homo2+" and Price like '%"+name2+"%'";
rs=data1.getrs(sql);
rs.beforeFirst();
rla="";
while(rs.next()&&rs!=null){
for(str1=0;str1<str2;str1++){
if (ary1[str1] == rs.getInt("Logo")) {
rla = rla + " tb_Values.Logo=" + rs.getInt("Logo") + " or";
}
}
}
rla=rla.substring(0,rla.length()-3);
sql = "select Name from tb_Basic2 where Home2="+logo;
rs = data.getrs(sql);
rs.beforeFirst();
while (rs.next()) {
if (rs.isLast()) {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "'";
}
else {
str = str + "min(case Name when '" + rs.getString("Name") +
"' then tb_Values.Price end) AS '" + rs.getString("Name") + "',";
}
}
rs.close();
sql = "select tb_Values.Logo,tb_Values.Type," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and("+rla+") group by tb_Values.Logo,tb_Values.Type";
rs = data.getrs(sql);
str="";
rla="";
return rs;
}
catch (Exception e) {
e.printStackTrace();
}
return null;
}
public ResultSet getrs(String name,String price){
try{
sql="select * from tb_Basic2 where Name='"+name+"'";
rs=data.getrs(sql);
rs.beforeFirst();
if(rs.next()&&rs!=null){
homo1 = rs.getInt("ID");
}
if(rs.next()&&rs!=null){
homo2 = rs.getInt("ID");
}
sql="select * from tb_Values where (Homo1="+homo1+" or Homo1="+homo2+") and Price='"+price+"'";
rs=data.getrs(sql);
rs.beforeFirst();
rla="";
while(rs.next()&&rs!=null){
homo1=rs.getInt("Logo");
rla=rla+" tb_Values.Logo="+homo1+" or";
}
rla=rla.substring(0,rla.length()-3);
str="min(case Name when '品牌' then tb_Values.Price end) AS '品牌',";
str=str+"min(case Name when '型号' then tb_Values.Price end) AS '型号',";
str=str+"min(case Name when '售价' then tb_Values.Price end) AS '售价'";
sql = "select tb_Values.Logo,tb_Values.Type," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and("+rla+") group by tb_Values.Logo,tb_Values.Type";
rs = data.getrs(sql);
sql="";
str="";
rla="";
return rs;
}
catch (Exception e) {
e.printStackTrace();
}
return null;
} public ResultSet getrs(){
try{
str="min(case Name when '品牌' then tb_Values.Price end) AS '品牌',";
str=str+"min(case Name when '型号' then tb_Values.Price end) AS '型号',";
str=str+"min(case Name when '售价' then tb_Values.Price end) AS '售价'";
sql = "select tb_Values.Logo,tb_Values.Type," + str + " from tb_Basic2 inner join tb_Values on tb_Basic2.ID=tb_Values.Homo1 and (tb_Values.Type=1 or tb_Values.Type=3) group by tb_Values.Logo,tb_Values.Type";
rs = data.getrs(sql);
sql="";
str="";
return rs;
}
catch (Exception e) {
e.printStackTrace();
}
return null;
}
public void close(){
data.close();
try{
rs.close();
}
catch(Exception e){
}
} public String getsql(){
return sql;
}}