如题:表users
userid username
1 aa
2 bb
3 cc
表:projects
pid pname userid
1 计算机 1
2 电脑桌 2
3 笔记本 3
4 水杯 1
5 厨具 2
6 菜刀 3
7 粉丝 3
============================================================
效果如下:
序号 userid username pid pname cnt(此用户拥有的商品总数)
1 1 aa 1 计算机 2
2 1 aa 4 水杯 2
1 2 bb 2 电脑桌 2
2 2 bb 5 厨具 2
1 3 cc 3 笔记本 3
2 3 cc 6 菜刀 3
3 3 cc 7 粉丝 3
==================================================================
userid username
1 aa
2 bb
3 cc
表:projects
pid pname userid
1 计算机 1
2 电脑桌 2
3 笔记本 3
4 水杯 1
5 厨具 2
6 菜刀 3
7 粉丝 3
============================================================
效果如下:
序号 userid username pid pname cnt(此用户拥有的商品总数)
1 1 aa 1 计算机 2
2 1 aa 4 水杯 2
1 2 bb 2 电脑桌 2
2 2 bb 5 厨具 2
1 3 cc 3 笔记本 3
2 3 cc 6 菜刀 3
3 3 cc 7 粉丝 3
==================================================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([COL1] varchar(1),[COL2] int)
insert [TB]
select 'A',12 union all
select 'B',12 union all
select 'A',1 union all
select 'C',4 union all
select 'B',5 union all
select 'B',7
GO--> 查询结果
SELECT ID=ROW_NUMBER() OVER (partition by COL1 ORDER BY COL2),*
FROM [TB]
--> 删除表格
--DROP TABLE [TB]
SELECT ID=RANK() OVER ( ORDER BY COL1),*
FROM [TB]SELECT ID=Dense_Rank () OVER ( ORDER BY COL1),*
FROM [TB]SELECT ID=NTILE(4) OVER ( ORDER BY COL1),*
FROM [TB]
if object_id('tempdb.dbo.#u') is not null drop table #u
create table #u(userid int, username varchar(8))
insert into #u
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc'
--> 测试数据:#p
if object_id('tempdb.dbo.#p') is not null drop table #p
create table #p(pid int, pname varchar(8), userid int)
insert into #p
select 1, '计算机', 1 union all
select 2, '电脑桌', 2 union all
select 3, '笔记本', 3 union all
select 4, '水杯', 1 union all
select 5, '厨具', 2 union all
select 6, '菜刀', 3 union all
select 7, '粉丝', 3select 序号=row_number()over(partition by u.userid order by p.pid),
u.userid, u.username, p.pid, p.pname,
cnt = count(1)over(partition by u.userid)
from #u u join #p p on u.userid=p.userid/*
序号 userid username pid pname cnt
-------------------- ----------- -------- ----------- -------- -----------
1 1 aa 1 计算机 2
2 1 aa 4 水杯 2
2 2 bb 5 厨具 2
1 2 bb 2 电脑桌 2
1 3 cc 3 笔记本 3
2 3 cc 6 菜刀 3
3 3 cc 7 粉丝 3
*/
序号=row_number()over(partition by username order by getdate()),*
from
(select a.userid,b.* from users a,projects b where a.userid=b.userid)t
u.userid, u.username, p.pid, p.pname,
cnt = count(1)over(partition by u.userid)
from #u u join #p p on u.userid=p.userid/*
序号 userid username pid pname cnt
-------------------- ----------- -------- ----------- -------- -----------
1 1 aa 1 计算机 2
2 1 aa 4 水杯 2
1 2 bb 2 电脑桌 2
2 2 bb 5 厨具 2
1 3 cc 3 笔记本 3
2 3 cc 6 菜刀 3
3 3 cc 7 粉丝 3
*/
with cte as (select a.userid,b.username,a.pid,a.pname,c.cnt
from projects a
left join users b on a.userid=b.userid
left join (select userid,count(*) as cnt from projects group by userid) c on a.userid=c.userid)select 序号=ROW_NUMBER() OVER(partition by userid ORDER BY pid),userid,username,pid,pname,cnt from cte