There are two tables:
TABLE1
myid
name
TABLE 2
id
myid
count
Suppose you have one TABLE1 record and three associated TABLE2s.
TABLE1
myid=1
name = Taro
TABLE 2
id = 1
myid=1
count = 2
id = 2
myid=1
count = 3
id = 3
myid=1
count = 1
Here, I would like to take out TABLE1 and all related TABLE2 records, and get only the top line as a result.However, I would like to give the total value of the three records only for the count result.
What kind of SQL statement should I write?
The desired acquisition results are as follows:
myid name id count
1 Taro 16
select TABLE 1.*, TABLE2.id, (select sum(count) from TABLE 2 where TABLE 2. myid = TABLE 1. myid) from TABLE 1 inner join TABLE 2 using (myid) order by myid asc limit 1;
Is that so?
I haven't tried it
All you have to do is group them and use their respective aggregation functions.However, there is no concept of "top row" in SQL, so other conditioning is required, such as MIN.
SELECT TABLE 1.myid,name,id,count
FROM TABLE 1 INNER JOIN (SELECT MYID, MIN(id) ASid, SUM(count) AS count
FROM TABLE 2 GROUP BY MYID) AST ON TABLE 1.myid = t.myid
and so on.
declare@id smallint
set@id = 1
select top1
t1.myid
, t1.name
, t2.myid
, (select sum(t2.count) from table2t2where t2.myid=@id)
as'count_sum'
from table 1 t1
where1.myid=@id
group by
t1.myid
, t1.name
order by
t1.myid
What do you think about this?
It's not a good example because it's pushing hard...
© 2024 OneMinuteCode. All rights reserved.