[来看]复杂sql查询

Question

UPMaterialInfo
+------------+------------+------------+
|字段名称    |数据类型    |备注        |
+------------+------------+------------+
|ID          |自动编号    |            |
+------------+------------+------------+
|UnitID      |数字        |零件编号    |
+------------+------------+------------+
|TypeID      |数字        |类型编号    |
+------------+------------+------------+
|Counts      |数字        |库存数量    |
+------------+------------+------------+
MaterialType
+------------+------------+------------+
|字段名称    |数据类型    |备注        |
+------------+------------+------------+
|ID          |自动编号    |            |
+------------+------------+------------+
|Type        |文本        |零件规格    |
+------------+------------+------------+
MaterialUnit
+------------+------------+------------+
|字段名称    |数据类型    |备注        |
+------------+------------+------------+
|ID          |自动编号    |            |
+------------+------------+------------+
|Unit        |文本        |零件        |
+------------+------------+------------+

Answer 1

MaterialInfo
ID UnitID TypeID Counts Re AddTime
9 11 4 1 TEST 2004-4-12 9:08:33
10 11 4 1 TEST 2004-4-12 9:15:22
11 11 4 1 TEST 2004-4-12 9:15:25
12 11 4 -1 TEST 2004-4-12 9:21:36
13 11 4 -1 TEST 2004-4-12 9:21:47
14 11 4 -1 TEST 2004-4-12 9:22:16
15 26 0 1 TEST 2004-4-12 9:23:15
16 61 4 0 TEST 2004-4-12 14:54:31
17 61 3 0 22222 2004-4-12 14:54:40
18 61 4 1 TEST 2004-4-12 14:54:52
19 61 3 1 22222 2004-4-12 14:55:16
----------------------------------------------------------------
MaterialType
ID Type
3 2KF-6
4 3KF-6
5 2B-31
6 4B-15
7 2BA-6
8 3BA-9
9 4BA-18
10 SZ-1
11 SZ-2
12 3B-33
13 IS50-32-125
14 IS50-32-160
15 300RXS-90
-----------------------------------------------------
MaterialUnit
ID Unit
4 泵体
5 后盖
6 支架
7 叶轮
8 口环
9 泵轴
10 轴套甲
11 轴套乙
12 轴承挡圈
13 压盖甲
14 压盖乙
15 管接头
16 轴承座
17 静环座
18 园螺母
19 销钉
20 泵盖
21 悬架
22 轴套
16 40RXL-8-12
---------------------------------------------------
要的结果+------------+------------+------------+------------+------------+
|型号规格    |泵体        |后盖        |支架        |叶轮        |
+------------+------------+------------+------------+------------+
|X1          |0           |0           |1           |0           |
+------------+------------+------------+------------+------------+
|X2          |9           |0           |0           |1           |
+------------+------------+------------+------------+------------+
|X3          |1           |1           |1           |1           |
+------------+------------+------------+------------+------------+
|.           |.           |.           |.           |.           |
+------------+------------+------------+------------+------------+
|.           |.           |.           |.           |.           |
+------------+------------+------------+------------+------------+
|XN          |0           |1           |1           |1           |
+------------+------------+------------+------------+------------+
______________________________________________________________

Answer 2

即把
SELECT [MaterialType].[Type], sum([MaterialInfo].[Counts]) AS totalcounts
FROM MaterialType, MaterialInfo, MaterialUnit
WHERE [MaterialType].[ID]=[MaterialInfo].[TypeID] And [MaterialUnit].[ID]=[MaterialInfo].[UnitID] And [MaterialUnit].[unit]='泵体'
GROUP BY [MaterialType].[Type], [MaterialUnit].[Unit]
ORDER BY [MaterialType].[Type];
SELECT [MaterialType].[Type], sum([MaterialInfo].[Counts]) AS totalcounts
FROM MaterialType, MaterialInfo, MaterialUnit
WHERE [MaterialType].[ID]=[MaterialInfo].[TypeID] And [MaterialUnit].[ID]=[MaterialInfo].[UnitID] And [MaterialUnit].[unit]='后盖'
GROUP BY [MaterialType].[Type], [MaterialUnit].[Unit]
ORDER BY [MaterialType].[Type];SELECT [MaterialType].[Type], sum([MaterialInfo].[Counts]) AS totalcounts
FROM MaterialType, MaterialInfo, MaterialUnit
WHERE [MaterialType].[ID]=[MaterialInfo].[TypeID] And [MaterialUnit].[ID]=[MaterialInfo].[UnitID] And [MaterialUnit].[unit]='支架’
GROUP BY [MaterialType].[Type], [MaterialUnit].[Unit]
ORDER BY [MaterialType].[Type];
SELECT [MaterialType].[Type], sum([MaterialInfo].[Counts]) AS totalcounts
FROM MaterialType, MaterialInfo, MaterialUnit
WHERE [MaterialType].[ID]=[MaterialInfo].[TypeID] And [MaterialUnit].[ID]=[MaterialInfo].[UnitID] And [MaterialUnit].[unit]='叶轮’
GROUP BY [MaterialType].[Type], [MaterialUnit].[Unit]
ORDER BY [MaterialType].[Type];
这4个查询横向加起来。

Answer 3

