急求一句汇总SQL查询语句，100分欢送

2012-04-09 09:53:31 296 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL查询语句的书写顺序编写符合语法的SQL语句。避免因顺序错误导致的逻辑问题。更清晰地组织复杂查询。阅读详情



有如下表结构：

ID       Name    ParentID     Qty

100      A       NULL         NULL

10001    A1      100          100

10002    A2      100          200

1000101  A11     10001        250

200      B       NULL         NULL

20001    B1      200          300

300      C       NULL         NULL

其中ID是主键ID，ParentID是对应的父ID，就是一个树型结构的表，统计数量，用SQL语句达到以下效果：



ID       Name    Qty 

100	 A	 550

10001	 A1	 100

1000101  A11     250

10002	 A2	 200

200	 B	 300

20001	 B1	 300

300	 C	 0

请赐教。。。。。。。。

...全文

经典SQL查询语句大全 select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)'密码 ',数据库名.dbo.表名)exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 'exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 阅读详情

lybjust

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

中国风 2012-04-10

打赏
举报

[Quote=引用 13 楼的回复:]

引用 12 楼的回复:

引用 11 楼的回复:

引用 10 楼的回复:

+1
引用 3 楼的回复:
SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarch……
[/Quote]

可以，生成的就是一个结果集，当多次调用时把CTE结果集生成一个临时表反复调用

lybjust 2012-04-10

打赏
举报

[Quote=引用 12 楼的回复:]

引用 11 楼的回复:

引用 10 楼的回复:

+1
引用 3 楼的回复:
SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(3),[ParentID] ……
[/Quote]
那请问大板，With查询的结果能否作为子集再直接使用呢

比如：
select * from
(
with as
( select 1 from ....)
)

中国风 2012-04-10

打赏
举报

[Quote=引用 11 楼的回复:]

引用 10 楼的回复:

+1
引用 3 楼的回复:
SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)
I……
[/Quote]

那样直接递归就行了





--> --> (Roy)生成測試數據

 

if not object_id('Tempdb..#T') is null

    drop table #T

Go

Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)

Insert #T

select 100,N'A',null,null union all

select 10001,N'A1',100,100 union all

select 10002,N'A2',100,200 union all

select 1000101,N'A11',10001,250 union all

select 200,N'B',null,null union all

select 20001,N'B1',200,300 union all

select 300,N'C',null,null

Go

;with t1

as

(

Select *,grp=ID,grpName=Name from #T  

union all

select a.*,grp=b.grp,grpName=b.grpName from #T as a inner join t1 as b on b.ID=a.ParentID

)

select ID=grp,Name=grpName, isnull(SUM(Qty),0)   as Qty

from t1 

group by grp,grpName



/*

100	A	550

10001	A1	350

1000101	A11	250

10002	A2	200

200	B	300

20001	B1	300

300	C	0

*/

lybjust 2012-04-10

打赏
举报

[Quote=引用 10 楼的回复:]

+1
引用 3 楼的回复:
SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)
Insert #T
select ……
[/Quote]

这里10001 A1 100 应该是350，自身的100+子项10001的250 大版主少加了250的数

让你望见影子的墙 2012-04-10

打赏
举报

借大版的测试数据一用



Go

Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)

Insert #T

select 100,N'A',null,null union all

select 10001,N'A1',100,100 union all

select 10002,N'A2',100,200 union all

select 1000101,N'A11',10001,250 union all

select 200,N'B',null,null union all

select 20001,N'B1',200,300 union all

select 300,N'C',null,null

Go



select id,name,ParentID,qty=(select sum(isnull(qty,0)) from #t where name like t.name+'%')

from #t t

lybjust 2012-04-10

打赏
举报

[Quote=引用 3 楼的回复:]

SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)
Insert #T
select 100,N'A',null,null u……
[/Quote]

这里10001 A1 100 应该是350，自身的100+子项10001的250？
求深入指教？

guguda2008 2012-04-09

打赏
举报

大版你大半夜的不去哄老婆孩子抢啥分啊。。。

guguda2008 2012-04-09

