select xmid,Sum(money) as totalMoney,bbh,times from Table group by xmid,bbh,times
create table #temp ( id int, [money] decimal(18,4), bbh varchar(20), times datetime ) insert into #temp values(101,10,'1.1版本','2013.12.6') insert into #temp values(101,20,'1.1版本','2013.12.6') insert into #temp values(101,30,'1.1版本','2013.12.6')insert into #temp values(102,10,'1.1版本','2013.12.6')insert into #temp values(102,20,'1.2版本','2013.12.7') insert into #temp values(102,20,'1.2版本','2013.12.7') -- --问题是我现在想 --查询出来的数据如: --101 40(合并) 1.2版本 2013.12.7(时间最后插入) --102 10(合并) 1.1版本 2013.12.6(时间最后插入 select id,sum([money]),bbh,times from #temp group by id,bbh,times drop table #temp//结果 101 60.0000 1.1版本 2013-12-06 00:00:00.000 102 10.0000 1.1版本 2013-12-06 00:00:00.000 102 40.0000 1.2版本 2013-12-07 00:00:00.000
select id,sum([money]),bbh,times from #temp where (id = 102 and times = '2013-12-06 00:00:00.000') or (id = 101 and times = '2013-12-07 00:00:00.000') group by id,bbh,times
select id,sum([money]),bbh,times from #temp where (id = 102 and times = (select top 1 max(times) from #temp where id = 102)) or (id = 101 and times = (select top 1 max(times) from #temp where id = 101)) group by id,bbh,times
create table #temp
(
id int,
[money] decimal(18,4),
bbh varchar(20),
times datetime
)
insert into #temp values(101,10,'1.1版本','2013.12.6')
insert into #temp values(101,20,'1.1版本','2013.12.6')
insert into #temp values(101,30,'1.1版本','2013.12.6')insert into #temp values(102,10,'1.1版本','2013.12.6')insert into #temp values(102,20,'1.2版本','2013.12.7')
insert into #temp values(102,20,'1.2版本','2013.12.7')
--
--问题是我现在想
--查询出来的数据如:
--101 40(合并) 1.2版本 2013.12.7(时间最后插入)
--102 10(合并) 1.1版本 2013.12.6(时间最后插入
select id,sum([money]),bbh,times from #temp
group by id,bbh,times
drop table #temp//结果
101 60.0000 1.1版本 2013-12-06 00:00:00.000
102 10.0000 1.1版本 2013-12-06 00:00:00.000
102 40.0000 1.2版本 2013-12-07 00:00:00.000
select id,sum([money]),bbh,times from #temp
where (id = 102 and times = '2013-12-06 00:00:00.000') or
(id = 101 and times = '2013-12-07 00:00:00.000')
group by id,bbh,times
select id,sum([money]),bbh,times from #temp
where (id = 102 and times = (select top 1 max(times) from #temp where id = 102)) or
(id = 101 and times = (select top 1 max(times) from #temp where id = 101))
group by id,bbh,times
我就是卡到同过时间如何让数据库只显示 最新版本数据这里了 (同一个xmid低版本的都不显示)
select id,sum([money]),bbh,times from #temp c
where id in(select t.id from (select id,max(times)[时间] from #temp group by id) t )
group by id,bbh,times
DataTable dt2 = dt.Clone();
dt.DefaultView.Sort = "xmid,[date] desc";
string Xmid = "";
foreach (DataRow row in dt.Rows)
{
if (Xmid != row["xmid"].ToString())
{
dt2.ImportRow(row);
Xmid = row["xmid"].ToString();
}
}
dt2 就是LZ要的结果,这样做安全,用楼上的方法数据多了会出现堆栈溢出
GROUP BY 项目,版本号,时间
这样一来关键是时间,时间不一样会出很多条
可以TO_CHAR(时间,'YYYY-MM-DD')转成天或转成月