表中数据如下:
id name date
1 a 2007-4-13
2 b 2007-4-13
3 c 2007-4-13
4 d 2007-4-13
1 a 2007-4-14
1 a 2007-4-15
2 b 2007-4-14希望查询结果如下:
id name sum date
1 a 3 2007-4-13
1 a 3 2007-4-14
1 a 3 2007-4-15
2 b 2 2007-4-13
2 b 2 2007-4-14
3 c 1 2007-4-13
4 d 1 2007-4-13谢谢!
id name date
1 a 2007-4-13
2 b 2007-4-13
3 c 2007-4-13
4 d 2007-4-13
1 a 2007-4-14
1 a 2007-4-15
2 b 2007-4-14希望查询结果如下:
id name sum date
1 a 3 2007-4-13
1 a 3 2007-4-14
1 a 3 2007-4-15
2 b 2 2007-4-13
2 b 2 2007-4-14
3 c 1 2007-4-13
4 d 1 2007-4-13谢谢!
解决方案 »
- sql中查询不重复的字段值
- 小弟问个sql的简单的知识,关于把一个表的数据集插入到另外一个表中
- 有一个Access数据库和一个Sql数据库,两个库的结构完全一样,但是内容不一样,怎么写SQL语句,把Access中的内容不在SQL中的导入到SQL中?
- 复制中一个比较严重的问题(请各位大哥帮帮忙,小妹没有多少分了,邹大哥,我可是你的忠实的FASN哦)
- 标准无限分类表,求限制分类层级的方法?另有挑战性技术问题求助?
- 完全备份的数据库恢复时可以只恢复数据文件不恢复日志吗?
- 用觸發器實現不同服務器上相同表的同步問題
- SQL Sever 2000安装问题
- 进入迷糊状态,不得已,求助一个SQL
- SQL server2017 索引超出 了数据界限
- 日期格式调整
- 请问下面的查询用一条SQL语句怎么实现?
a.id,
a.name,
b.[sum],
a.date
from
表 a,
(
select
id,
count(*) as [sum]
from
表
group by
id
) as b
where
a.id=b.id
order by
a.id,
a.date
select
id,
name,
[sum]=(select count(*) from 表 where id=a.id),
date
from
表 a
order by
id,
date
on tb.tname=t.tname order by consum desc,tdate
from tb as a Order by Consum desc,tdate
select 1,'a','2007-4-13' union all
select 2,'b','2007-4-13' union all
select 3,'c','2007-4-13' union all
select 4,'d','2007-4-13' union all
select 1,'a','2007-4-14' union all
select 1,'a','2007-4-15' union all
select 2,'b','2007-4-14'
select [id],[name],(select count(*) from #t where id=a.id) as [sum],[date]
from #t a
order by [id],[date]drop table #t
union all select '2','b','2007-4-13'
union all select '3','c','2007-4-13'
union all select '4','d','2007-4-13'
union all select '1','a','2007-4-14'
union all select '1','a','2007-4-15'
union all select '2','b','2007-4-14'select id,name,sum=(case id when id then count(id) end)
,date from tt group by name,id,date order by id,nameselect * into #t from (select name,count(name) as ct from tt a group by name)bselect * from #tselect id,#t.name,#t.ct,date from tt,#t
where tt.name=#t.name order by id,tt.name,tt.datedrop table #t
drop table tt(所影响的行数为 7 行)id name sum date
---------- ---------- ----------- ------------------------------------------------------
1 a 1 2007-04-13 00:00:00.000
1 a 1 2007-04-14 00:00:00.000
1 a 1 2007-04-15 00:00:00.000
2 b 1 2007-04-13 00:00:00.000
2 b 1 2007-04-14 00:00:00.000
3 c 1 2007-04-13 00:00:00.000
4 d 1 2007-04-13 00:00:00.000
(所影响的行数为 4 行)id name ct date
---------- ---------- ----------- ------------------------------------------------------
1 a 3 2007-04-13 00:00:00.000
1 a 3 2007-04-14 00:00:00.000
1 a 3 2007-04-15 00:00:00.000
2 b 2 2007-04-13 00:00:00.000
2 b 2 2007-04-14 00:00:00.000
3 c 1 2007-04-13 00:00:00.000
4 d 1 2007-04-13 00:00:00.000