;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY A ORDER BY GETDATE())RN
,*
FROM TB
)
SELECT T1.A,SUM(T1.B)B,T2.C,T2.D
FROM TB T1
JOIN CTE T2 ON T1.A=T2.A AND T2.RN=1
GROUP BY T1.A,T2.C,T2.D你最好有一个,A相同时,指定顺序的列
SELECT ROW_NUMBER()OVER(PARTITION BY A ORDER BY GETDATE())RN
,*
FROM TB
)
SELECT T1.A,SUM(T1.B)B,T2.C,T2.D
FROM TB T1
JOIN CTE T2 ON T1.A=T2.A AND T2.RN=1
GROUP BY T1.A,T2.C,T2.D你最好有一个,A相同时,指定顺序的列
解决方案 »
- 日期排序问题?
- 存储过程中对sql语句上锁
- 难度五星问题:如何获得存储过程结果集的字段名称和类型?
- sql server 2000,一次写入超过20条记录就不正常,好奇怪啊。
- SQL 数据库表的导出疑点
- 数据库中的除法如何理解?
- 如何在sqlserver中实现类似oracle的select * from (select kk from dd)的查询?
- 在线等待....数据库中的id问题...
- zqllyh(您问我也问总可以问出个所以然),pengdali(大力)和j9988(j9988) 请进,这900分特别酬谢 3
- UML、JAVA项目小组
- sql查询语句求助
- 事件日志表的处理方案数据类型应该为哪种?
(
SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY (SELECT 1)) AS ROW,
*,
SUM(B) OVER (PARTITION BY A) AS BSum
FROM TBA
)
SELECT A,BSum,C,D
FROM CTe
WHERE ROW = 1
比如你的数据
A B C D E
--------------------------------------------------------------------------------------------
1 10 ACD 张三 2
1 10 ACD 张三Mr 1
当A相同时,根据E列的不同来决定取哪一列,
如果没有这一列,想根据从上往下的顺序取上面第一列,
在很多情况下是不准确的,SQL SERVER返回的数据不一定会按照这个顺序因此,我问的是,有没有类似E列这个功能的列
create table ew(A int,B int,C varchar(10),D varchar(10))insert into ew
select 1,10,'ACD','张三' union all
select 2,10,'SEC','李四Miss' union all
select 1,10,'ACD','张三Mr' union all
select 2,10,'SEC','李四' union all
select 3,10,'CCB','小明' union all
select 4,10,'MCB','Mika' union all
select 5,10,'DDA','John' union all
select 3,10,'CCB','小明MSS' union all
select 5,10,'DDA','John Mr'
select t1.A,t1.B,t2.C,t2.D
from
(select A,
sum(B) 'B'
from ew
group by A) t1
inner join
(select A,C,D,
row_number() over(partition by A order by getdate()) 'rn'
from ew) t2 on t1.A=t2.A
where t2.rn=1/*
A B C D
----------- ----------- ---------- ----------
1 20 ACD 张三Mr
2 20 SEC 李四Miss
3 20 CCB 小明
4 10 MCB Mika
5 20 DDA John(5 row(s) affected)
*/
AS
WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY A ORDER BY GETDATE())RN
,*
FROM TB
)
SELECT T1.A,SUM(T1.B)B,T2.C,T2.D
FROM TB T1
JOIN CTE T2 ON T1.A=T2.A AND T2.RN=1
GROUP BY T1.A,T2.C,T2.D