见过史上最长的SQL语句（惊）

这是同事写的一个进销存的SQL语句，一条语句这么长，大吓人了（数据少的话速度还很快，佩服）。

 

 

SELECT Id, GName, Model1, Model2, unit, AverageCost, (Select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.inoutflag=1 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and StockBill.Type in (0,2,4,6,8)) AS BeginPeriodXinPinInQty, (Select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.inoutflag=2 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and StockBill.Type in (1,3,5,7,9,15)) AS BeginPeriodXinPinOutQty, (Select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.inoutflag=0 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and Type=10 and StockBillItems.Receiver='0') AS BeginPeriodDiaoBoInQty, (Select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.inoutflag=0 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and Type=10 and StockBillItems.Supplier='0') AS BeginPeriodDiaoBoOutQty, (Select sum(qty) from RGStockBill inner join RGStockBillItems on RGStockBill.Id=RGStockBillItems.BillId where RGStockBill.status=3 and RGStockbill.InOutflag=1 and RGStockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and ProcessFlag=13 and RGStockBillItems.FixMeans='1') AS BeginPeriodHuanXinInQty, (BeginPeriodXinPinInQty+BeginPeriodHuanXinInQty-BeginPeriodXinPinOutQty) AS BeginPeriodQty, (Select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.InOutFlag=1 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and StockBill.Type in (0,2,4,6,8)) AS BeginPeriodInAmount, (Select sum(Cost) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.InOutFlag=2 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and StockBill.Type in (1,3,5,7,9,15)) AS BeginPeriodOutCost, (Select sum(Cost) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.InOutFlag=0 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and Type=10 and StockBillItems.Supplier='0') AS BeginPeriodDiaoBoInCost, (Select sum(Cost) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.InOutFlag=0 and StockBillItems.Gid=Goods.id and AuditTime<'2009-05-03 14:45:13' and Type=10 and StockBillItems.Supplier='0') AS BeginPeriodDiaoBoOutCost, ((BeginPeriodInAmount+BeginPeriodDiaoBoInCost)-(BeginPeriodOutCost+BeginPeriodDiaoBoOutCost)) AS BeginPeriodBlanceAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=2 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQCGJinHuoQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=2 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQCGJinHuoAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=4 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQXSTuiHuoQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=4 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQXSTuiHuoAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=6 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQQiTaInQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=6 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQQiTaInAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=10 and Stockbill.inoutflag=0 and StockBillItems.Receiver='0' and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQDiaoBoInQty, BQDiaoBoInQty*AverageCost AS BQDiaoBoInAmount, (select sum(Qty) from RGStockBill inner join RGStockBillItems on RGStockBill.Id=RGStockBillItems.BillId where RGStockBill.status=3 and RGStockbill.ProcessFlag=13 and RGStockBillItems.Gid=Goods.id and RGStockBillItems.FixMeans='1' and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQHuanXinQty, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=8 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQBaoYiQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=8 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQBaoYiAmount, (BQCGJinHuoQty+BQXSTuiHuoQty+BQXSTuiHuoQty+BQQiTaInQty+BQHuanXinQty+BQBaoYiQty+BQDiaoBoInQty) AS BQInQty, (BQCGJinHuoAmount+BQXSTuiHuoAmount+BQXSTuiHuoAmount+BQQiTaInAmount+BQBaoYiAmount+BQDiaoBoInAmount) AS BQInAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=3 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQCGTuiHuoQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=3 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQCGTuiHuoAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=5 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQXiaoShouOutQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=5 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQXiaoShouOutAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=7 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQQiTaOutQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=7 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQQiTaOutAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=10 and Stockbill.inoutflag=0 and StockBillItems.Supplier='0' and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQDiaoBoOutQty, BQDiaoBoOutQty*AverageCost AS BQDiaoBoOutAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=15 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQBaoXiuQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=15 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQBaoXiuAmount, (select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=9 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQBaoSunQty, (select sum(StockBillItems.Amount) from StockBill inner join StockBillItems on StockBill.Id=StockBillItems.BillId where StockBill.status=3 and Stockbill.Type=9 and StockBillItems.Gid=Goods.id and (AuditTime>='2009-05-03 14:45:13' and AuditTime<='2009-05-03 14:45:13')) AS BQBaoSunAmount, (BQCGTuiHuoQty+BQXiaoShouOutQty+BQQiTaOutQty+BQBaoXiuQty+BQBaoSunQty) AS BQOutQty, (BQCGTuiHuoAmount+BQXiaoShouOutAmount+BQQiTaOutAmount+BQBaoXiuAmount+BQBaoSunAmount) AS BQOutAmount, (BQInQty-BQOutQty) AS BQBalanceQty, (BQInAmount-BQOutAmount) AS BQBalanceAmount
FROM Goods
WHERE 1=1;