SQL一道繁琐的题。。向达人请教

Question

select *
from 表A a inner join 表A b
on a.id=b.id

Answer 1

select *
from 表A a inner join 表B b
on a.id=b.id

Answer 2

看错
select *
from 表A a left outer join 表B b
on a.id=b.id

Answer 3

1100 Name2 290  ?1100 Name2 190  才对 

Answer 4

你又看错了。。ID   NAME   Num1 Num2 Num3 
1000 Name1  590 0 0 
1100 Name2  290 
1110 Name3  100 
1120 Name4  100 
1200 Name5  100 
1300 Name6  100 
1400 Name7  100 
2000 Name8  600 
2100 Name9  500 
2110 Name10 100 
2120 Name11 100 
2130 Name12 100 
2140 Name13 100 
2200 Name14 100 Num1是有统计功能的。

Answer 5

刚开始的时候我也没太看空。。你仔细读读这句话
表A中ID值为1000或2000的不能输入数据，ID本身是关系ID，有子ID时，本身没有数据，统计其子ID的合计，没有时显示其本身，b.num1    b.num2    b.num3可为NULL，为null时，统计为0 
B表中的ID是可重复的。。实际情况是第四为数也可以算作统计的一个子ID的。。
抛出实际情况拿这个题说1000又子ID那么他就显示所有子ID的和
1100有子ID显示所有子ID的和。
1110无子ID显示本身。。
重复子ID显示最高Num1.。
2000同理

Answer 6

看表B。。1100的子ID又三个。。1110、1110和1120    SUM=190

Answer 7

看表B。。1100的子ID又三个。。1110、1110和1120    SUM=290打错。。

Answer 8

select A.id,A.name, num1= case when RIGHT(LEFT(a.id,2),1)='0' then b.num1 else A.Num1 end,
                   num2= case when RIGHT(LEFT(a.id,2),1)='0' then b.num2 else A.Num2 end,
                   num3= case when RIGHT(LEFT(a.id,2),1)='0' then b.num3 else A.Num3 end
   
from 
( select A.id,A.name,isnull(B.Num1,0) Num1 ,isnull(B.Num2,0) Num2,isnull(B.Num3,0) Num3
  from  tableA 
   left join 
      (select ID,SUM(isnull(Num1,0))Num1,SUM(isnull(Num2,0))Num2,SUM(isnull(Num3,0))Num3
       from tableB 
    group by id
    )B on(tableA .id=B.id)
) A left join(
select SUM(num1) num1, SUM(num2) num2, SUM(num3) num3, left(id,1) idl
from
(
select A.id,A.name,isnull(B.Num1,0) Num1 ,isnull(B.Num2,0) Num2,isnull(B.Num3,0) Num3
from tableA   left join 
    (select ID,SUM(isnull(Num1,0))Num1,SUM(isnull(Num2,0))Num2,SUM(isnull(Num3,0))Num3
    from tableB 
    group by id
   )  B on(tableA .id=B.id)
) As A 
group by left(id,1)
) AS B 
on left(A.id,1)=B.idl

Answer 9

测试结果1000 Name1 590 0 0
1100 Name2 0 0 0
1110 Name3 190 0 0
1120 Name4 100 0 0
1200 Name5 100 0 0
1300 Name6 100 0 0
1400 Name7 100 0 0
2000 Name8 600 0 0
2100 Name9 0 0 0
2110 Name10 200 0 0
2120 Name11 100 0 0
2130 Name12 100 0 0
2140 Name13 100 0 0
2200 Name14 100 0 0

Answer 10

--------测试数据select A.id,A.name, num1= case when RIGHT(LEFT(a.id,2),1)='0' then b.num1 else A.Num1 end,
                   num2= case when RIGHT(LEFT(a.id,2),1)='0' then b.num2 else A.Num2 end,
                   num3= case when RIGHT(LEFT(a.id,2),1)='0' then b.num3 else A.Num3 end
   
