select b.FSupplyID,sum(FConsignAmount) as money from ICStockBillEntry a left join ICStockBill b on a.FInterID=b.FInterID
where b.FSupplyID='31890'
group by b.FSupplyID order by moneyselect FSupplyID,count(*) as money from ICStockBill where FSupplyID='31890' group by FSupplyID order by FSupplyID
--------------
31890 127899.0031890 3
---------------------
2个查询语句,返回的结果分别如上,能否整合成一条
31890 127899.00 3
因为一个是订单表+订单明细表,取出某个客户消费金额
一个是直接取出客户购买的订单次数,使用UNION不好直接查出。。谢谢了。
where b.FSupplyID='31890'
group by b.FSupplyID order by moneyselect FSupplyID,count(*) as money from ICStockBill where FSupplyID='31890' group by FSupplyID order by FSupplyID
--------------
31890 127899.0031890 3
---------------------
2个查询语句,返回的结果分别如上,能否整合成一条
31890 127899.00 3
因为一个是订单表+订单明细表,取出某个客户消费金额
一个是直接取出客户购买的订单次数,使用UNION不好直接查出。。谢谢了。
select b.FSupplyID,a.money,b.money1 from
(
select b.FSupplyID,sum(FConsignAmount) as money from ICStockBillEntry a left join ICStockBill b on a.FInterID=b.FInterID
where b.FSupplyID='31890'
group by b.FSupplyID
)a
inner join
(
select FSupplyID,count(*) as money1 from ICStockBill where FSupplyID='31890'
group by FSupplyID
)b
on a.FInterID=b.FInterID
with cte
as
(
select b.FSupplyID,sum(FConsignAmount) as money
from ICStockBillEntry a left join ICStockBill b on a.FInterID=b.FInterID
where b.FSupplyID='31890'
group by b.FSupplyID order by money
),
cte1
as
(
select FSupplyID,count(*) as money
from ICStockBill
where FSupplyID='31890'
group by FSupplyID order by FSupplyID
)
select t1.FSupplyID,t1.money,t2.money
from
cte t1 inner join cte1 t2 on t1.FSupplyID=t2.FSupplyID