select A.goods_code,A.goods_name,A.buy_amount,A.buy_money, B.sell_amount, B.sell_money,A.etc from 表A A ,表B B where (A.goods_code=B.goods_code and A.goods_name=B.goods_name) and (A.cur_date='2005-04-01' or B.v_date='2005-04-01')
试试 Select A.goods_code,A.goods_name,buy_amount,buy_money,sell_amount,sell_money,A.etc from A Inner Join B On A.goods_code=B.goods_code Where cur_date=条件 Or v_date=条件
select T.goods_code,T.goods_name,T.buy_amount,T.buy_money,M.sell_amount from 表a T left join 表b M on T.goods_code=M.goods_code and T.goods_name=M.goods_name
我的意思是说,有一张购进表,一张销售表(购进销售的产品名、代码均相同) 比如: between '2005-04-01' and '2005-04-25' 这两张表是肯定有变化的,有购进,同时也有销售。有没有办法对以上两张表做一个统计,如:betwwen '2005-04-01' and '2005-04-25'时间内汇总数据,做成如下结构: goods_code,goods_name,buy_amount,buy_money,sell_amount,sell_money求出这段时间内,某货品购进多少,同时销售多少。 因为这个查询不能用SQL查询分析器或其他数据库客户端工具完成查询,只能在一个已设定好的软件界面中添加查询语句以及查询日期段,但是查询日期段只能对一个表的时间段进行查询,因此,我写不出来到底应该怎么写这个语句。
----我的理解是看今日卖的价格. Select A.goods_code ,A.goods_name ,B.buy_amount ,B.buy_money ,B.sell_amount ,B.sell_money ,A.etc from A Inner Join B On A.goods_code=B.goods_code Where datediff(d,B.v_date,'查询时间变量')=0 ----条件自己更改
select goods_code,goods_name,sum(isnull(buy_amount,0)) as amount,sum(isnull(buy_money,0)) as [money] from( select goods_code,goods_name,buy_amount,0-buy_money as buy_money ,cur_date union all select goods_code,goods_name,0-sell_amount,sell_money,v_date )t group by goods_code,goods_name where cur_date between '2005-04-01' and '2005-04-25'
可以这样: 写一个存储过程, create procedure Pro_name @input datetimeas create table #t ( goods_code datatype, goods_name datatype, buy_amount datatype, buy_money datatype, sell_amount datatype, sell_money datatype ) insert into #t1 --insert the select result into the #t1select goods_code, goods_name, buy_amount, buy_money, sell_amount=0, sell_money=0 cur_date from A where cur_date between ** and **UNION ALL select goods_code, goods_name, buy_amount=0, buy_money=0, sell_amount, sell_money, v_date from B where v_date between ** and ** select * --this result should be u want get from #t1 drop table #t1go
Select t_b_c_client.full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name, t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_code, sum(convert(numeric(10,2),default_package_amount)) as sum_default_package_amount, sum(t_b_b_buy_detail.no_tax_money) as sum_no_tax_money, sum(t_b_b_buy_detail.tax_money) as sum_tax_money, sum(t_b_b_buy_detail.total_money) as sum_total_money from t_b_b_buy_detail,t_b_b_buy_head,t_b_c_client,t_b_c_goods where (t_b_b_buy_detail.corp_code=t_b_b_buy_head.corp_code) and (t_b_b_buy_detail.buy_no=t_b_b_buy_head.buy_no) and (t_b_b_buy_detail.goods_code=t_b_c_goods.goods_code) and (t_b_b_buy_head.client_code=t_b_c_client.client_code) and (t_b_b_buy_head.register_corp_code=t_b_c_client.register_corp_code) group by t_b_c_client.client_code,full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name, t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_code order by t_b_c_client.client_code,full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name, t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_codet_b_c_client.full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name,t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_code,sum_cur_package_amount,sum_default_package_amount,sum_no_tax_money, sum_tax_money,sum_total_money,|供货商;卷烟编号;卷烟品名;当前包装;卷烟类别;当前数量;默认数量;不含税金额;税金;含税金额; 8;750;1013;1843;2103;2330;2673;3036;3399;3758; 734;256;822;251;219;334;357;357;352;379; 70 1;1;1;1;1;1;1;1;1;1; 1 cur_date
可以这样: 写一个存储过程, create procedure Pro_name @input datetime /*在这里设置你的输入参数, 参数个数和数据类型都要和应用程序中的保持一致*/as create table #t ( goods_code datatype, goods_name datatype, buy_amount datatype, buy_money datatype, sell_amount datatype, sell_money datatype ) insert into #t1 --insert the select result into the #t1select goods_code, goods_name, buy_amount, buy_money, sell_amount=0, sell_money=0 cur_date from A where cur_date between ** and ** /*这里就是针对你的查询了, 你可以在这里查询一个时间段的数据, 比喻说:cur_date between '2005-4-1' and '2005-4-5'*/UNION ALLselect goods_code, goods_name, buy_amount=0, buy_money=0, sell_amount, sell_money, v_date from B where v_date between ** and ** /*在这里可以查询另外一个时间段的数据 ,as :v_date between '2004-04-31' and '2004-05-01'*/ select * --这里得到最终的结果 from #t1 drop table #t1go /* 不知道这样说你明白了没有?把查询结果放到临时表中是为了所有的信息都集中在同一个Id下, 这是为了避免 union all 造成的查询结果中,同一个id不一定只出现一次 */
goods_code,goods_name,buy_amount,buy_money,cur_date,etc from 表A UNION goods_code,goods_name,sell_amount,sell_money,v_date,etc from 表B
你这种应用,应该合计后再连接。 select a.*,b.sell_amount,b.sell_money from (select goods_code,goods_name,sum(buy_amount) as buy_amount,sum(buy_money)/sum(1) as buy_money from 表A group by goods_code,goods_name) a inner join (select goods_code, sum(sell_amount) as sell_amount,sum(sell_money) as sell_money from 表B group by goods_code ) b on b.goods_code = a.goods_code 在子查询里加时间条件。
B.sell_amount, B.sell_money,A.etc
from 表A A ,表B B where (A.goods_code=B.goods_code and A.goods_name=B.goods_name)
and (A.cur_date='2005-04-01' or B.v_date='2005-04-01')
Select A.goods_code,A.goods_name,buy_amount,buy_money,sell_amount,sell_money,A.etc
from A Inner Join B On A.goods_code=B.goods_code
Where cur_date=条件 Or v_date=条件
from 表a T left join 表b M on T.goods_code=M.goods_code and T.goods_name=M.goods_name
比如: between '2005-04-01' and '2005-04-25'
这两张表是肯定有变化的,有购进,同时也有销售。有没有办法对以上两张表做一个统计,如:betwwen '2005-04-01' and '2005-04-25'时间内汇总数据,做成如下结构:
goods_code,goods_name,buy_amount,buy_money,sell_amount,sell_money求出这段时间内,某货品购进多少,同时销售多少。
因为这个查询不能用SQL查询分析器或其他数据库客户端工具完成查询,只能在一个已设定好的软件界面中添加查询语句以及查询日期段,但是查询日期段只能对一个表的时间段进行查询,因此,我写不出来到底应该怎么写这个语句。
Select A.goods_code
,A.goods_name
,B.buy_amount
,B.buy_money
,B.sell_amount
,B.sell_money
,A.etc
from A
Inner Join B
On A.goods_code=B.goods_code
Where datediff(d,B.v_date,'查询时间变量')=0 ----条件自己更改
from(
select goods_code,goods_name,buy_amount,0-buy_money as buy_money ,cur_date
union all
select goods_code,goods_name,0-sell_amount,sell_money,v_date
)t
group by goods_code,goods_name
where cur_date between '2005-04-01' and '2005-04-25'
写一个存储过程,
create procedure Pro_name @input datetimeas
create table #t (
goods_code datatype,
goods_name datatype,
buy_amount datatype,
buy_money datatype,
sell_amount datatype,
sell_money datatype
)
insert into #t1 --insert the select result into the #t1select goods_code,
goods_name,
buy_amount,
buy_money,
sell_amount=0,
sell_money=0
cur_date
from A
where cur_date between ** and **UNION ALL
select goods_code,
goods_name,
buy_amount=0,
buy_money=0,
sell_amount,
sell_money,
v_date
from B
where v_date between ** and **
select * --this result should be u want get
from #t1
drop table #t1go
t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_code,
sum(convert(numeric(10,2),default_package_amount)) as sum_default_package_amount,
sum(t_b_b_buy_detail.no_tax_money) as sum_no_tax_money,
sum(t_b_b_buy_detail.tax_money) as sum_tax_money,
sum(t_b_b_buy_detail.total_money) as sum_total_money
from
t_b_b_buy_detail,t_b_b_buy_head,t_b_c_client,t_b_c_goods
where
(t_b_b_buy_detail.corp_code=t_b_b_buy_head.corp_code) and
(t_b_b_buy_detail.buy_no=t_b_b_buy_head.buy_no) and
(t_b_b_buy_detail.goods_code=t_b_c_goods.goods_code) and
(t_b_b_buy_head.client_code=t_b_c_client.client_code) and
(t_b_b_buy_head.register_corp_code=t_b_c_client.register_corp_code)
group by t_b_c_client.client_code,full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name,
t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_code
order by t_b_c_client.client_code,full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name,
t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_codet_b_c_client.full_name,t_b_b_buy_detail.goods_code,t_b_c_goods.goods_name,t_b_b_buy_detail.cur_package_name,t_b_c_goods.class_code,sum_cur_package_amount,sum_default_package_amount,sum_no_tax_money, sum_tax_money,sum_total_money,|供货商;卷烟编号;卷烟品名;当前包装;卷烟类别;当前数量;默认数量;不含税金额;税金;含税金额; 8;750;1013;1843;2103;2330;2673;3036;3399;3758; 734;256;822;251;219;334;357;357;352;379; 70 1;1;1;1;1;1;1;1;1;1; 1 cur_date
70 行高
1;1;1;1;1;1;1;1;1;1; 是否显示该列
cur_date 你欲查询的表的时间段字段名因为他这里只提供了一个时间段字段名,因此上述两个表有两个不同的时间字段,因此。
另外,如果是存储过程的话,我怎么写这个时间段的字段名啊?不好意思,我很少接触存储过程
写一个存储过程,
create procedure Pro_name @input datetime /*在这里设置你的输入参数,
参数个数和数据类型都要和应用程序中的保持一致*/as
create table #t (
goods_code datatype,
goods_name datatype,
buy_amount datatype,
buy_money datatype,
sell_amount datatype,
sell_money datatype
)
insert into #t1 --insert the select result into the #t1select goods_code,
goods_name,
buy_amount,
buy_money,
sell_amount=0,
sell_money=0
cur_date
from A
where cur_date between ** and ** /*这里就是针对你的查询了,
你可以在这里查询一个时间段的数据,
比喻说:cur_date between '2005-4-1' and '2005-4-5'*/UNION ALLselect goods_code,
goods_name,
buy_amount=0,
buy_money=0,
sell_amount,
sell_money,
v_date
from B
where v_date between ** and ** /*在这里可以查询另外一个时间段的数据
,as :v_date between '2004-04-31' and '2004-05-01'*/
select * --这里得到最终的结果
from #t1
drop table #t1go
/*
不知道这样说你明白了没有?把查询结果放到临时表中是为了所有的信息都集中在同一个Id下,
这是为了避免 union all 造成的查询结果中,同一个id不一定只出现一次
*/
goods_code,goods_name,buy_amount,buy_money,cur_date,etc
from 表A
UNION
goods_code,goods_name,sell_amount,sell_money,v_date,etc
from 表B
select a.*,b.sell_amount,b.sell_money from (select goods_code,goods_name,sum(buy_amount) as buy_amount,sum(buy_money)/sum(1) as buy_money from 表A group by goods_code,goods_name) a inner join (select goods_code, sum(sell_amount) as sell_amount,sum(sell_money) as sell_money from 表B group by goods_code ) b on b.goods_code = a.goods_code 在子查询里加时间条件。