打赏
举报

USE TEMPDB

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

ID VARCHAR(10)

,NAME VARCHAR(10)

,PARENTID VARCHAR(10)

,QTY INT

)

INSERT INTO TB

SELECT '100','A',NULL,NULL UNION ALL

SELECT '10001','A1','100','100' UNION ALL

SELECT '10002','A2','100','200' UNION ALL

SELECT '1000101','A11','10001','250' UNION ALL

SELECT '200','B',NULL,NULL UNION ALL

SELECT '20001','B1','200','300' UNION ALL

SELECT '300','C',NULL,NULL

GO

;WITH MU AS (

SELECT *,';'+CONVERT(VARCHAR(MAX),ID)+';' AS PATH FROM TB WHERE PARENTID IS NULL

UNION ALL

SELECT T1.*,T2.PATH+T1.ID+';'

FROM TB T1

INNER JOIN MU T2 ON T1.PARENTID=T2.ID

)

SELECT ID,NAME,ISNULL((SELECT SUM(QTY) FROM MU WHERE PATH LIKE '%;'+TB.ID+';%'),0) AS QTY

FROM TB

/*

100	A	550

10001	A1	350

10002	A2	200

1000101	A11	250

200	B	300

20001	B1	300

300	C	0

*/

开着拖拉机泡妞 2012-04-09

打赏
举报

[Quote=引用 3 楼的回复:]

SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)
Insert #T
select 100,N'A',null,null u……
[/Quote]

拜膜大版了，居然卡在一个地方了，学习学习

开着拖拉机泡妞 2012-04-09

打赏
举报



--> 测试数据：[tbl]

if object_id('[tbl]') is not null drop table [tbl]

create table [tbl]([ID] int,[Name] varchar(3),[ParentID] int,[Qty] int)

insert [tbl]

select 100,'A',null,null union all

select 10001,'A1',100,100 union all

select 10002,'A2',100,200 union all

select 1000101,'A11',10001,250 union all

select 200,'B',null,null union all

select 20001,'B1',200,300 union all

select 300,'C',null,null





select * from(

select left([ID],3) as [ID],LEFT([Name],1) [Name],SUM(isnull([Qty],0)) [Qty] 

from tbl group by left([ID],3),LEFT([Name],1)

union all

select [ID],[Name],SUM(isnull([Qty],0)) from tbl where LEN([ID])>3

group by [ID],[Name])a order by 2



/*

ID	Name	Qty

100	A	550

10001	A1	100

1000101	A11	250

10002	A2	200

200	B	300

20001	B1	300

300	C	0

*/



你给的测试数据用递归不怎么好实现

中国风 2012-04-09

打赏
举报

--> --> (Roy)生成測試數據

 

if not object_id('Tempdb..#T') is null

	drop table #T

Go

Create table #T([ID] int,[Name] nvarchar(3),[ParentID] int,[Qty] int)

Insert #T

select 100,N'A',null,null union all

select 10001,N'A1',100,100 union all

select 10002,N'A2',100,200 union all

select 1000101,N'A11',10001,250 union all

select 200,N'B',null,null union all

select 20001,N'B1',200,300 union all

select 300,N'C',null,null

Go

;with t1

as

(

Select *,grp=ID,grpName=Name from #T  where ParentID is null

union all

select a.*,grp=b.grp,grpName=b.grpName from #T as a inner join t1 as b on b.ID=a.ParentID

)

select ID=rtrim(grp),Name=grpName, isnull(SUM(Qty),0)   as Qty

from t1 

group by grp,grpName

union 

select rtrim(ID) as ID,Name,Qty from #T where ParentID is not null order by ID



/*

ID	Name	Qty

100	A	550

10001	A1	100

1000101	A11	250

10002	A2	200

200	B	300

20001	B1	300

300	C	0

*/

jstoic 2012-04-09

打赏
举报

存储过程倒是写出来了，一句话的还是不行啊。

jstoic 2012-04-09

打赏
举报

没写出来，帮顶，留记号

急求一句汇总SQL查询语句，100分欢送

相关推荐