从不同的结果中选择后获取身份

2021-09-10 00:00:00 sql tsql sql-server
Model_Tabl ( ID, ModelName, ModelQuantity)
Items_Tabl ( I_Code, IName, ID)

将新行插入 (Model_Table) 后 - 触发器将多行插入 (Items_Table) 取决于 (Model_Table) 中的 ModelQuantity,直到现在它的工作正常

after inserting new row into (Model_Table) - Triggers insert multi row into (Items_Table) Depend on ModelQuantity from (Model_Table) , and until now its work fine

I Created "选择不同的 ModelName , Sum(ModelQuantity) group by ModelName"我的结果很好

I Created "select distinct ModelName , Sum(ModelQuantity) group by ModelName" and i got result fine

我的问题是:

当我从 (DISTINCT) 查询中选择模型名称时,我想知道我从 (Model_Table) 中选择了哪个 (ID)

When i select model name from (DISTINCT) query i want to know which (ID) I selected from (Model_Table)

Model_ID (TO) Model_Name = 1 (TO) 许多ty

Model_ID (TO) Model_Name = 1 (TO) Many ty

推荐答案

首先,你不需要 DISTINCT 那里,所以你可以摆脱它.然后,你可以试试这个:

First of all, you don't need the DISTINCT there, so you can get rid of it. And then, you can try this:

SELECT ID, ModelName, SUM(ModelQuantity) Quantity
FROM Model_Tabl 
GROUP BY ID, ModelName

好的,正如我在评论中解释的那样,如果您有多个同名的 ID,则需要选择一个,即最大 ID 或最小 ID(最终还是没有意义)仅在您实际需要识别该 ID 时选择一个 ID),但是,这里是:

Ok, as I explained on my comment, if you have multiple IDs with the same name, you need to choose one, either the max or the min ID (it still doesn't make sense that you are going to end up just choosing one ID when you actually need to identify that ID), but well, here it is:

SELECT MIN(ID) ID, ModelName, SUM(ModelQuantity) Quantity
FROM Model_Tabl 
GROUP BY ModelName

相关文章