Find a SQL statement

135qiwang

Table 1
Name ID number

Table 2
Name ID No. Item Name


The total number of items in Table 2 needs to be queried and the name ID number in Table 2 corresponds to the name display in Table 1 after query.

fanfan1980

Paste the data and the results you want to see

xuming0207

// Need to query the total number of items in Table 2 and the name ID number in Table 2 corresponds to the name display in Table 1 after query?
There is no clearing.If you are querying the total number of items, there is no Group condition.If you want to display the name corresponding to the name ID, you must have the Group condition.

fanfan1980

select A. *, B. quantity from A
left join
(
select name ID number, quantity = sum (quantity) from B group by name ID number
) B on A. Name ID number = B. Name ID number

wgfxman

select A. name, A. name ID number, SUM (B. quantity) AS 'total', B. item name
FROM Table 1 A, Table 2 B
WHERE A. Name ID number = B. Name ID number
GROUP BY A. Name, A. Name ID number, B. Item name

qupengyun

select A. name, A. name ID number, SUM (B. quantity) AS 'total', B. item name
FROM table 1 A inner join table 2 B
on A. Name ID number = B. Name ID number
GROUP BY A. Name, A. Name ID number, B. Item name

aokawa

select A. name, A. name ID number, SUM (B. quantity) AS'total', B. item name
FROM Table 1 A, Table 2 B
WHERE A. Name ID number = B. Name ID number
GROUP BY A. Name, A. Name ID number, B. Item name

that's it!