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

yumanqing 2008-03-22 10:23:07
创建测试环境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] ON



INSERT [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] ON



INSERT [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
...全文
178 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
yumanqing 2008-03-22
  • 打赏
  • 举报
 回复
喔,明白了,谢谢各位的指点,
Limpire 2008-03-22
  • 打赏
  • 举报
 回复
因为不能肯定你的iCode和SupCode是否1对1.

当然，如果非1对1，这样汇总数据就有点怪，应该是对iCode进行分组汇总。
dawugui 2008-03-22
  • 打赏
  • 举报
 回复 
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 行）
*/
Limpire 2008-03-22
  • 打赏
  • 举报
 回复
再sum反而可能会错，或许又重复了
Limpire 2008-03-22
  • 打赏
  • 举报
 回复
--用MAX不对吧
我已经将结果转换为1对1，用min或max都对，因为只有一条对应数据，
Limpire 2008-03-22
  • 打赏
  • 举报
 回复
三个表之间不是1对1关系，对三个表的项进行分类汇总，当然会导致重复数据。
yumanqing 2008-03-22
  • 打赏
  • 举报
 回复
喔,那这里为什么用:


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
bulesky_xshp 2008-03-22
  • 打赏
  • 举报
 回复
原因：以主表连接从表
主表一条记录
从表多条记录
用left outer join
连接出来结果是

1*多条记录

要先用从表Group by 成一条数据，再进行连接
yumanqing 2008-03-22
  • 打赏
  • 举报
 回复
昨夜小楼
的结果是对的,可我还是有些不是明白原因,能解释一下吗?谢谢
-晴天 2008-03-22
  • 打赏
  • 举报
 回复
在向两个表 left 连接时,icode为6的记录与两个从表连接分别各产生了一条记录.
Limpire 2008-03-22
  • 打赏
  • 举报
 回复 
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

*/
yujiang930 2008-03-22
  • 打赏
  • 举报
 回复
具体问题具体分析，一个问题一个问题排除。我是做零售POS软件的，我要回自己部门了，我们部门上不了网！你自己检查一下吧
yujiang930 2008-03-22
  • 打赏
  • 举报
 回复
先一个表一个表的查询出来看一下结果，主表里应该有2条记录才对
yumanqing 2008-03-22
  • 打赏
  • 举报
 回复
主表是合同,两个子表分别是计划付款和实际付款,但我从数据库中看应该是这个结果呀
SupCode HTCount HTMontyCount PlanMoney YFMoney
00023 1 100
00022 1 123 14.76 5

00022 的HTCount怎么是2呢?只有一条记录呀
yujiang930 2008-03-22
  • 打赏
  • 举报
 回复
很明显你的00022供应商在 detail 中有2条记录，在detail中在把条件加一下
parss 2008-03-22
  • 打赏
  • 举报
 回复
楼主看一下你的字段里有没有小数点，还有字段类型是否符合，是否有空值等等，我看你写的ＳＱＬ语句是没有错，就是不知道你的字段里的值有没有问题？
yujiang930 2008-03-22
  • 打赏
  • 举报
 回复
呵呵，是不是想查供应商付款金额和应付金额啊。
确实不知道你想要得到什么结果
你的语句也没什么太大的问题，改成以下方法试一下，看是不是你想要的结果。

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

-晴天 2008-03-22
  • 打赏
  • 举报
 回复
结果好像没错吧.统计值不就是两条吗?
yumanqing 2008-03-22
  • 打赏
  • 举报
 回复
现在查询出来的结果为：是错误的
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语句的书写那里有问题,会出来那么一个错误的结果
loworth 2008-03-22
  • 打赏
  • 举报
 回复
你想要什么样的正确结果
sql中where、having、group by、order by执行和书写顺序
当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是： 1.执行where xx对全表数据做筛选，返回第1个结果集。 2.针对第1个结果集使用group by分组，返回第2个结果集。 3.针对第2个...
数据库SQL语句中 where,group by,having,order by的执行顺序
其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行： from>where>group by>having>select>order by from：需要从...
SQL查询语句分步详解——SELECT...FROM...WHERE...GROUP BY...
SQL查询语句分步详解——SELECT...FROM...WHERE...GROUP BY...
8. SQL中Order by和Group by子句的使用简介
如同Where子句一样，在SQL语言中order by和group by是两个出现频率极高的子句，本篇中我将着重介绍下他们的使用方法，因为他们是极其实用的。
SQL学习(二)——SELECT COUNT GROUP BY HAVING
https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh 2.列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。 SELECT name FROM world WHERE continent = 'Europe' and gdp/po...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章