How to use two query results to continue the query?

aaapeter

oOracleConn.Open ();
                string select;
                select = "select ymd, sum (stoptim) as sum, devid"
                        + "from dev_bandata"
                        + "group by ymd, devid";
                
                OracleDataAdapter da = new OracleDataAdapter (select, oOracleConn);

                DataSet ds = new DataSet ();
                System.Data.DataTable sum_table = new System.Data.DataTable (); // Add a table sum_table
                da.Fill (ds, "sum_table");

                select = "select devid, devname from dev_bandata";
                da = new OracleDataAdapter (select, oOracleConn);
                System.Data.DataTable name_table = new System.Data.DataTable ();
                da.Fill (ds, "name_table");

                select = "select ymd, sum, devname from sum_table, name_table where sum_table.devid = name_table.devid";
                da = ????? // What should I write here?
                System.Data.DataTable result_table = new System.Data.DataTable ();
                da.Fill (ds, "result_table");

First ask me if this idea is feasible?
I checked the related issues, it seems that it can be done with a sql statement, but I don't think the readability is high.
I am newbie, please help more.

kalian2000

TO: select = "select ymd, sum, devname from sum_table, name_table where sum_table.devid = name_table.devid";

What is the purpose of the landlord?

Want to perform a joint query on the two tables in the DataSet?

lb1514

Not tested, just for reference!
select a.devname, b.ymd, b.sum from dev_bandata a, (select ymd, sum (stoptim) as sum, devid from dev_bandata group by ymd, devid) b where a.devid = b.devid

aaapeter

I just want to perform a joint query on the two tables in the DataSet.
And I don't really want to use thelb1514method.

simonsi

// Such t-sql must be wrong, because sumtable and name_table do not exist in the library
//Your previous job was to put the data set in memory, in the server running the web
//May not be on the same machine as the data server
select = "select ymd,sum,devname from sum_table,name_table where sum_table.devid=name_table.devid";
//So if you want to directly refill like this, it will not be successful anyway.
da = ????? //What should I write here?
System.Data.DataTable result_table = new System.Data.DataTable();
da.Fill(ds, "result_table");
//If you must want to do this, let us think about what we have and what we need
//We have two datasets on the web server, what we want is another table

You can clone a structure from sum_table, add new fields, and then insert the records of the two tables one by one
If sum_table will not be used later, you can directly add a new field to it, and then update the records one by one
In any case, this is not a good idea, a lot of orm supports relational mapping, but I personally do not like this, the advantage of relational database is that it is "relational"

First of all, ask me if this idea is feasible?
I checked related issues, it seems that it can be completed with a sql statement, but I think the readability is not high.
I am a novice, please help.

///In fact, a really better solution is to use tans-sql to directly reflect their relationship and solve

There are now two result sets
select ymd,sum(stoptim) as sum ,devid
from dev_bandata
group by ymd,devid

select devid ,devname
from dev_bandata


See what the relationship is
They come from a data table
May be one-to-one mapping/one-to-many/many-to-one/many-to-many
You can't see the relationship from your original text
We assume that it is one-to-one
Then
Simply change it

select ymd,sum(stoptim) as sum ,devid,devname
from dev_bandata
group by ymd,devid,devname
Just

peng16889

Just write it as a stored procedure