%%%%%一个查询问题%%%%%

[nik_Amis]

1-----------------------
SELECT a.UID,ItemID,a.Quantity,Date1,Date2,b.EligibleBatch,b.Quantity AS EligibleQuantity ,b.EligibleDate,a.Remark,a.BatchID

FROM TB_ProductPlan a LEFT JOIN TB_ProductEligible b ON a.UID=b.PlanUID

2-----------------------
SELECT planuid,sum(quantity) as finishedamount
FROM TB_ProductEligible
group by planuid


如何合并两个查询？把第二段作为一个查询的字段放到1里面。要求一条SQL，不能用sp,fun的方式

[中国风]

如何合并两个查询？

----------
显示效果为...
列数不等时用

如：
select col1,col2 from t--
select col1,0 from t2--没有的列用0

---
横行显示:

select col1,col2,t2_col1=0 from t1
union all
select 0,0,col1 from t

[nik_Amis]

1,A,2
2,A,3
3,A,1
4,B,6
4,B,1
6,C,8

-->

1,A,2,6
2,A,3,6
3,A,1,6
4,B,6,7
5,C,8,8

要这么重效果

[中国风]

以上是一个结果集，第二个是显示?

group by .. rollup就行了,第一列用2000记录数,05以上版本row_number

[nik_Amis]

第一个是1的查询结果，第二个实际上是对第一个的Quantity得分组求和，然后作为一个字段放在第一个查询里面

-_-!
不知道表达清楚了没有

[nik_Amis]

SELECT a.UID,ItemID,a.Quantity,Date1,Date2,b.EligibleBatch,b.Quantity AS EligibleQuantity ,b.EligibleDate,a.Remark,a.BatchID

,QtySum IN (
SELECT planuid,sum(quantity) as finishedamount
FROM TB_ProductEligible
group by planuid
)
FROM TB_ProductPlan a LEFT JOIN TB_ProductEligible b ON a.UID=b.PlanUID

就是这种感觉，但是我不知道怎么写

[中国风]

--> --> (Roy)生成測試數據
 
set nocount on;
declare @T table([Col1] int,[Col2] nvarchar(1),[Col3] int)
Insert @T
select 1,N'A',2 union all
select 2,N'A',3 union all
select 3,N'A',1 union all
select 4,N'B',6 union all
select 4,N'B',1 union all
select 6,N'C',8
 
Select
	[Col1]=(select count(distinct [Col1]) from @T where [Col1]<=t1.[Col1]),
	[Col2],
	[Col3]=(select sum([Col3]) from @T where [Col2]=t1.[Col2] )
from 
	@T t1
group by [Col1],[Col2]

Col1        Col2 Col3
----------- ---- -----------
1           A    6
2           A    6
3           A    6
4           B    7
5           C    8

[中国风]

就是这种感觉，但是我不知道怎么写?

可列出一些数据和得到效果

[nik_Amis]

：|

[nik_Amis]

SELECT a.UID,ItemID,a.Quantity,Date1,Date2,b.EligibleBatch,b.Quantity AS EligibleQuantity ,b.EligibleDate,a.Remark,a.BatchID

,QtySum IN (
SELECT planuid,sum(quantity) as finishedamount
FROM TB_ProductEligible
group by planuid
)
FROM TB_ProductPlan a LEFT JOIN TB_ProductEligible b ON a.UID=b.PlanUID

实际上就是按照PlanUID分组对Quantity求和，结果作为一个字段放到第一段SQL里面，就是那个红色的字段

[中国风]

QtySum IN (
--显示两列这样？

用2000生成临时表identity--05用row_number--自增

通过自增连接

[中国风]

[Quote=引用 7 楼 roy_88 的回复:]
HTML code就是这种感觉，但是我不知道怎么写?


可列出一些测试数据和要得到效果
[/Quote]

[nik_Amis]

兄弟，你好像没理解我的意思，没那么复杂的
就是一个查询，然后Quantity字段是根据PlanUID字段的分组查询求和

QA-----------------------
SELECT a.UID,a.Quantity,b.EligibleBatch

FROM TB_ProductPlan a LEFT JOIN TB_ProductEligible b ON a.UID=b.PlanUID

QB-----------------------
SELECT planuid,sum(quantity) as finishedamount
FROM TB_ProductEligible
group by planuid

QA的查询结果类似于
PlanUID,Quantity
A,2
A,3
A,1
B,6
B,1
C,8

我要的查询结果（在QA查询的基础上增加求和）：
PlanUID,Quantity,Sum(Quantity)
A,2,6
A,3,6
A,1,6
B,6,7
C,8,8

[hery2002]

SELECT a.UID,a.Quantity,b.EligibleBatch,
( select sum(quantity)  FROM TB_ProductEligible where planuid = a.UID ) as finishedamount  
FROM TB_ProductPlan a 

[hery2002]

--去掉b表的那一列,
SELECT a.UID,a.Quantity,--b.EligibleBatch,
( select sum(quantity)  FROM TB_ProductEligible where planuid = a.UID ) as finishedamount  
FROM TB_ProductPlan a 

[中国风]

6楼已列出方法:

--> --> (Roy)生成測試數據
 
set nocount on;
declare @Qa table([PlanUID] nvarchar(1),[Quantity] int)
Insert @Qa
select N'A',2 union all
select N'A',3 union all
select N'A',1 union all
select N'B',6 union all
select N'B',1 union all
select N'C',8
 
2000:
Select *,(select sum([Quantity]) from @Qa where [PlanUID]=a.[PlanUID])sumQuantity
 from @Qa a

2005:
Select *,sum([Quantity])over(partition by [PlanUID])sumQuantity
 from @Qa a


PlanUID Quantity    sumQuantity
------- ----------- -----------
A       2           6
A       3           6
A       1           6
B       6           7
B       1           7
C       8           8

[nik_Amis]

SELECT a.UID,ItemID,a.Quantity,c.finishedamount ,Date1,Date2,b.EligibleBatch,b.Quantity AS EligibleQuantity ,b.EligibleDate,a.Remark,a.BatchID

FROM TB_ProductPlan a LEFT JOIN TB_ProductEligible b ON a.UID=b.PlanUID LEFT JOIN


(SELECT planuid,sum(quantity) as finishedamount
FROM TB_ProductEligible
group by planuid
) AS C ON a.uid=c.planuid

我已经写出来了，谢谢两位了，接铁

[nik_Amis]

就是个语法不是很熟悉，呵呵

[hery2002]

[Quote=引用 15 楼 roy_88 的回复:]
6楼已列出方法:
[/Quote]
风,貌似不是同一个表,
TB_ProductPlan
TB_ProductEligible
子查询应该就可以实现了..