以下是我的一段代码根据指定的日期值检索,结果对。如果没有指定日期值,则按照每个日期进行分组统计各项内容的和。
请各位帮我看看有什么问题:
SELECT distinct t_mtlist.model_cd1 as 製番,t_mtlist.model_cd1 as 機種1,'-' as 横1,
t_mtlist.model_cd2 as 機種2,'-' as 横2,t_mtlist.lot as ロット,
t_purchase.del_date2 as 納期,t_delivery.del_date1 as 納入日,
(select count(distinct purchase_no)
from t_delivery ,t_mtlist where t_mtlist.order_no= t_delivery.purchase_no and
t_delivery.del_date1='2010-7-1' group by t_delivery.del_date1) ----納入日
as 納入枚数 ,
(select count(distinct purchase_no)
from t_delivery ,t_mtlist where t_mtlist.order_no= t_delivery.purchase_no )
as 累計枚数 ,
(select count(purchase_no)
from t_purchase ,t_mtlist where t_mtlist.order_no= t_purchase.purchase_no and
t_purchase .del_date2 ='2010-8-1' group by t_purchase .del_date2
)
as 伝票枚数,
(select (case when (select count(purchase_no)
from t_purchase ,t_mtlist where t_purchase.purchase_no=t_mtlist.order_no and
t_purchase .del_date2 ='2010-8-1' group by t_purchase .del_date2
) = 0 then
'0'
else
round((select count(distinct purchase_no)
from t_delivery ,t_mtlist where t_mtlist.order_no= t_delivery.purchase_no and
t_delivery.del_date1='2010-7-1' group by t_delivery.del_date1
) / (select count(purchase_no)
from t_purchase ,t_mtlist where t_mtlist.order_no= t_purchase.purchase_no and
t_purchase .del_date2 ='2010-8-1' group by t_purchase .del_date2) ,2)
end ))
as 枚数納入率,
(select SUM (del_qty ) from t_delivery ,t_mtlist
where t_delivery .purchase_no =t_mtlist .order_no and t_delivery.del_date1='2010-7-1'
group by t_delivery.del_date1)
as 納入数,
(select SUM (del_qty ) from t_delivery ,t_mtlist
where t_delivery .purchase_no =t_mtlist .order_no )
as 累計納入数,
(select SUM (order_use_qty ) from t_mtlist where
model_cd1 ='12345' and model_cd2 ='1234567890' and lot ='123')
as 発注数,
(select (case when (select SUM (order_use_qty ) from t_mtlist where
model_cd1 ='12345' and model_cd2 ='1234567890' and lot ='123') = 0 then
'0'
else
ROUND ((select SUM (del_qty ) from t_delivery ,t_mtlist
where t_delivery .purchase_no =t_mtlist .order_no
and t_delivery.del_date1='2010-7-1' group by t_delivery.del_date1) /
(select SUM (order_use_qty ) from t_mtlist where
model_cd1 ='12345' and model_cd2 ='1234567890' and lot ='123'),2)
end )) as 数量納入率
from t_mtlist
inner join t_delivery
on t_delivery.purchase_no=t_mtlist.order_no
inner join t_purchase
on t_purchase.purchase_no = t_mtlist .order_no where t_mtlist.model_cd1 ='12345' and t_mtlist.model_cd2='1234567890' and t_mtlist.lot ='123'
and t_delivery.del_date1='2010-7-1' and t_purchase.del_date2='2010-8-1'其中,最下面一行是检索条件可输入可不输入:t_delivery.del_date1='2010-7-1' and t_purchase.del_date2='2010-8-1'
,上面的数字是必须输入的。
现在想知道没有这个条件t_delivery.del_date1='2010-7-1' and t_purchase.del_date2='2010-8-1'的SQL该怎么写?
实现效果:
製番 納期 納入日 納入枚数 累計枚数 枚数納入率
1 2010/8/1 2010/7/1 2 4 2/4
2 2010/8/1 2010/7/2 1 4 1/4
请各位帮我看看有什么问题:
SELECT distinct t_mtlist.model_cd1 as 製番,t_mtlist.model_cd1 as 機種1,'-' as 横1,
t_mtlist.model_cd2 as 機種2,'-' as 横2,t_mtlist.lot as ロット,
t_purchase.del_date2 as 納期,t_delivery.del_date1 as 納入日,
(select count(distinct purchase_no)
from t_delivery ,t_mtlist where t_mtlist.order_no= t_delivery.purchase_no and
t_delivery.del_date1='2010-7-1' group by t_delivery.del_date1) ----納入日
as 納入枚数 ,
(select count(distinct purchase_no)
from t_delivery ,t_mtlist where t_mtlist.order_no= t_delivery.purchase_no )
as 累計枚数 ,
(select count(purchase_no)
from t_purchase ,t_mtlist where t_mtlist.order_no= t_purchase.purchase_no and
t_purchase .del_date2 ='2010-8-1' group by t_purchase .del_date2
)
as 伝票枚数,
(select (case when (select count(purchase_no)
from t_purchase ,t_mtlist where t_purchase.purchase_no=t_mtlist.order_no and
t_purchase .del_date2 ='2010-8-1' group by t_purchase .del_date2
) = 0 then
'0'
else
round((select count(distinct purchase_no)
from t_delivery ,t_mtlist where t_mtlist.order_no= t_delivery.purchase_no and
t_delivery.del_date1='2010-7-1' group by t_delivery.del_date1
) / (select count(purchase_no)
from t_purchase ,t_mtlist where t_mtlist.order_no= t_purchase.purchase_no and
t_purchase .del_date2 ='2010-8-1' group by t_purchase .del_date2) ,2)
end ))
as 枚数納入率,
(select SUM (del_qty ) from t_delivery ,t_mtlist
where t_delivery .purchase_no =t_mtlist .order_no and t_delivery.del_date1='2010-7-1'
group by t_delivery.del_date1)
as 納入数,
(select SUM (del_qty ) from t_delivery ,t_mtlist
where t_delivery .purchase_no =t_mtlist .order_no )
as 累計納入数,
(select SUM (order_use_qty ) from t_mtlist where
model_cd1 ='12345' and model_cd2 ='1234567890' and lot ='123')
as 発注数,
(select (case when (select SUM (order_use_qty ) from t_mtlist where
model_cd1 ='12345' and model_cd2 ='1234567890' and lot ='123') = 0 then
'0'
else
ROUND ((select SUM (del_qty ) from t_delivery ,t_mtlist
where t_delivery .purchase_no =t_mtlist .order_no
and t_delivery.del_date1='2010-7-1' group by t_delivery.del_date1) /
(select SUM (order_use_qty ) from t_mtlist where
model_cd1 ='12345' and model_cd2 ='1234567890' and lot ='123'),2)
end )) as 数量納入率
from t_mtlist
inner join t_delivery
on t_delivery.purchase_no=t_mtlist.order_no
inner join t_purchase
on t_purchase.purchase_no = t_mtlist .order_no where t_mtlist.model_cd1 ='12345' and t_mtlist.model_cd2='1234567890' and t_mtlist.lot ='123'
and t_delivery.del_date1='2010-7-1' and t_purchase.del_date2='2010-8-1'其中,最下面一行是检索条件可输入可不输入:t_delivery.del_date1='2010-7-1' and t_purchase.del_date2='2010-8-1'
,上面的数字是必须输入的。
现在想知道没有这个条件t_delivery.del_date1='2010-7-1' and t_purchase.del_date2='2010-8-1'的SQL该怎么写?
实现效果:
製番 納期 納入日 納入枚数 累計枚数 枚数納入率
1 2010/8/1 2010/7/1 2 4 2/4
2 2010/8/1 2010/7/2 1 4 1/4
试试 between ....and ........
也就是说没有指定日期,不可能能查到值的。
否则按日期查当然也可以改为没有指定日期,查询所有值
大概就是这么个思路。