表A和表B
A结构如下: B结构如下:
ID Name Number ID Name Max
001 abc 10 001 abc 15
001 abc 3 002 aa 35
001 abc 4 003 nb 2
002 aa 12
002 aa 20
003 nb 1
003 nb 2把A进行分组,结果如下:
ID Name Number
001 abc 17
002 aa 32
003 nb 3我现在要显示分组后A表的Number>B表的Max的记录
我的语句是这样的:Select a.id,a.name,Sum(a.number)As Number From a Inner Join b On a.id=b.id Where Number>=b.max Group By a.id,a.name
可是这样显示的结果是正确,请问该怎么写这样的语句。谢谢
A结构如下: B结构如下:
ID Name Number ID Name Max
001 abc 10 001 abc 15
001 abc 3 002 aa 35
001 abc 4 003 nb 2
002 aa 12
002 aa 20
003 nb 1
003 nb 2把A进行分组,结果如下:
ID Name Number
001 abc 17
002 aa 32
003 nb 3我现在要显示分组后A表的Number>B表的Max的记录
我的语句是这样的:Select a.id,a.name,Sum(a.number)As Number From a Inner Join b On a.id=b.id Where Number>=b.max Group By a.id,a.name
可是这样显示的结果是正确,请问该怎么写这样的语句。谢谢
having Sum(a.number)>=b.max
where a.id=b.id and sum(a.number)>b.max
group by a.id,a.name
------
Select a.id,a.name,Sum(a.number) As Number From a Inner Join b On a.id=b.id Group By a.id,a.name,b.max
having Sum(a.number)>=b.max
windindance(风舞轻扬) 你写的语句在Access下运行出错提示:Sum(a.number)>=b.max表达式错误。但是把b.max换成具体的数字就可以了,能不能再帮我改一改啊大侠......
JOIN b ON C.id = b.id AND C.Number > b.Max
Group By a.id,a.name
having Sum(a.number)>=(select top 1 max from b where a.id=b.id)