简化下如下:
表A
ID NAME
1 Jack
2 John
3 Mary
4 Robin表B
ID UserId(对应表A ID) PID(参与项目ID)
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1想得到的结果是
JACK
JACK
JACK
John
John
Mary
Robin 说明 :查询表A User 并结合表B参与大于1个的累计改用户
谢谢各位~~
表A
ID NAME
1 Jack
2 John
3 Mary
4 Robin表B
ID UserId(对应表A ID) PID(参与项目ID)
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1想得到的结果是
JACK
JACK
JACK
John
John
Mary
Robin 说明 :查询表A User 并结合表B参与大于1个的累计改用户
谢谢各位~~
insert into a values(1 ,'Jack')
insert into a values(2 ,'John')
insert into a values(3 ,'Mary')
insert into a values(4 ,'Robin')
create table B(ID int,UserId int,PID int)
insert into b values(1 ,1 ,1)
insert into b values(2 ,1 ,2)
insert into b values(3 ,1 ,3)
insert into b values(4 ,2 ,1)
insert into b values(5 ,2 ,2)
insert into b values(6 ,3 ,1)
goselect a.name from a left join b on a.id = b.userid
/*
name
----------
Jack
Jack
Jack
John
John
Mary
Robin(所影响的行数为 7 行)
*/drop table a , b
insert into a values(1 ,'Jack')
insert into a values(2 ,'John')
insert into a values(3 ,'Mary')
insert into a values(4 ,'Robin')
create table B(ID int,UserId int,PID int)
insert into b values(1 ,1 ,1)
insert into b values(2 ,1 ,2)
insert into b values(3 ,1 ,3)
insert into b values(4 ,2 ,1)
insert into b values(5 ,2 ,2)
insert into b values(6 ,3 ,1)
goselect a.name from a left join b on a.id = b.userid
/*
name
----------
Jack
Jack
Jack
John
John
Mary
Robin(所影响的行数为 7 行)
*/
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([ID] [int],[NAME] [nvarchar](10))
INSERT INTO [A]
SELECT '1','Jack' UNION ALL
SELECT '2','John' UNION ALL
SELECT '3','Mary' UNION ALL
SELECT '4','Robin'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([ID] [int],[UserId] [int],[PID] [int])
INSERT INTO [B]
SELECT '1','1','1' UNION ALL
SELECT '2','1','2' UNION ALL
SELECT '3','1','3' UNION ALL
SELECT '4','2','1' UNION ALL
SELECT '5','2','2' UNION ALL
SELECT '6','3','1'-->SQL查询如下:SELECT Name FROM [A] OUTER APPLY(SELECT 1 a FROM B WHERE a.id=b.UserId) c
/*
Name
----------
Jack
Jack
Jack
John
John
Mary
Robin(7 行受影响)
*/