更新按group by子句选择的几行 [英] update few rows select by group by clause

问题描述

专家，

感谢您的关注.

我有这个.

Hi Expert,

Thanks for your attention.

I have this.

ID Student Class
1  Raj     5
2  Ram     1
3  Ratan   4
4  Raj     6
5  Dijen   3
6  Ratan   7
7  Ritesh  12

这里的ID是标识号增加1

任何学生记录的首次出现.这些班级数据替换为1.
更新后我要

Here ID is identity number increasing 1

First occurence of any Student Record.These class data replace with 1.
After updation I want

ID Student Class
1  Raj     1
2  Ram     1
3  Ratan   1
4  Raj     6
5  Dijen   1
6  Ratan   7
7  Ritesh  1

预先感谢您的解决方案.

Thanks in advance for your solution.

推荐答案

请尝试以下查询:-

try below query:-

update tableA
set class ='1'
where id in (select min(id)
             from tabelA 
             group by name)

这是一个样本方法

Here is a sample approach

CREATE TABLE #Students (ID  INT IDENTITY(1,1), Student VARCHAR(50), Class INT)

INSERT INTO #Students
SELECT 'Raj', 5 UNION ALL
SELECT 'Ram', 1 UNION ALL
SELECT 'Ratan', 4 UNION ALL
SELECT 'Raj', 6  UNION ALL
SELECT 'Dijen', 3 UNION ALL
SELECT 'Ratan', 7 UNION ALL
SELECT 'Ritesh', 12


SELECT * FROM #Students


UPDATE #Students
SET Class = 1
WHERE ID IN
(SELECT MIN(ID) AS ID FROM #Students GROUP BY Student)


SELECT * FROM #Students


DROP TABLE #Students

根据我的理解，您希望将class列中的所有首次出现都更新为1.
如果是这样，请参考下面的链接.此外，您需要在语句中使用order by子句才能完成此工作.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102391 [ ^ ]


希望这会有所帮助.

Hi,

As per my understanding, you want to update all the first occurances to 1 in the class column.
If so, refer below link.Also, you need to use a order by clause in your statement to get this work.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102391[^]


Hope this helps.

这篇关于更新按group by子句选择的几行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！