首页 > 专题系列 > Java解POJ > POJ 3699 miniSQL [解题报告] Java
2013
11-13

POJ 3699 miniSQL [解题报告] Java

miniSQL

问题描述 :

Joan is taking an internship at a company these days. Every day she is assigned tons of dull statistical jobs such as counting how many products made by the company values more than $100. In order to have more time to chat with the handsome intern nearby, she asks you to program a clever software that helps her get released from the onerous work.

The software should take as input a table of records together with several queries and for each query output all the records respectively.

输入:

The first part of the input is a single line consisting of three integers, m(1 ≤ m ≤ 10), n(1 ≤ n ≤ 10000), k(1 ≤ k ≤ 100) indicating the number of columns in the table, the number of records and the number of queries.

The second part of the input is the description of the table, which consists of m lines. The i-th line of this part consists two strings in the format Column_Name Type which describes the i-th column of the table.

  • Column_Name contains only letters(a-zA-Z), digits(0-9) and underline(_).
  • Type is either "STR" or "INT" indicating the type of this column.

The third part of the input shows the content of the table which contains n lines. The i-th line of this part shows the i-th record in the table which consists of m items.

  • if the type of the i-th column is "INT", the i-th item will be a 32-bit integer.
  • if the type of the i-th column is "STR" , the i-th item will be a string containing only letters(a-zA-Z), digits(0-9) and underline(_).

The last part of the input gives the k queries which are similar to SQL select statements. The query is in the format select Column_List where Condition

  • Column_List contains a list of column names which are separated by a single comma. All the names occured in the description of the table.
  • Condition is in the format Column_Name Op Value
    • Column_Name is one of the column name in the description of the table.
    • if the column’s type is "STR", Op is "=". Otherwise it may be "=", "<" or ">".
    • if the column’s type is "STR", Value will be a quoted string containing only letters(a-zA-Z), digits(0-9) and underline(_). Otherwise it will be a 32-bit integer.

The size of the input will not exceed 1M bytes.

输出:

For each query output a table and a blank line as Sample Output shows.

样例输入:

3 10 4
SID INT
NAME STR
PRIZE INT
1000 Potion 50
1001 Phoenix_Down 150
1002 Remedy 300
1003 Dagger 320
1004 Rod 260
1005 Flame_Staff 1100
1006 Broad_Sword 330
1007 Fork 1100
1008 Javelin 880
1009 Cats_Claws 4000
select SID,NAME,PRIZE where PRIZE>100
select SID,SID where NAME="Dagger"
select PRIZE where NAME="Rod"
select SID,NAME,PRIZE where NAME="Excalibur"

样例输出:


+-----------------------------+
| SID  |     NAME     | PRIZE |
|------|--------------|-------|
| 1001 | Phoenix_Down |  150  |
| 1002 |    Remedy    |  300  |
| 1003 |    Dagger    |  320  |
| 1004 |     Rod      |  260  |
| 1005 | Flame_Staff  | 1100  |
| 1006 | Broad_Sword  |  330  |
| 1007 |     Fork     | 1100  |
| 1008 |   Javelin    |  880  |
| 1009 |  Cats_Claws  | 4000  |
+-----------------------------+

+-------------+
| SID  | SID  |
|------|------|
| 1003 | 1003 |
+-------------+

+-------+
| PRIZE |
|-------|
|  260  |
+-------+

+--------------------+
| SID | NAME | PRIZE |
|-----|------|-------|
+--------------------+

解题代码:

/* @author:SmilingWang */
import java.util.*;