UPMaterialInfo
+------------+------------+------------+
|字段名称　　|数据类型　　|备注　　　　|
+------------+------------+------------+
|ID　　　　　|自动编号　　|　　　　　　|
+------------+------------+------------+
|UnitID　　　|数字　　　　|零件编号　　|
+------------+------------+------------+
|TypeID　　　|数字　　　　|类型编号　　|
+------------+------------+------------+
|Counts　　　|数字　　　　|库存数量　　|
+------------+------------+------------+
MaterialType
+------------+------------+------------+
|字段名称　　|数据类型　　|备注　　　　|
+------------+------------+------------+
|ID　　　　　|自动编号　　|　　　　　　|
+------------+------------+------------+
|Type　　　　|文本　　　　|零件规格　　|
+------------+------------+------------+
MaterialUnit
+------------+------------+------------+
|字段名称　　|数据类型　　|备注　　　　|
+------------+------------+------------+
|ID　　　　　|自动编号　　|　　　　　　|
+------------+------------+------------+
|Unit　　　　|文本　　　　|零件　　　　|
+------------+------------+------------+

Answer 4

--统计
declare @s varchar(8000)
set @s=''
select @s=@s+',['+a.Unit+']=sum(case UnitID when '
+cast(a.ID as varchar)+' then Counts else 0 end)'
from MaterialUnit a join(
select UnitID from MaterialInfo group by UnitID
)b on a.ID=b.UnitIDexec('select a.Type'+@s+'
from MaterialType a
join MaterialInfo b on a.ID=b.TypeID
group by a.Type
')

Answer 5

--测试--测试数据
create table MaterialInfo(ID int,UnitID int,TypeID int,Counts int,Re varchar(10),AddTime datetime)
insert MaterialInfo
select 9,11,4,1,'TEST','2004-4-12 9:08:33'
union all select 10,11,4,1,'TEST','2004-4-12 9:15:22'
union all select 11,11,4,1,'TEST','2004-4-12 9:15:25'
union all select 12,11,4,-1,'TEST','2004-4-12 9:21:36'
union all select 13,11,4,-1,'TEST','2004-4-12 9:21:47'
union all select 14,11,4,-1,'TEST','2004-4-12 9:22:16'
union all select 15,26,0,1,'TEST','2004-4-12 9:23:15'
union all select 16,61,4,0,'TEST','2004-4-12 14:54:31'
union all select 17,61,3,0,'22222','2004-4-12 14:54:40'
union all select 18,61,4,1,'TEST','2004-4-12 14:54:52'
union all select 19,61,3,1,'22222','2004-4-12 14:55:16'create table MaterialType(ID int,Type varchar(20))
insert MaterialType
select 3,'2KF-6'
union all select 4,'3KF-6'
union all select 5,'2B-31'
union all select 6,'4B-15'
union all select 7,'2BA-6'
union all select 8,'3BA-9'
union all select 9,'4BA-18'
union all select 10,'SZ-1'
union all select 11,'SZ-2'
union all select 12,'3B-33'
union all select 13,'IS50-32-125'
union all select 14,'IS50-32-160'
union all select 15,'300RXS-90'create table MaterialUnit(ID int,Unit varchar(20))
insert MaterialUnit
select 0,'泵体'
union all select 1,'后盖'
union all select 2,'支架'
union all select 3,'叶轮'
union all select 4,'口环'
union all select 5,'泵轴'
union all select 6,'轴套甲'
union all select 7,'轴套乙'
union all select 8,'轴承挡圈'
union all select 9,'压盖甲'
union all select 10,'压盖乙'
union all select 11,'管接头'
union all select 12,'轴承座'
union all select 13,'静环座'
union all select 14,'园螺母'
union all select 15,'销钉'
union all select 26,'泵盖'
union all select 27,'悬架'
union all select 28,'轴套'
union all select 61,'40RXL-8-12'
go--统计
declare @s varchar(8000)
set @s=''
select @s=@s+',['+a.Unit+']=sum(case UnitID when '
+cast(a.ID as varchar)+' then Counts else 0 end)'
from MaterialUnit a 
--/*如果不管MaterialInfo中是否有数据,都要显示所有的Unit,则取消这个join条件
join(
select UnitID from MaterialInfo group by UnitID
)b on a.ID=b.UnitID
--*/
exec('select a.Type'+@s+'
from MaterialType a
join MaterialInfo b on a.ID=b.TypeID
group by a.Type
')
go--删除测试
drop table MaterialInfo,MaterialType,MaterialUnit/*--测试结果
Type                 管接头         泵盖          40RXL-8-12  
-------------------- ----------- ----------- ----------- 
2KF-6                0           0           1
3KF-6                0           0           1--*/

Answer 6

--上面的方法是动态的,即根据表中的数据来生成结果--如果查询是固定的,只加起来就行,就用:SELECT t.[Type]
,[泵体]=sum(case u.[unit] when '泵体' then i.[Counts] else 0 end)
,[后盖]=sum(case u.[unit] when '后盖' then i.[Counts] else 0 end)
,[支架]=sum(case u.[unit] when '支架' then i.[Counts] else 0 end)
,[叶轮]=sum(case u.[unit] when '叶轮' then i.[Counts] else 0 end)
FROM MaterialType t
join MaterialInfo i on t.[ID]=i.[TypeID]
join MaterialUnit u on u.[ID]=i.[UnitID]
where u.[unit] in('泵体','后盖','支架','叶轮')
GROUP BY t.[Type]
ORDER BY t.[Type]