提问者:小点点

是否需要在表中添加一个新列,该列的值是在其他列中获得相同值的用户名?


我需要你对这个问题的建议。考虑这个例子:输入:

    EmpID | FirstName | LastName | Groupmbr
---------------------------------------------------------
    201   | Ravi      | kona     | PayrollAccess
    201   | Ravi      | kona     | AcctsPayableAccess
    202   | Tinku     | sa       | AcctsReceivableAccess2
    202   | Tinku     | sa       | AcctsPayableAccess 
    203   | Nani      | ni       | AcctsPayable
    204   | Vamsi     | vi       | PayrollAccess 
    204   | Vamsi     | vi       | AcctsPayableAccess

例如,在第一个&; 表的第二行,则不重复Groupmbr值。 但对于Tinku(第三行),重复Groupmbr值。 因此,在新添加的列访问名称中,添加Ravi,tinku。 输出:

    EmpID | FirstName | LastName | Groupmbr              | AccessNames
------------------------------------------------------------------------
    201   | Ravi      | kona     | PayrollAccess         | Ravi
    201   | Ravi      | kona     | AcctsPayableAccess    | Ravi
    202   | Tinku     | sa       | AcctsReceivableAccess2| Ravi
    202   | Tinku     | sa       | AcctsPayableAccess    | Ravi,Tinku
    203   | Nani      | ni       | AcctsPayable          | Nani
    204   | Vamsi     | vi       | PayrollAccess         | Ravi,Vamsi
    204   | Vamsi     | vi       | AcctsPayableAccess    | Ravi,Tinku,Vamsi

这是我的密码。 获得输出所需的一些修改。

try {
            Driver ref=new Driver(); 
            DriverManager.registerDriver(ref);
            
            // connecting to database
            String dburl="jdbc:mysql://localhost:3306/test?user=root&password=toor@";
            connection=DriverManager.getConnection(dburl);
            ArrayList<String> grpmbr = new ArrayList<String>();
            String[] str = null;
            // locating csv file
            File file = new File("C:\\Users\\nas\\Desktop\\Details.csv");
            FileReader reader = new FileReader(file);
            BufferedReader bufferedReader = new BufferedReader(reader);
             
            // query for inserting
            String query = ("INSERT INTO information (EmpID,FirstName,LastName,Groupmbr,AccessNames) VALUES (?,?,?,?,?)");
            
            String query1 = ("SELECT Accessnames from information");
            
            // query for update when Groupmbr already exits in DB
            String query2 = ("UPDATE information SET AccessNames=? where FirstName=?");
            
            // prepared statement for insert and update
            PreparedStatement preparedStatement=connection.prepareStatement(query);
            PreparedStatement preparedStatement1=connection.prepareStatement(query1);
            PreparedStatement preparedStatement2=connection.prepareStatement(query2);
            
            
            // reading column names
            String line = bufferedReader.readLine();
                          
            // getting line by line into "line" variable 
            while ((line = bufferedReader.readLine()) != null) {
                str = line.split(",");
                EmpID =str[0];
                FirstName =str[1];
                LastName =str[2];
                Groupmbr = str[3];
                //AccessNames = str[4]; 
                
                preparedStatement.setInt(1, Integer.parseInt(EmpID));
                preparedStatement.setString(2, FirstName);
                preparedStatement.setString(3, LastName);
                preparedStatement.setString(4, Groupmbr);
                preparedStatement.setString(5, AccessNames);
                 
                try {
                    preparedStatement.executeUpdate();
                    preparedStatement1.executeUpdate();
                    preparedStatement2.executeUpdate();
                }
                catch(Exception e) {
                    preparedStatement2.setString(1, FirstName);
                    preparedStatement2.setString(2, Groupmbr);
                    preparedStatement2.executeUpdate();
                }
                 
                
                if(grpmbr.isEmpty() || !grpmbr.contains(str[3])) {
                    AccessNames = str[1];
                    grpmbr.add(str[3]);
                    System.out.println("grpmbr doesn't contain str[3]");
                }
                else if(grpmbr.contains(str[3])){
                    
                    AccessNames += ","+str[1];
                    grpmbr.add(str[3]);
                    System.out.println("contains");
                }
                else{
                    System.out.println("Not done");
                }
            }
            System.out.println("Successfully stored data into Database and able to fetch it now.");
            bufferedReader.close();
            connection.close();
            preparedStatement.close();
        }
        catch (SQLException e) { 
            e.printStackTrace();
        }

共1个答案

匿名用户

UPDATE test
JOIN ( SELECT t1.EmpId, t1.Groupmbr, GROUP_CONCAT(t2.FirstName) AccessNames
       FROM test t1
       JOIN test t2 ON t1.EmpId >= t2.EmpId AND t1.Groupmbr = t2.Groupmbr
       GROUP BY t1.EmpId, t1.Groupmbr ) data_for_update USING (EmpId, Groupmbr)
SET test.AccessNames = data_for_update.AccessNames;

小提琴