public class Main {
 static StringBuffer res = new StringBuffer();
 public static void main(String[] args){
  Scanner in = new Scanner(System.in);
  int m = in.nextInt();
  int n = in.nextInt();
  int k = in.nextInt();
	
  Map< String, String> symbol_table = new HashMap< String, String>();
  Map< String, Integer> symbol_pos_table = new HashMap< String,Integer>();
  for(int i = 0; i < m; i++){
   String name = in.next();
   String type = in.next();
	
   symbol_pos_table.put(name, i);
   symbol_table.put(name, type);
  }
		
  String[][] table = new String[n][m];
  for(int i = 0; i < n; i++){
    for(int j = 0; j < m; j++){
	table[i][j] = in.next();
    }
	
  }		
		
  for(int i = 0; i < k; i++){
   in.next();
   String cols = in.next();
   in.next();
   String op = in.next();			
   StringTokenizer st = new StringTokenizer(cols, ",");
			
   ArrayList[] result = new ArrayList[st.countTokens()];
   int len = 0;
   String result_index_table[] = new String[st.countTokens()];
		
   while(st.hasMoreTokens()){
    result[len] = new ArrayList< String>();
    String col = st.nextToken();
    result_index_table[len] = col;
				
    int index = symbol_pos_table.get(col);
    int sp = -1;
    String op1="", op2="";
    char opt = 0;
    if((sp=op.indexOf(">")) > 0){
	op1 = op.substring(0, sp);
	op2 = op.substring(sp+1, op.length());
	opt = '>';
    }
    else if((sp=op.indexOf("=")) > 0){
	op1 = op.substring(0, sp);
	op2 = op.substring(sp+1, op.length());
	opt = '=';
    }
    else if((sp=op.indexOf("< ")) > 0){
	op1 = op.substring(0, sp);
	op2 = op.substring(sp+1, op.length());
	opt = '< ';
    }
				
    for(int r = 0; r < table.length; r++){
	String op_type = symbol_table.get(op1);
	int op_index = symbol_pos_table.get(op1);
		
	if(op_type.equals("INT")){
	  if(opt=='>'){
	   if(Integer.parseInt(table[r][op_index]) > Integer.parseInt(op2)){
		result[len].add(table[r][index]);
	   }
	 }
      else if(opt == '='){
	if(Integer.parseInt(table[r][op_index]) == Integer.parseInt(op2)){
	  result[len].add(table[r][index]);
	}
      }
     else if(opt == '< '){
	if(Integer.parseInt(table[r][op_index]) < Integer.parseInt(op2)){
	  result[len].add(table[r][index]);
	}
     }
   }
   else if(op_type.equals("STR")){
	if(table[r][op_index].equals(op2.replaceAll("\"", ""))){
        result[len].add(table[r][index]);
	}
   }
  }
  len++;
  }
				
  int table_len[] = new int[result_index_table.length];
  for(int x = 0; x < table_len.length; x++){
   int max = result_index_table[x].length();
   for(int y = 0; y < result[x].size(); y++){
	String item = result[x].get(y).toString();
	if(item.length() > max){
         max = item.length();
	}
   }
   table_len[x] = max+2;
   }

   int total_len = 0;
   for(int x = 0; x < table_len.length; x++){
	total_len += table_len[x];
   }

   total_len += (table_len.length + 1);	
   for(int x = 1; x <= total_len; x++){
     if(x == 1 || x == total_len){
	//System.out.print("+");
	res.append("+");
     }
     else{
	//System.out.print("-");
	res.append("-");
     }
   }
   res.append("\n");
			
  for(int x = 0; x < result_index_table.length; x++){				
	res.append("|");
	printCenter(result_index_table[x], table_len[x]);
	if(x == result_index_table.length-1){
         res.append("|");
	}
   }
   res.append("\n");		
			
  for(int x = 0; x < table_len.length; x++){
    res.append("|");
    for(int y = 0; y < table_len[x]; y++){
	//System.out.print("-");
	res.append("-");
    }
    if(x == result_index_table.length-1){
	//System.out.print("|");
	res.append("|");
    }
  }

  res.append("\n");
  for(int y = 0; y < result[0].size(); y++){
   for(int x = 0; x < result.length; x++){	
	//System.out.print("|");
	res.append("|");
	printCenter(result[x].get(y).toString(), table_len[x]);
	if(x == result.length-1){
         //System.out.print("|");
	  res.append("|");
	}
    }
     //System.out.println();
     res.append("\n");
   }
					
  for(int x = 1; x <= total_len; x++){
    if(x == 1 || x == total_len){
	//System.out.print("+");
	res.append("+");
    }
    else{
	res.append("-");
	//System.out.print("-");
    }
   }
   res.append("\n");
   res.append("\n");
  }	

  System.out.println(res);
  }
	
  public static void printCenter(String data, int space){
   int len = data.length();
   int left = (space - len)/2;
   int total_len = 0;
		
   for(int i = 0; i < left; i++){
	//System.out.print(" ");
	res.append(" ");
   }
  total_len += left;
		
  res.append(data);
  //System.out.print(data);
  total_len += data.length();
		
  for(int i = total_len; i < space; i++){
    //System.out.print(" ");
    res.append(" ");
   }
 }
}