create table a (
id int(4),
para1 float,
para2 float,
.
.
.
para57 float
)
create table b(
id int(4),
paraName varchar(45),//这个字段用来存储表a中的所有数据类型为float的字段的名称。
up float,
bottom float
)现在我要实现:select up,bottom from b where paraName=para1 and paraName=para2... and paraName=para57;请问,有什么简单的方式实现同样的效果么?
id int(4),
para1 float,
para2 float,
.
.
.
para57 float
)
create table b(
id int(4),
paraName varchar(45),//这个字段用来存储表a中的所有数据类型为float的字段的名称。
up float,
bottom float
)现在我要实现:select up,bottom from b where paraName=para1 and paraName=para2... and paraName=para57;请问,有什么简单的方式实现同样的效果么?
id int(4),
para1 float,
para2 float,
para3 float
)
create table b(
id int(4) AUTO_INCREMENT,
paraName varchar(45),//这个字段用来存储表a中的所有数据类型为float的字段的名称。
up float,
bottom float
)
insert into b (paraName ,up ,bottom ) values ( para1,0.55,0.30 );
insert into b (paraName ,up ,bottom ) values ( para2,0.95,0.80 );
insert into b (paraName ,up ,bottom ) values ( para3,2.10,0.33 );现在我要把参数para1,para2,para3中对应的up和bottom都找出来。
Empty set (0.00 sec)mysql> select * from b;
+----+----------+------+--------+
| id | paraName | up | bottom |
+----+----------+------+--------+
| 1 | para1 | 0.55 | 0.3 |
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
+----+----------+------+--------+
3 rows in set (0.00 sec)mysql>
基于以上数据,你期望的结果是什么样?
另外 insert into b (paraName ,up ,bottom ) values ( para1,0.55,0.30 ); 这个语法本身不对,估计你是想加 'para1' ,这也是我自己猜的,如果不是请及时指出!
//获取给定表的所有字段名称,中间以","分割,用于查询。
public String getTableField(String tableName) {
Connection con=UtilClass.getCon();
PreparedStatement pstat=null;
ResultSet rs=null;
ResultMeataData rsmd=null;
StringBuffer tableFields=null; try{
pstat=con.prepareStatement("select * from ?");
pstat.setString(1,tableName);
rs=pstat.executeQuery();
rsmd=rs.getMetaData();
for(int i=1;i<54;i++) {
tableFields.append(rsmd.getColumnName(i)).append(",");
}
}catch(Exception e) {
e.printStackTrace();
} finally{ UtilClass.closeRs(rs);
UtilClass.closePstat(pstat);
UtilClass.closeCon(con);
}return tableFields.toString();
}//根据给定的表名,获取对应表中所有字段的上限,下限
public ArrayList getDataRange(String tableName) {
Connection con=UtilClass.getCon();
PreparedStatement pstat=null;
ResultSet rs=null; try{
pstat=con.prepareStatement("select * from b where para in ( ? )");
pstat.setString(1,getTableField(tablename));
rs=pstat.executeQuery();
ArrayList list=new ArrayList();
while(rs.next()) {
DataRange dr=new DataRange();
dr.setChDataName(rs.getString("paraName"));
dr.setUpper(rs.getFloat("up"));
dr.setBottom(rs.getFloat("bottom"));
lsit.add(dr);
}
}catch(Exception e) {
e.printStackTrace();
} finally{ UtilClass.closeRs(rs);
UtilClass.closePstat(pstat);
UtilClass.closeCon(con);
}return list;
}
to ACMAIN_CHM:以上是我使用此次查询的目的,当然,最终的作用还是返回到页面,使用javascript代码来验证表单。所以,我想问的是public ArrayList getDataRange(String tableName)这个函数,在数据中能够实现么,不适用java编程语言。
id int(4),
para1 float,
para3 float
)
mysql> select * from b;
+----+----------+------+--------+
| id | paraName | up | bottom |
+----+----------+------+--------+
| 1 | para1 | 0.55 | 0.3 |
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
+----+----------+------+--------+
然后,你输入 tblname = 'A'
则期望结果为| 1 | para1 | 0.55 | 0.3 |
| 3 | para3 | 2.1 | 0.33 |你是不是想说这个意思?
你的mysql 是什么版本?mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql>
id int(4),
para2 float,
para3 float
) mysql> select * from b;
+----+----------+------+--------+
| id | paraName | up | bottom |
+----+----------+------+--------+
| 1 | para1 | 0.55 | 0.3 |
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
+----+----------+------+--------+则当输入为 tblname = 'C' 则期望结果为
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
from information_schema.`COLUMNS` x ,b
where x.TABLE_SCHEMA='test' and x.TABLE_NAME='c'
and x.COLUMN_NAME=b.paraName红字部分换成你实际的数据库名和表名。
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| para1 | float | YES | | NULL | |
| para2 | float | YES | | NULL | |
| para3 | float | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> desc c;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| para2 | float | YES | | NULL | |
| para3 | float | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> select * from b;
+----+----------+------+--------+
| id | paraName | up | bottom |
+----+----------+------+--------+
| 1 | para1 | 0.55 | 0.3 |
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
+----+----------+------+--------+
3 rows in set (0.00 sec)mysql> select b.*
-> from information_schema.`COLUMNS` x ,b
-> where x.TABLE_SCHEMA='test' and x.TABLE_NAME='a'
-> and x.COLUMN_NAME=b.paraName;
+----+----------+------+--------+
| id | paraName | up | bottom |
+----+----------+------+--------+
| 1 | para1 | 0.55 | 0.3 |
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
+----+----------+------+--------+
3 rows in set (0.00 sec)mysql> select b.*
-> from information_schema.`COLUMNS` x ,b
-> where x.TABLE_SCHEMA='test' and x.TABLE_NAME='c'
-> and x.COLUMN_NAME=b.paraName;
+----+----------+------+--------+
| id | paraName | up | bottom |
+----+----------+------+--------+
| 2 | para2 | 0.95 | 0.8 |
| 3 | para3 | 2.1 | 0.33 |
+----+----------+------+--------+
2 rows in set (0.00 sec)mysql>
-> from b
-> a.COLUMN_NAME=b.paraName;
id int(4) AUTO_INCREMENT,
para1 float,
para2 float,
para3 float,
fpara1 tinyint,
fpara2 tinyint,
fpara3 tinyint
)
create table b(
id int(4) AUTO_INCREMENT,
paraName varchar(45),//这个字段用来存储表a中的所有数据类型为float的字段的名称。
up float,
bottom float
)
insert into b (paraName ,up ,bottom ) values ( para1,0.55,0.30 );
insert into b (paraName ,up ,bottom ) values ( para2,0.95,0.80 );
insert into b (paraName ,up ,bottom ) values ( para3,2.10,0.33 );
insert insert into a (para1 ,para2 ,para3 ) values (0.4,0.6,0.4 ); 这个问题还是前天讨论的对表中的数据做标记的问题。我如何让a表中的这条记录最后的结果是:0.4,0.6,0.4,0,1,0就是说,我在往a表中插入数据的同时,让这些float型的数据和表b中的数据进行对比,如果bottom<para<up,则更新本条记录后面的标识为0,否则标示为1。