第一题:
/*-----------------------------------------------
Table Temp:
字段A,int,PK
字段B,int,PK 是一个用户报名系统 A是用户ID,B是班级ID 现在要返回的是:同时报了多个指定班级的学生ID ,
比如有4个班级,班级A,班级B,班级C,班级D
学生报了三个班在表里就会有三条记录,报两个班就会有两条记录
要返回的是只报了传入的ID的班级的学生
传入班级B、班级C的ID,返回的就是只报了班级B和班级C两个班的学生
-----------------------------------------------------------*/
CREATE table Temp(A int,B int)
insert Temp
SELECT 1,11 union all
SELECT 1,12 union all
SELECT 1,13 union all
SELECT 2,11 union all
SELECT 2,12 union all
SELECT 3,11 union all
SELECT 3,12第二题:
表
ID PID NAME VALUE
1 0 A
2 1 B
3 0 C
4 2 D
5 3 E
6 4 F 更新后结果
ID PID NAME VALUE
1 0 A A
2 1 B AB
3 0 C C
4 2 D BD
5 3 E CE
6 4 F BDF
/*-----------------------------------------------
Table Temp:
字段A,int,PK
字段B,int,PK 是一个用户报名系统 A是用户ID,B是班级ID 现在要返回的是:同时报了多个指定班级的学生ID ,
比如有4个班级,班级A,班级B,班级C,班级D
学生报了三个班在表里就会有三条记录,报两个班就会有两条记录
要返回的是只报了传入的ID的班级的学生
传入班级B、班级C的ID,返回的就是只报了班级B和班级C两个班的学生
-----------------------------------------------------------*/
CREATE table Temp(A int,B int)
insert Temp
SELECT 1,11 union all
SELECT 1,12 union all
SELECT 1,13 union all
SELECT 2,11 union all
SELECT 2,12 union all
SELECT 3,11 union all
SELECT 3,12第二题:
表
ID PID NAME VALUE
1 0 A
2 1 B
3 0 C
4 2 D
5 3 E
6 4 F 更新后结果
ID PID NAME VALUE
1 0 A A
2 1 B AB
3 0 C C
4 2 D BD
5 3 E CE
6 4 F BDF
insert Temp
SELECT 1,11 union all
SELECT 1,12 union all
SELECT 1,13 union all
SELECT 2,11 union all
SELECT 2,12 union all
SELECT 3,11 union all
SELECT 3,12
select a from temp group by a having count(b)>1
go
drop table temp
/*
a
-----------
1
2
3
*/
第二题:
表
ID PID NAME VALUE
1 0 A
2 1 B
3 0 C
4 2 D
5 3 E
6 4 F 更新后结果
ID PID NAME VALUE
1 0 A A
2 1 B AB
3 0 C C
4 2 D BD
5 3 E CE
6 4 F BDF 这个是什么意思啊
insert Temp
SELECT 1,11 union all
SELECT 1,12 union all
SELECT 1,13 union all
SELECT 2,11 union all
SELECT 2,12 union all
SELECT 3,11 union all
SELECT 3,12
--第1题
--select a from temp group by a having count(b)>1
go
--第1题b
create table bj(id int,bjname varchar(10))
insert into bj
select 11,'A' union all
select 12,'B' union all
select 13,'C' union all
select 14,'D'
go
--只报了B,C没有,选只报了A,B
select distinct a from temp t where exists(select 1 from temp where a=t.a and b=11)
and exists(select 1 from temp where a=t.a and b=12)
and not exists(select 1 from temp where a=t.a and b<>11 and b<>12)
go
drop table temp,bj
/*
a
-----------
2
3
*/
insert Temp
SELECT 1,11 union all
SELECT 1,12 union all
SELECT 1,13 union all
SELECT 2,11 union all
SELECT 2,12 union all
SELECT 3,11 union all
SELECT 3,12
--第1题
--select a from temp group by a having count(b)>1
go
--第1题b
create table bj(id int,bjname varchar(10))
insert into bj
select 11,'A' union all
select 12,'B' union all
select 13,'C' union all
select 14,'D'
go
--只报了B,C没有,选只报了A,B
declare @b1 int,@b2 int
select @b1=11,@b2=12
select distinct a from temp t where exists(select 1 from temp where a=t.a and b=@b1)
and exists(select 1 from temp where a=t.a and b=@b2)
and not exists(select 1 from temp where a=t.a and b<>@b1 and b<>@b2)
go
drop table temp,bj
/*
a
-----------
2
3
*/
INSERT @TB
SELECT 1, 0, 'A', NULL UNION ALL
SELECT 2, 1, 'B', NULL UNION ALL
SELECT 3, 0, 'C', NULL UNION ALL
SELECT 4, 2, 'D', NULL UNION ALL
SELECT 5, 3, 'E', NULL UNION ALL
SELECT 6, 4, 'F', NULL;WITH CTE AS
(
SELECT ID,PID,NAME,CAST([NAME] AS VARCHAR(8000)) AS VALUE FROM @TB WHERE PID=0
UNION ALL
SELECT A.ID,A.PID,A.NAME,C.VALUE+A.[NAME] FROM @TB AS A,CTE AS C WHERE A.PID=C.ID
)
SELECT * FROM CTE ORDER BY ID
/*
ID PID NAME VALUE
----------- ----------- ---- --------------------------------------------------------
1 0 A A
2 1 B AB
3 0 C C
4 2 D ABD
5 3 E CE
6 4 F ABDF
*/
create table tb(ID int,PID int,NM varchar(5),VL varchar(10))
insert into tb select 1,0,'A',null
insert into tb select 2,1,'B',null
insert into tb select 3,0,'C',null
insert into tb select 4,2,'D',null
insert into tb select 5,3,'E',null
insert into tb select 6,4,'F',null
go
;with cte as(
select id,pid,nm,vl=convert(varchar(10),nm) from tb where pid=0
union all
select a.id,a.pid,a.nm,vl=convert(varchar(10),b.vl+a.nm) from tb a inner join cte b on a.pid=b.id
)select * from cte order by id
go
drop table tb
/*
id pid nm vl
----------- ----------- ----- ----------
1 0 A A
2 1 B AB
3 0 C C
4 2 D ABD
5 3 E CE
6 4 F ABDF
*/
insert into test0613(id,pid,name)
select 1,0,'A' UNION ALL
SELECT 2,1,'B' UNION ALL
SELECT 3,0,'C' UNION ALL
SELECT 4,2,'D' UNION ALL
SELECT 5,3,'E' UNION ALL
SELECT 6,4,'F'select *,dbo.xx_uf(id) from test0613
alter function xx_uf
(@id int)
returns varchar(30)
as
begin
declare @x varchar(30)
set @x='';WITH CTE AS
(
SELECT PID,id,[NAME] FROM test0613 WHERE ID=@id
UNION ALL
SELECT b.PID,b.id,b.[NAME] FROM CTE a, test0613 b where b.ID=a.PID
)SELECT @x=@x+name FROM CTE order by pidreturn @x
end
/*
id pid name value
----------- ----------- ---------- -------------------- ------------------------------
1 0 A NULL A
2 1 B NULL AB
3 0 C NULL C
4 2 D NULL ABD
5 3 E NULL CE
6 4 F NULL ABDF(6 行受影响)
*/
DECLARE @TB TABLE([ID] INT, [PID] INT, [NAME] VARCHAR(1), [VALUE] VARCHAR(10))
INSERT @TB
SELECT 1, 0, 'A', NULL UNION ALL
SELECT 2, 1, 'B', NULL UNION ALL
SELECT 3, 0, 'C', NULL UNION ALL
SELECT 4, 2, 'D', NULL UNION ALL
SELECT 5, 3, 'E', NULL UNION ALL
SELECT 6, 4, 'F', NULL
select a. id,a.pid,a.name,isnull(a.name,'')+isnull(b.name,'') from @tb a,
@tb b
where a.pid*=b.id------------------------------------
1 0 A A
2 1 B BA
3 0 C C
4 2 D DB
5 3 E EC
6 4 F FD借用7樓的數據.
到底是value=name+(pid对应的ID的name)還是value=name+(pid对应的ID的value)?
INSERT @TB
SELECT 1, 0, 'A', NULL UNION ALL
SELECT 2, 1, 'B', NULL UNION ALL
SELECT 3, 0, 'C', NULL UNION ALL
SELECT 4, 2, 'D', NULL UNION ALL
SELECT 5, 3, 'E', NULL UNION ALL
SELECT 6, 4, 'F', NULLDECLARE @LVL INT
DECLARE @T TABLE([ID] INT, [PID] INT, [NAME] VARCHAR(1), [VALUE] VARCHAR(10),LVL INT)SET @LVL=1
INSERT @T
SELECT ID,PID,NAME,NAME,@LVL FROM @TB WHERE PID=0WHILE @@ROWCOUNT>0
BEGIN
SET @LVL=@LVL+1
INSERT @T
SELECT A.ID,A.PID,A.NAME,T.VALUE+A.NAME,@LVL
FROM @TB AS A,@T AS T
WHERE A.PID=T.ID AND LVL=@LVL-1
ENDSELECT * FROM @T
第二题稍微修改下:
表
ID PID NAME VALUE
1 0 A
2 1 B
3 0 C
4 2 D
5 3 E
6 4 F 更新后结果
ID PID NAME VALUE
1 0 A A
2 1 B AB
3 0 C C
4 2 D ABD
5 3 E CE
6 4 F ABDF 这里的value=name+(pid对应的ID的value)!!
CREATE table Temp(A int,B int)
insert Temp
SELECT 1,11 union all
SELECT 1,12 union all
SELECT 1,13 union all
SELECT 2,11 union all
SELECT 2,12 union all
SELECT 3,11 union all
SELECT 3,12 declare @ClassIDs nvarchar(30)
select @ClassIDs='11,12'SELECT distinct [A]
FROM [dbo].[Temp] T
WHERE CHARINDEX(','+LTRIM(B)+',',','+@ClassIDs+',')>0
AND NOT EXISTS(SELECT 1 FROM Temp WHERE A=T.A AND CHARINDEX(','+LTRIM(B)+',',','+@ClassIDs+',')=0)
A
-----------
2
3(2 行受影响)
create table tb(ID int,PID int,NM varchar(5),VL varchar(10))
insert into tb select 1,0,'A',null
insert into tb select 2,1,'B',null
insert into tb select 3,0,'C',null
insert into tb select 4,2,'D',null
insert into tb select 5,3,'E',null
insert into tb select 6,4,'F',null
go
select id,pid,nm,vl=convert(varchar(10),nm) into #t from tb where pid=0
while exists(select 1 from tb a where not exists(select 1 from #t where id=a.id))
insert into #t
select a.id,a.pid,a.nm,vl=b.vl+a.nm from tb a inner join #t b on a.pid=b.id
where not exists(select 1 from #t where id=a.id)
select * from #t order by id
go
drop table tb,#t
/*
id pid nm vl
----------- ----------- ----- ----------
1 0 A A
2 1 B AB
3 0 C C
4 2 D ABD
5 3 E CE
6 4 F ABDF
*/
这有个地方不太懂
CHARINDEX(','+LTRIM(B)+',',','+@ClassIDs+',')>0
-- 为什么要用LTRIM(B) 感觉没必要,去掉空格?可是没空格?