如果基础表的编号是唯一的:SELECT
ta.id, ta.name, ta.num, SUM(tb.num), ta.num - SUM(tb.num)
FROM ta
LEFT JOIN tb ON ta.id = tb.id
GROUP BY ta.id
如果基础表的编号不是唯一的:
SELECT
ta.id, ta.name, SUM(ta.num)/COUNT(tb.id), SUM(tb.num), ta.num - SUM(tb.num)
FROM ta
LEFT JOIN tb ON ta.id = tb.id
GROUP BY ta.id
ta.id, ta.name, ta.num, SUM(tb.num), ta.num - SUM(tb.num)
FROM ta
LEFT JOIN tb ON ta.id = tb.id
GROUP BY ta.id
如果基础表的编号不是唯一的:
SELECT
ta.id, ta.name, SUM(ta.num)/COUNT(tb.id), SUM(tb.num), ta.num - SUM(tb.num)
FROM ta
LEFT JOIN tb ON ta.id = tb.id
GROUP BY ta.id
有没想到“与运算”的多字符串正则表示式搜索了?
我问了mysql.com,竟叫我交费学习,哈哈。
:SUM(ta.num)就可以了。可能我没理解什么是编号不唯一。
基础表编号不唯一是什么意思?
1基础表
编号 图书 购入总数
1 we 6
1 we 3这种情况是不是编号不唯一?
如果是的话,报表的第三列应该是9=SUM(ta.num) 啊?