问题一共涉及到三张表出工表
CREATE TABLE OutBill(
sendID VARCHAR2(20) PRIMARY KEY,
corpID NUMBER NOT NULL,//与客户ID关联
engineer varchar2(10),//该单子工程师
sendMode VARCHAR2(10), //出工类型
actualFee NUMBER(10,2), //费用
);
客户表
CREATE TABLE corpInfo
(
corpID NUMBER PRIMARY KEY,
REVENUE NUMBER, //与字典表的dicContentID关联
)
字典表
CREATE TABLE dicContent(
dicContentID NUMBER NOT NULL,//
typeID NUMBER NOT NULL, //父片区ID
content VARCHAR2(100), //此片区中文名
extendData VARCHAR2(100),//此片区的标识
FOREIGN KEY (typeID) REFERENCES dicType(typeID)
);其中 出工表与客户表通过corpID关联,而客户表的revenue与字典表的dicContentID关联现在需要查出以下内容
片区名称,出工类型,该片区总费用,统计开始时间,统计结束时间 还需要再查记录总数我写到这儿就不会了,也不知道对不对
from OutBill s,CorpInfo c,DicContent d,
(select engineer as engineer,sum(actualFee) as allfee from OutBill where group by engineer) sc
where s.corpID=c.corpID
and c.revenue=d.dicContentID
and sc.engineer=s.engineer
and d.typeID=14
求各位不吝赐教
CREATE TABLE OutBill(
sendID VARCHAR2(20) PRIMARY KEY,
corpID NUMBER NOT NULL,//与客户ID关联
engineer varchar2(10),//该单子工程师
sendMode VARCHAR2(10), //出工类型
actualFee NUMBER(10,2), //费用
);
客户表
CREATE TABLE corpInfo
(
corpID NUMBER PRIMARY KEY,
REVENUE NUMBER, //与字典表的dicContentID关联
)
字典表
CREATE TABLE dicContent(
dicContentID NUMBER NOT NULL,//
typeID NUMBER NOT NULL, //父片区ID
content VARCHAR2(100), //此片区中文名
extendData VARCHAR2(100),//此片区的标识
FOREIGN KEY (typeID) REFERENCES dicType(typeID)
);其中 出工表与客户表通过corpID关联,而客户表的revenue与字典表的dicContentID关联现在需要查出以下内容
片区名称,出工类型,该片区总费用,统计开始时间,统计结束时间 还需要再查记录总数我写到这儿就不会了,也不知道对不对
from OutBill s,CorpInfo c,DicContent d,
(select engineer as engineer,sum(actualFee) as allfee from OutBill where group by engineer) sc
where s.corpID=c.corpID
and c.revenue=d.dicContentID
and sc.engineer=s.engineer
and d.typeID=14
求各位不吝赐教
大致的统计如下,按照片区名称,出工类型统计出该片区总费用和记录总数
select d.content,s.sendMode,sum(actualFee),count(1) as cnt
from OutBill s,CorpInfo c,DicContent d,
where s.corpID=c.corpID
and c.revenue=d.dicContentID
and d.typeID=14
你的表间关系没有说明啊,例如一个片区只有一个客户?还是如何
CREATE TABLE OutBill(
sendID VARCHAR2(20) PRIMARY KEY,
corpID NUMBER NOT NULL,//与客户ID关联
engineer varchar2(10),//该单子工程师
sendMode VARCHAR2(10), //出工类型
actualFee NUMBER(10,2), //费用
sendDate DATE //出工时间
);一个片区有很多客户,针对每个客户可能产生N个出工单,需求就是根据出工时间起止对出工的费用进行统计的
from OutBill a,corpInfo b,dicContent c
where a.corpID=b.corpID and b.REVENUE=c.dicContentID
group by c.content,a.sendMode