create table cybr_u_orderdish “销售明细表”
(
ch_dishno varchar(10),菜品
num_num varchar(20),数量
num_price varchar(10),价格
dt_operdate varchar(10),销售时间
)
insert into cybr_u_orderdish ‘7777‘,’1‘,'23',‘2010-07-12’
insert into cybr_u_orderdish ‘7776‘,’1‘,'23',‘2010-07-13’create table cybr_u_emp_time “考勤表”
(
vch_empno varchar(20),员工编号
dt_onduty varchar(10),上班时间
dt_offduty varchar(10),下班时间
)
insert into cybr_u_emp_time '5555','2010-07-12 12:30','2010-07-12 20:30'
insert into cybr_u_emp_time '5555','2010-07-13 12:30','2010-07-12 20:30'
insert into cybr_u_emp_time '6666','2010-07-12 21:00','2010-07-12 23:30'
insert into cybr_u_emp_time '6666','2010-07-13 21:00','2010-07-12 23:30'
/*
要得到这样的报表:5555员工上班时间内销售的菜品明细
vch_empno ch_dishno num_num num_price dt_operdate
5555 7777 1 23 2010-07-12
5555 7776 1 23 2010-07-13*/
(
ch_dishno varchar(10),菜品
num_num varchar(20),数量
num_price varchar(10),价格
dt_operdate varchar(10),销售时间
)
insert into cybr_u_orderdish ‘7777‘,’1‘,'23',‘2010-07-12’
insert into cybr_u_orderdish ‘7776‘,’1‘,'23',‘2010-07-13’create table cybr_u_emp_time “考勤表”
(
vch_empno varchar(20),员工编号
dt_onduty varchar(10),上班时间
dt_offduty varchar(10),下班时间
)
insert into cybr_u_emp_time '5555','2010-07-12 12:30','2010-07-12 20:30'
insert into cybr_u_emp_time '5555','2010-07-13 12:30','2010-07-12 20:30'
insert into cybr_u_emp_time '6666','2010-07-12 21:00','2010-07-12 23:30'
insert into cybr_u_emp_time '6666','2010-07-13 21:00','2010-07-12 23:30'
/*
要得到这样的报表:5555员工上班时间内销售的菜品明细
vch_empno ch_dishno num_num num_price dt_operdate
5555 7777 1 23 2010-07-12
5555 7776 1 23 2010-07-13*/
from cybr_u_emp_time
where vch_empno = '5555';select * from cybr_u_orderdish where dt_operdate between @dt_onduty and @dt_offduty
(
cybr_ID char(36),
ch_dishno varchar(10),
num_num varchar(20),
num_price varchar(10),
dt_operdate datetime,
)
insert into cybr_u_orderdish VALUES(newid(),'7777','1','23','2010-7-12 14:30')
insert into cybr_u_orderdish VALUES(newid(),'7776','1','23','2010-7-13 17:45')create table cybr_u_emp_time
(
emp_ID char(36),
vch_empno varchar(20),
dt_onduty datetime,
dt_offduty datetime,
)insert into cybr_u_emp_time VALUES(newid(),'5555','2010-07-12 12:30','2010-07-12 20:30')
insert into cybr_u_emp_time VALUES(newid(),'5555','2010-07-13 12:30','2010-07-12 20:30')
insert into cybr_u_emp_time VALUES(newid(),'6666','2010-07-12 21:00','2010-07-12 23:30')
insert into cybr_u_emp_time VALUES(newid(),'6666','2010-07-13 21:00','2010-07-12 23:30')
from cybr_u_emp_time,cybr_u_orderdish
where dt_operdate>=dt_onduty and dt_operdate<=dt_offduty
顺便说一句,这贴发的也太不负责了,语法、字段就不说了,你上班时间是:
'2010-07-13 12:30',下班时间是:'2010-07-12 20:30',搞我奇怪了一下
from cybr_u_emp_time,cybr_u_orderdish
where dt_operdate>=dt_onduty and dt_operdate<=dt_offduty
正解