SQL难题求助^^^^^^^^^^^^^^^^^^^^^^^^^^^^
（所影响的行数为 4 行）
A 表
GUID Server_name portal_id Type_id Module_id msg_type
------------------------------------------------------------
3ed823fc-99d0-4616-a509-3656d12bc2c8 98 159 3909 9481 1
75f53e53-d981-46ca-a1ca-fc5ad964e4df 123 0 1 12147 1
8e031372-d89e-48cf-9778-8a7f53d13931 44 91 4471 12096 0
926bb6d6-a385-476e-b7c8-401dfa8dcfaa 11 0 1 17758 0
A7205118-EB52-4545-880E-1F2482E712CE 短信服务测试 0 3889 1188 1
c3dba60d-d89c-482d-a74b-0259fe32f446 业务咨询提醒 92 4686 12813 0
f57f2142-ddd1-4c9b-bbe8-0d61ad266238 1111 0 1 1190 1
FFDDAB7A-1548-42F7-8F6F-EF001B2A8918 海关法规 0 399 1147 1
B 表
MODULE_ID USE_ID
------------------------------------ -----------
A7205118-EB52-4545-880E-1F2482E712CE 100
A7205118-EB52-4545-880E-1F2482E712CE 125
A7205118-EB52-4545-880E-1F2482E712CE 13275
A7205118-EB52-4545-880E-1F2482E712CE 26415
A7205118-EB52-4545-880E-1F2482E712CE 33800
f57f2142-ddd1-4c9b-bbe8-0d61ad266238 2
f57f2142-ddd1-4c9b-bbe8-0d61ad266238 33800
FFDDAB7A-1548-42F7-8F6F-EF001B2A8918 2
FFDDAB7A-1548-42F7-8F6F-EF001B2A8918 31
FFDDAB7A-1548-42F7-8F6F-EF001B2A8918 180
FFDDAB7A-1548-42F7-8F6F-EF001B2A8918 216
C 表
GUID MODULE_ID Portal_id
------------------------------------ ----------- -----------
9652AFAC-DF83-45D7-8E53-1A4400D9E47E 1188 0
13CEC422-5B58-4F2D-AC7C-1C2255301387 1188 0
6A0C9123-4B77-475A-A841-1E6E7927B7CD 0 0
64D9AC24-D162-4C00-8D69-20F96AB2BACF 1188 0
5085F879-CCCF-42B1-BBD7-24E62D5BAE2D 0 0
BC2E0C83-CE6B-46B4-93F0-2BEA730F2F0F 9049 91
3FB12427-421A-4337-8921-57B714D9D968 1188 0
7489B0C8-BE8F-4446-A878-58BD5FF5BFE5 1188 0
F383729C-9EE8-4FA3-AB72-636C84BFB11E 1188 0
3C795578-CD20-457E-8B31-764A173CD812 1188 0
B095449B-8DAB-4D51-AD78-769DD1CB3A3F 1188 0
D5E01064-C4CF-4482-9BF9-83BDE46B9DFD 1188 0
9A01AA78-61F0-456D-A025-AB632123C12E 1188 0
29198674-6EF6-4081-83E2-BCE5A57933DD 0 0
96650700-3EE1-42CF-801C-CEDE466BC28D 0 0
709B0973-3888-413E-9175-DD051697DD4F 0 0
71C076C4-D80E-4F96-A66C-DE186F4335BB 0 0
97A740E9-D950-4992-93F0-DE596606F4C4 0 0
78A71BD9-2727-4D3A-A01F-E4B112014AB2 0 0
5710DB4B-3AC6-4B0B-B666-EAB82299A86D 1188 0
2B5035FB-11AD-4FB5-8D22-F4F2A1C579F0 1188 0
84C7E96D-AB85-49CE-BC35-F79F8254E508 1188 0
038EC67D-34EF-4580-B913-FD4010931B45 1188 0
条件是当A表中的portal_id = 0 and msg_Type = 1 是统计出下面的结果
Msg_Type Server_Name C表Count B表Count
-------- ---------------------------- -----------
1 1111 0 2
1 123 0 0
1 短信服务测试 14 5
1 海关法规 0 4
这是我写的查询语句，但不查不出我想要的结果，
select Msg_Type, s_m.Server_Name as Server_Name
,count(s_i.Module_ID) as C表Count
,count(s_dm.USE_ID) as B表Count
from A s_m
left join C s_i on s_m.Module_ID = s_i.Module_ID
inner join B s_dm on s_m.GUID = s_dm.MODULE_ID
where s_m.MSG_TYPE = 1 and s_m.Portal_ID = 0 group by s_m.Server_Name,s_m.MSG_TYPE
[解决办法]
用關聯這麼寫
try
select Msg_Type, s_m.Server_Name
, IsNull(C表Count, 0) As C表Count
, IsNull(B表Count, 0) As B表Count
from A s_m
left join (Select Module_ID, Count(Module_ID) As C表Count From C Group By Module_ID) s_i on s_m.Module_ID = s_i.Module_ID
left join (Select MODULE_ID, Count(USE_ID) As B表Count From B Group By MODULE_ID) s_dm on s_m.GUID = s_dm.MODULE_ID
where s_m.MSG_TYPE = 1 and s_m.Portal_ID = 0
[解决办法]
cefriend(青草) ( ) 信誉：100 2007-08-09 09:28:52 得分: 0
不对呢，你没测
-----------------------------------------------
你把測試語句寫好,我來測,
最多加個條件
select Msg_Type ,Server_Name ,
C表Count= (select count(*) from C where MODULE_ID=A.MODULE_ID and Portal_id=0),
B表Count= (select count(*) from B where MODULE_ID=A.GUID)
where portal_id = 0 and msg_Type = 1