from 
(select A.id,A.name,isnull(B.Num1,0) Num1 ,isnull(B.Num2,0) Num2,isnull(B.Num3,0) Num3
from 
(select 1000 id, 'Name1' name
union all
select 1100 id, 'Name2' name
union all
select 1110 id, 'Name3' name
union all
select 1120 id, 'Name4' name
union all
select 1200 id, 'Name5' name
union all
select 1300 id, 'Name6' name
 
union all
select 1400 id, 'Name7' name
union all
 select 2000 , 'Name8' name
 union all 
  select 2100 , 'Name9' name
 union all
  select 2110 , 'Name10' name
  union all
  select 2120 , 'Name11' name
  union all
  select 2130 , 'Name12' name
  union all
  select 2140 , 'Name13' name
  union all
  select 2200 , 'Name14' name
) A left join (select ID,SUM(isnull(Num1,0))Num1,SUM(isnull(Num2,0))Num2,SUM(isnull(Num3,0))Num3
from
(
select 1110 id, 100  Num1, null num2,null num3
union all
select 1120 id, 100  Num1, null num2,null num3
union all
select 1110 id, 90  Num1, null num2,null num3
union all
select 1200 id, 100  Num1, null num2,null num3
union all
select 1300 id, 100  Num1, null num2,null num3
union all
select 1400 id, 100  Num1, null num2,null num3
union all
select 2110 id, 100  Num1, null num2,null num3
union all
select 2110 id, 100  Num1, null num2,null num3 union all
select 2120 id, 100  Num1, null num2,null num3 union all
select 2130 id, 100  Num1, null num2,null num3 
union all
select 2140 id, 100  Num1, null num2,null num3
union all
select 2200 id, 100  Num1, null num2,null num3
) b
group by id)B on(A.id=B.id)) A left join
(
select SUM(num1) num1, SUM(num2) num2, SUM(num3) num3, left(id,1) idl
from
(
select A.id,A.name,isnull(B.Num1,0) Num1 ,isnull(B.Num2,0) Num2,isnull(B.Num3,0) Num3
from 
(select 1000 id, 'Name1' name
union all
select 1100 id, 'Name2' name
union all
select 1110 id, 'Name3' name
union all
select 1120 id, 'Name4' name
union all
select 1200 id, 'Name5' name
union all
select 1300 id, 'Name6' name
 
union all
select 1400 id, 'Name7' name
union all
 select 2000 , 'Name8' name
 union all 
  select 2100 , 'Name9' name
 union all
  select 2110 , 'Name10' name
  union all
  select 2120 , 'Name11' name
  union all
  select 2130 , 'Name12' name
  union all
  select 2140 , 'Name13' name
  union all
  select 2200 , 'Name14' name
) A left join (select ID,SUM(isnull(Num1,0))Num1,SUM(isnull(Num2,0))Num2,SUM(isnull(Num3,0))Num3
from
(
select 1110 id, 100  Num1, null num2,null num3
union all
select 1120 id, 100  Num1, null num2,null num3
union all
select 1110 id, 90  Num1, null num2,null num3
union all
select 1200 id, 100  Num1, null num2,null num3
union all
select 1300 id, 100  Num1, null num2,null num3
union all
select 1400 id, 100  Num1, null num2,null num3
union all
select 2110 id, 100  Num1, null num2,null num3
union all
select 2110 id, 100  Num1, null num2,null num3 union all
select 2120 id, 100  Num1, null num2,null num3 union all
select 2130 id, 100  Num1, null num2,null num3 
union all
select 2140 id, 100  Num1, null num2,null num3
union all
select 2200 id, 100  Num1, null num2,null num3
) b
group by id)B on(A.id=B.id)) As A 
group by left(id,1)) AS B 
on left(A.id,1)=B.idl

Answer 11

测试结果应该是
1000 Name1  590  0 0 
1100 Name2  290  0 0 
1110 Name3  100  0 0 
1120 Name4  100  0 0 
1200 Name5  100  0 0 
1300 Name6  100  0 0 
1400 Name7  100  0 0 
2000 Name8  600  0 0 
2100 Name9  500  0 0 
2110 Name10 100  0 0 
2120 Name11 100  0 0 
2130 Name12 100  0 0 
2140 Name13 100  0 0 
2200 Name14 100  0 0 
才对

Answer 12

