group by 和 count的一个SQL的书写问题,在线等...

Question

创建测试环境SQL:
if exists (select * from sysobjects where id = OBJECT_ID('[HT_MainBasic]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [HT_MainBasic]CREATE TABLE [HT_MainBasic] (
[iCode] [int]  NOT NULL,
[iName] [char]  (200) NULL,
[CreatePersonCode] [char]  (10) NULL,
[CreatePersonName] [char]  (50) NULL,
[CreateDate] [datetime]  NULL,
[DeptCode] [char]  (50) NULL,
[CusCode] [char]  (50) NULL,
[SupCode] [char]  (50) NULL,
[PersonName] [char]  (50) NULL,
[HTClass] [char]  (50) NULL,
[SFFlag] [char]  (10) NULL,
[iMoney] [float]  NULL,
[BeginDate] [datetime]  NULL,
[EndDate] [datetime]  NULL,
[ConfimDate] [datetime]  NULL,
[NextDate] [datetime]  NULL,
[DutyPerson] [char]  (50) NULL,
[ContinueFlag] [int]  NULL,
[PerformFlag] [char]  (50) NULL,
[Area] [float]  NULL,
[Content] [char]  (200) NULL,
[CheckFlag] [int]  NULL,
[Memo] [char]  (100) NULL)ALTER TABLE [HT_MainBasic] WITH NOCHECK ADD  CONSTRAINT [PK_HT_MainBasic] PRIMARY KEY  NONCLUSTERED ( [iCode] )INSERT [HT_MainBasic] ([iCode],[iName],[CreatePersonCode],[CreatePersonName],[CreateDate],[DeptCode],[CusCode],[HTClass],[SFFlag],[iMoney],[BeginDate],[EndDate],[ConfimDate],[NextDate],[DutyPerson],[ContinueFlag],[PerformFlag],[Area],[CheckFlag]) VALUES ( 1,'合同名称','001','yumanqing','2008-3-16 0:00:00','0601','HuB00017','01','SK',555,'2008-3-16 10:54:33','2008-3-16 10:54:33','2008-3-16 10:54:33','1900-1-1 0:00:00','060101',0,'未执行',0,1)
INSERT [HT_MainBasic] ([iCode],[iName],[CreatePersonCode],[CreatePersonName],[CreateDate],[DeptCode],[CusCode],[HTClass],[SFFlag],[iMoney],[BeginDate],[EndDate],[ConfimDate],[NextDate],[DutyPerson],[ContinueFlag],[PerformFlag],[Area],[Content]) VALUES ( 2,'合同名称','001','yumanqing','2008-3-16 0:00:00','0601','HuB00017','01','SK',34,'2008-3-16 10:57:15','2008-3-16 10:57:15','2008-3-16 10:57:15','2008-3-22 0:00:00','060101',1,'未执行',0,'描述')
INSERT [HT_MainBasic] ([iCode],[iName],[CreatePersonCode],[CreatePersonName],[CreateDate],[DeptCode],[CusCode],[HTClass],[SFFlag],[iMoney],[BeginDate],[EndDate],[ConfimDate],[NextDate],[ContinueFlag],[PerformFlag],[Area],[Content]) VALUES ( 3,'呵呵','001','yumanqing','2008-3-16 0:00:00','0602','HuB00017','01','SK',4545,'2008-3-16 0:00:00','2008-3-16 0:00:00','2008-3-16 11:01:15','1900-1-1 0:00:00',0,'未执行',0,'反对反对反对')
INSERT [HT_MainBasic] ([iCode],[iName],[CreatePersonCode],[CreatePersonName],[CreateDate],[DeptCode],[CusCode],[HTClass],[SFFlag],[iMoney],[BeginDate],[EndDate],[ConfimDate],[NextDate],[ContinueFlag],[PerformFlag],[Area],[Content]) VALUES ( 4,'rerrere','001','yumanqing','2008-3-16 0:00:00','0605','HuB00016','01','SK',56565,'2008-3-16 0:00:00','2008-3-16 0:00:00','2008-3-16 11:05:51','1900-1-1 0:00:00',0,'未执行',0,'ferrererer')
INSERT [HT_MainBasic] ([iCode],[iName],[CreatePersonCode],[CreatePersonName],[CreateDate],[DeptCode],[SupCode],[HTClass],[SFFlag],[iMoney],[BeginDate],[EndDate],[ConfimDate],[NextDate],[DutyPerson],[ContinueFlag],[PerformFlag],[Area]) VALUES ( 5,'测试采购合同','001','yumanqing','2008-3-21 0:00:00','0601','00023','01','FK',100,'2008-3-21 21:16:31','2009-3-21 21:16:31','2008-3-21 21:16:31','2008-3-21 0:00:00','060101',1,'执行中',0)
INSERT [HT_MainBasic] ([iCode],[iName],[CreatePersonCode],[CreatePersonName],[CreateDate],[DeptCode],[SupCode],[HTClass],[SFFlag],[iMoney],[BeginDate],[EndDate],[ConfimDate],[NextDate],[DutyPerson],[ContinueFlag],[PerformFlag],[Area]) VALUES ( 6,'aaaa','001','yumanqing','2008-3-21 0:00:00','0602','00022','01','FK',123,'2008-3-21 21:18:57','2008-3-21 21:18:57','2008-3-21 21:18:57','1900-1-1 0:00:00','010301',0,'执行中',0)
if exists (select * from sysobjects where id = OBJECT_ID('[HT_SettlementPlan]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [HT_SettlementPlan]CREATE TABLE [HT_SettlementPlan] (
[AutoNo] [int]  IDENTITY (1, 1)  NOT NULL,
[CreateDate] [char]  (50) NULL,
[CreatePerson] [char]  (50) NULL,
[HTCode] [int]  NULL,
[PlanDate] [datetime]  NULL,
[JSRate] [int]  NULL,
[jsMoney] [float]  NULL,
[jsType] [char]  (50) NULL,
[jsProperty] [char]  (50) NULL,
[Memo] [char]  (100) NULL)ALTER TABLE [HT_SettlementPlan] WITH NOCHECK ADD  CONSTRAINT [PK_HT_SettlementPlan] PRIMARY KEY  NONCLUSTERED ( [AutoNo] )SET IDENTITY_INSERT [HT_SettlementPlan] ONINSERT [HT_SettlementPlan] ([AutoNo],[CreateDate],[CreatePerson],[HTCode],[PlanDate],[JSRate],[jsMoney],[jsType],[Memo]) VALUES ( 2,'0001-1-1 0:00:00','yumanqing',1,'2008-3-18 10:24:17',2,4440,'6566','65656556')
INSERT [HT_SettlementPlan] ([AutoNo],[CreateDate],[CreatePerson],[HTCode],[PlanDate],[JSRate],[jsMoney],[jsType],[Memo]) VALUES ( 4,'0001-1-1 0:00:00','yumanqing',1,'2008-3-14 11:17:47',7,38.85,'type','memo')
INSERT [HT_SettlementPlan] ([AutoNo],[CreateDate],[CreatePerson],[HTCode],[PlanDate],[JSRate],[jsMoney]) VALUES ( 6,'2008-3-21','yumanqing',6,'2008-3-21 21:19:50',10,11.07)
INSERT [HT_SettlementPlan] ([AutoNo],[CreateDate],[CreatePerson],[HTCode],[PlanDate],[JSRate],[jsMoney]) VALUES ( 7,'2008-3-21','yumanqing',6,'2008-3-22 23:16:46',3,3.69)SET IDENTITY_INSERT [HT_SettlementPlan] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[HT_SettlementDetails]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [HT_SettlementDetails]CREATE TABLE [HT_SettlementDetails] (
[AutoNo] [int]  IDENTITY (1, 1)  NOT NULL,
[CreateDate] [char]  (50) NULL,
[CreatePerson] [char]  (50) NULL,
[HTCode] [int]  NULL,
[jsDate] [datetime]  NULL,
[jsMoney] [float]  NULL,
[jsType] [char]  (50) NULL,
[VouchType] [char]  (50) NULL,
[VouchCode] [char]  (50) NULL,
[CheckPerson] [char]  (10) NULL,
[CheckDate] [datetime]  NULL,
[Memo] [char]  (100) NULL)ALTER TABLE [HT_SettlementDetails] WITH NOCHECK ADD  CONSTRAINT [PK_HT_SettlementDetails] PRIMARY KEY  NONCLUSTERED ( [AutoNo] )SET IDENTITY_INSERT [HT_SettlementDetails] ONINSERT [HT_SettlementDetails] ([AutoNo],[CreateDate],[CreatePerson],[HTCode],[jsDate],[jsMoney],[jsType]) VALUES ( 1,'0001-1-1 0:00:00','yumanqing',1,'2008-3-18 17:28:59',5454,'fangshi')
INSERT [HT_SettlementDetails] ([AutoNo],[CreateDate],[CreatePerson],[HTCode],[jsDate],[jsMoney]) VALUES ( 3,'2008-3-21','yumanqing',6,'2008-3-21 21:38:56',5)SET IDENTITY_INSERT [HT_SettlementDetails] OFF这三个表的是标准的主从表关系,HT_MainBasic表的iCode是主键,其他两个表的HTCode是外键=================问题========================
我想要的SQL语句 SELECT ht.SupCode,count(ht.SupCode) as HTCount,
 sum(ht.iMoney) as HTMontyCount,sum(HT_SettlementPlan.jsMoney) as PlanMoney,
 sum(HT_SettlementDetails.jsMoney) as YFMoney FROM HT_MainBasic ht
 LEFT JOIN HT_SettlementPlan ON ht.iCode=HT_SettlementPlan.HTCode 
 LEFT JOIN HT_SettlementDetails ON ht.iCode=HT_SettlementDetails.HTCode
 
 WHERE ht.SFFlag='FK' 
 GROUP BY ht.SupCode
       可是统计结果是错误的？怎么也找不出原因,请指点....TKS

Answer 1

现在查询出来的结果为：是错误的
SupCode HTCount HTMontyCount PlanMoney YFMoney
00023   1       100           
00022   2       246          14.76     10而正确的结果应该是:
SupCode HTCount HTMontyCount PlanMoney YFMoney
00023   1       100           
00022   1       123          14.76     5不知SQL语句的书写那里有问题,会出来那么一个错误的结果

Answer 2

呵呵，是不是想查供应商付款金额和应付金额啊。
确实不知道你想要得到什么结果
你的语句也没什么太大的问题，改成以下方法试一下，看是不是你想要的结果。 SELECT ht.SupCode,count(distinct ht.SupCode) as HTCount,
 sum(isnull(ht.iMoney,0)) as HTMontyCount,sum(isnull(HT_SettlementPlan.jsMoney,0)) as PlanMoney,
 sum(isnull(HT_SettlementDetails.jsMoney,0)) as YFMoney FROM HT_MainBasic ht
 LEFT JOIN HT_SettlementPlan ON ht.iCode=HT_SettlementPlan.HTCode 
 LEFT JOIN HT_SettlementDetails ON ht.iCode=HT_SettlementDetails.HTCode
 
 WHERE ht.SFFlag='FK' 
 GROUP BY ht.SupCode

Answer 3

很明显你的00022供应商在 detail 中有2条记录，在detail中在把条件加一下

Answer 4

主表是合同,两个子表分别是计划付款和实际付款,但我从数据库中看应该是这个结果呀
SupCode HTCount HTMontyCount PlanMoney YFMoney 
00023   1       100            
00022   1       123          14.76     5 00022 的HTCount怎么是2呢?只有一条记录呀

Answer 5

具体问题具体分析，一个问题一个问题排除。我是做零售POS软件的，我要回自己部门了，我们部门上不了网！你自己检查一下吧

Answer 6

SELECT
ht.SupCode,
count(ht.SupCode) as HTCount,
sum(ht.iMoney) as HTMontyCount,
max(b.PlanMoney) as PlanMoney,
max(c.YFMoney) as YFMoney
FROM
HT_MainBasic ht
LEFT JOIN
(select HTCode, sum(jsMoney) as PlanMoney from HT_SettlementPlan group by HTCode) as b
on ht.iCode = b.HTCode
LEFT JOIN
(select HTCode, sum(jsMoney) as YFMoney from HT_SettlementDetails group by HTCode) as c
ON ht.iCode=c.HTCode
WHERE ht.SFFlag='FK' 
GROUP BY ht.SupCode--> 结果应该如下：/*
SupCode  HTCount  HTMontyCount  PlanMoney  YFMoney
-------- -------- ------------- ---------- ----------------------
00022    1        123           14.76      5
00023    1        100           NULL       NULL
*/

Answer 7

在向两个表 left 连接时,icode为6的记录与两个从表连接分别各产生了一条记录.

Answer 8

原因：以主表连接从表
主表一条记录
从表多条记录
用left outer join
连接出来结果是1*多条记录要先用从表Group by 成一条数据，再进行连接

Answer 9

喔,那这里为什么用:SELECT
    ht.SupCode,
    count(ht.SupCode) as HTCount,
    sum(ht.iMoney) as HTMontyCount,
    max(b.PlanMoney) as PlanMoney,  --用MAX不对吧
    max(c.YFMoney) as YFMoney
FROM
    HT_MainBasic ht
LEFT JOIN
    (select HTCode, sum(jsMoney) as PlanMoney from HT_SettlementPlan group by HTCode) as b
    on ht.iCode = b.HTCode
LEFT JOIN
    (select HTCode, sum(jsMoney) as YFMoney from HT_SettlementDetails group by HTCode) as c
    ON ht.iCode=c.HTCode
WHERE ht.SFFlag='FK' 
GROUP BY ht.SupCode

Answer 10

--用MAX不对吧
我已经将结果转换为1对1，用min或max都对，因为只有一条对应数据，

Answer 11

再sum反而可能会错，或许又重复了

Answer 12

select ht.SupCode,
       HTCount = count(ht.SupCode),
       HTMontyCount = sum(ht.iMoney),
       PlanMoney = max(m.PlanMoney),
       YFMoney = max(n.YFMoney)
from HT_MainBasic ht
left join (select htcode , PlanMoney = sum(jsMoney) from HT_SettlementPlan group by  htcode) m on ht.icode = m.htcode
left join (select htcode , YFMoney = sum(jsMoney) from HT_SettlementDetails group by  htcode) n on ht.icode = n.htcode
WHERE ht.SFFlag='FK' 
group by ht.SupCode/*
SupCode                                            HTCount     HTMontyCount                                          PlanMoney                                             YFMoney                                               
-------------------------------------------------- ----------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
00022                                              1           123.0                                                 14.76                                                 5.0
00023                                              1           100.0                                                 NULL                                                  NULL（所影响的行数为 2 行）
*/

Answer 13

因为不能肯定你的iCode和SupCode是否1对1.当然，如果非1对1，这样汇总数据就有点怪，应该是对iCode进行分组汇总。