你贴的示例数据不对，你表里有两条1110DECLARE @a TABLE([ID] INT,[NAME] NVARCHAR(6))
INSERT @a SELECT 1000,N'Name1'
UNION ALL SELECT 1100,N'Name2'
UNION ALL SELECT 1110,N'Name3'
UNION ALL SELECT 1120,N'Name4'
UNION ALL SELECT 1200,N'Name5'
UNION ALL SELECT 1300,N'Name6'
UNION ALL SELECT 1400,N'Name7'
UNION ALL SELECT 2000,N'Name8'
UNION ALL SELECT 2100,N'Name9'
UNION ALL SELECT 2110,N'Name10'
UNION ALL SELECT 2120,N'Name11'
UNION ALL SELECT 2130,N'Name12'
UNION ALL SELECT 2140,N'Name13'
UNION ALL SELECT 2200,N'Name14'DECLARE @b TABLE([ID] INT,[Num1] INT,[Num2] INT,[Num3] INT)
INSERT @b SELECT 1110,100,NULL,NULL
UNION ALL SELECT 1120,100,NULL,NULL
UNION ALL SELECT 1110,90 ,NULL,NULL
UNION ALL SELECT 1200,100,NULL,NULL
UNION ALL SELECT 1300,100,NULL,NULL
UNION ALL SELECT 1400,100,NULL,NULL
UNION ALL SELECT 2110,100,NULL,NULL
UNION ALL SELECT 2110,100,NULL,NULL
UNION ALL SELECT 2120,100,NULL,NULL
UNION ALL SELECT 2130,100,NULL,NULL
UNION ALL SELECT 2140,100,NULL,NULL
UNION ALL SELECT 2200,100,NULL,NULL
/************/
/*Test  Data*/
/***fcuandy**/
/*2009-02-11*/
/************/
SELECT c.id,d.num1,d.num2,d.num3 FROM
(
SELECT *,REVERSE(STUFF(REVERSE(ID),1,PATINDEX('%[^0]%',REVERSE(ID))-1,'')) pat FROM @a
) c
OUTER APPLY
(
SELECT SUM(ISNULL(Num1,0)) NUM1, SUM(ISNULL(Num2,0)) NUM2, SUM(ISNULL(Num3,0)) NUM3
FROM @a a
INNER JOIN @b b
ON a.id = b.id
WHERE a.id LIKE c.pat + '%'
) d
/*
1000 590 0 0
1100 290 0 0
1110 190 0 0
1120 100 0 0
1200 100 0 0
1300 100 0 0
1400 100 0 0
2000 600 0 0
2100 500 0 0
2110 200 0 0
2120 100 0 0
2130 100 0 0
2140 100 0 0
2200 100 0 0
*/

Answer 13

ljhcy99   你还差2级子ID的计算没写进去呵呵
我这分少了。。忘记设置了其实。。
搞定了一会另外给你加分。。

Answer 14

 fcuandy  老大。。这个题是正确的开始我也觉得不对。。后来我确定了下确实是对的又两条1110.还有两条2110

Answer 15

有子ID时，本身没有数据，统计其子ID的合计，没有时显示其本身

Answer 16

fcuandy老大。你考虑下我说的那个呢。
这题是给输出结果让写语句。。问怎么才能输出这个数据
你说1110是没有子ID的。没有子ID的显示本身。所以显示100或者显示90都应该可以吧？或者取最大值呢。

Answer 17

if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([ID] varchar(4),[NAME] varchar(6))
insert [ta]
select '1000','Name1' union all
select '1100','Name2' union all
select '1110','Name3' union all
select '1120','Name4' union all
select '1200','Name5' union all
select '1300','Name6' union all
select '1400','Name7' union all
select '2000','Name8' union all
select '2100','Name9' union all
select '2110','Name10' union all
select '2120','Name11' union all
select '2130','Name12' union all
select '2140','Name13' union all
select '2200','Name14'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(4),[Num1] int,[Num2] int,[Num3] int)
insert [tb]
select '1110',100,null,null union all
select '1120',100,null,null union all
select '1110',90,null,null union all
select '1200',100,null,null union all
select '1300',100,null,null union all
select '1400',100,null,null union all
select '2110',100,null,null union all
select '2110',100,null,null union all
select '2120',100,null,null union all
select '2130',100,null,null union all
select '2140',100,null,null union all
select '2200',100,null,nullselect * from [ta]
select * from [tb]
goselect  a.id,a.name
,Num1= isnull(case when len(replace(a.id,'0',''))<3 
then (select sum(Num1) from tb where replace(id,'0','') like replace(a.id,'0','')+'%')
 else
(select max(Num1) from tb where id=a.id)
end,0)
,Num2= isnull(case when len(replace(a.id,'0',''))<3 
then (select sum(Num2) from tb where replace(id,'0','') like replace(a.id,'0','')+'%')
 else
(select max(Num2) from tb where id=a.id)
end,0)
,Num3= isnull(case when len(replace(a.id,'0',''))<3 
then (select sum(Num3) from tb where replace(id,'0','') like replace(a.id,'0','')+'%')
 else
(select max(Num3) from tb where id=a.id)
end,0)
from ta a
--测试结果:
/*
id   name   Num1        Num2        Num3
---- ------ ----------- ----------- -----------
1000 Name1  590         0           0
1100 Name2  290         0           0
1110 Name3  100         0           0
1120 Name4  100         0           0
1200 Name5  100         0           0
1300 Name6  100         0           0
1400 Name7  100         0           0
2000 Name8  600         0           0
2100 Name9  500         0           0
2110 Name10 100         0           0
2120 Name11 100         0           0
2130 Name12 100         0           0
2140 Name13 100         0           0
2200 Name14 100         0           0(14 行受影响)
*/

Answer 18

DECLARE @a TABLE([ID] INT,[NAME] NVARCHAR(6))
INSERT @a SELECT 1000,N'Name1'
UNION ALL SELECT 1100,N'Name2'
UNION ALL SELECT 1110,N'Name3'
UNION ALL SELECT 1120,N'Name4'
UNION ALL SELECT 1200,N'Name5'
UNION ALL SELECT 1300,N'Name6'
UNION ALL SELECT 1400,N'Name7'
UNION ALL SELECT 2000,N'Name8'
UNION ALL SELECT 2100,N'Name9'
UNION ALL SELECT 2110,N'Name10'
UNION ALL SELECT 2120,N'Name11'
UNION ALL SELECT 2130,N'Name12'
UNION ALL SELECT 2140,N'Name13'
UNION ALL SELECT 2200,N'Name14'DECLARE @b TABLE([ID] INT,[Num1] INT,[Num2] INT,[Num3] INT)
INSERT @b SELECT 1110,100,NULL,NULL
UNION ALL SELECT 1120,100,NULL,NULL
UNION ALL SELECT 1110,90 ,NULL,NULL
UNION ALL SELECT 1200,100,NULL,NULL
UNION ALL SELECT 1300,100,NULL,NULL
UNION ALL SELECT 1400,100,NULL,NULL
UNION ALL SELECT 2110,100,NULL,NULL
UNION ALL SELECT 2110,100,NULL,NULL
UNION ALL SELECT 2120,100,NULL,NULL
UNION ALL SELECT 2130,100,NULL,NULL
UNION ALL SELECT 2140,100,NULL,NULL
UNION ALL SELECT 2200,100,NULL,NULL
/************/
/*Test  Data*/
/***fcuandy**/
/*2009-02-11*/
/************/
;WITH fc AS
(
SELECT *,REVERSE(STUFF(REVERSE(ID),1,PATINDEX('%[^0]%',REVERSE(ID))-1,'')) pat FROM @a
)
SELECT c.id,ISNULL(d.num1,m1) num1,ISNULL(d.num2,m2) num2,ISNULL(d.num3,m3) num3 FROM
(
SELECT * FROM fc
) c
OUTER APPLY
(
SELECT SUM(ISNULL(Num1,0)) NUM1, SUM(ISNULL(Num2,0)) NUM2, SUM(ISNULL(Num3,0)) NUM3
FROM fc a
INNER JOIN @b b
ON a.id = b.id
WHERE pat LIKE c.pat + '_%'
) d
LEFT JOIN
(
SELECT x.*,ISNULL(y.m1,0) m1,ISNULL(y.m2,0) m2,ISNULL(y.m3,0) m3 FROM fc x
INNER JOIN 
(SELECT id,MAX(num1) m1,MAX(num2) m2,MAX(num3) M3 FROM @b GROUP BY id) y
ON x.id=y.id
WHERE NOT EXISTS(
SELECT 1 FROM fc 
WHERE pat LIKE x.pat + '_%'
)
) e
ON e.id = c.id/*
1000 590 0 0
1100 290 0 0
1110 100 0 0
1120 100 0 0
1200 100 0 0
1300 100 0 0
1400 100 0 0
2000 600 0 0
2100 500 0 0
2110 100 0 0
2120 100 0 0
2130 100 0 0
2140 100 0 0
2200 100 0 0
*/

Answer 19

我不用REPLACE的原因是： 可能有1010这样的数据。

Answer 20

我不用with的原因是：可能是sql server 2000的版本。对于含混不清的题目，我一般懒于用过于严格的验证。。

Answer 21

我的写法用不用with都一样，把pat的生成代进去就是了，所以没有什么不适用于2000的问题，只是懒得敲，就打了with.
out apply改为join把 与c的连接提出来就可以了。

Answer 22

ljhcy99快点来非技术区。。我要结贴了标题是你们三个的名字。。

Answer 23

呵呵气势fcuandy是对的1110应该是取和。。是190.。。

Answer 24

ljhcy99 快点来非技术区。。我要结贴了标题是你们三个的名字。。