select * from table order by (case when 入库时间 is null then 出库时间 else 入库时间 end)
select * form tablename order by isnull(入库时间,出库时间)
select * form stock order by isnull(StoreDate,DeliverDate)
我如果这样查,能查出数据来 order by StoreDate,DeliverDate 可是,这样就不行,order by isnull(StoreDate,DeliverDate) 显示是列名DeliverDate无效
把代码贴出来,大家看下吧 select 1 as id, a.ItemNo as StockNo, b.ProductName, c.StoreDate as StoreDate, null as DeliverBillNo, null as DeliverDate, c.ItemNo as StoreNo, null as CustName, a.UnitPrice, a.StockAmount, null as Amount, dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate) as RemainAmount, --dbo.uf_DeliverRemainAmount(b.ItemNo,'c.StoreDate') , --a.StockAmount as RemainAmount, (a.UnitPrice *dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate)) as RemainMoney, d.SuppName, e.Name, a.Comment from ((((tStock as a join tProduct as b on a.Prod_No=b.ItemNo) join tStoreBill as c on a.Store_No=c.ItemNo) join tSupplier as d on c.Sup_No=d.ItemNo) join tEmployee as e on c.Handler_Emp_No=e.ItemNo) where 1=1 union all select 2 as id, aa.Stock_No as StockNo, bb.ProductName, null as StoreDate, aa.DeliverBill_No as DeliverBillNo, cc.DeliverDate as DeliverDate, null as StoreNo, dd.CustName, aa.UnitPrice, null as StockAmount, aa.Amount, dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate) as RemainAmount, (aa.UnitPrice * dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate)) as RemainMoney, null as SuppName, ee.Name, aa.Comment from ((((tDeliver as aa join tProduct as bb on aa.Product_No=bb.ItemNo) join tDeliverBill as cc on aa.DeliverBill_No=cc.ItemNo) join tCustomer as dd on aa.Customer=dd.ItemNo) join tEmployee as ee on cc.Leader=ee.ItemNo) where 1=1 order by 这怎么写
这样就不行,order by isnull(StoreDate,DeliverDate) 显示是列名DeliverDate无效 ---没道理吧
create table ta(id int,col1 datetime,col2 datetime ) goinsert ta select 1,'2001-01-01',null insert ta select 1,'2001-03-01',null insert ta select 1,null,'2001-02-01' insert ta select 1,null,'2001-05-01' select * from ta order by isnull(col1,col2) /*id col1 col2 ----------- ------------------------------------------------------ ------------------------------------------------------ 1 2001-01-01 00:00:00.000 NULL 1 NULL 2001-02-01 00:00:00.000 1 2001-03-01 00:00:00.000 NULL 1 NULL 2001-05-01 00:00:00.000(所影响的行数为 4 行)(所影响的行数为 1 行)*/ drop table ta
太长没细看,select * from(select 1 as id, a.ItemNo as StockNo, b.ProductName, c.StoreDate as StoreDate, null as DeliverBillNo, null as DeliverDate, c.ItemNo as StoreNo, null as CustName, a.UnitPrice, a.StockAmount, null as Amount, dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate) as RemainAmount, --dbo.uf_DeliverRemainAmount(b.ItemNo, 'c.StoreDate ') , --a.StockAmount as RemainAmount, (a.UnitPrice *dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate)) as RemainMoney, d.SuppName, e.Name, a.Comment from ((((tStock as a join tProduct as b on a.Prod_No=b.ItemNo) join tStoreBill as c on a.Store_No=c.ItemNo) join tSupplier as d on c.Sup_No=d.ItemNo) join tEmployee as e on c.Handler_Emp_No=e.ItemNo) where 1=1 union all select 2 as id, aa.Stock_No as StockNo, bb.ProductName, null as StoreDate, aa.DeliverBill_No as DeliverBillNo, cc.DeliverDate as DeliverDate, null as StoreNo, dd.CustName, aa.UnitPrice, null as StockAmount, aa.Amount, dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate) as RemainAmount, (aa.UnitPrice * dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate)) as RemainMoney, null as SuppName, ee.Name, aa.Comment from ((((tDeliver as aa join tProduct as bb on aa.Product_No=bb.ItemNo) join tDeliverBill as cc on aa.DeliverBill_No=cc.ItemNo) join tCustomer as dd on aa.Customer=dd.ItemNo) join tEmployee as ee on cc.Leader=ee.ItemNo) where 1=1) order by isnull(StoreDate,DeliverDate)
select x.* from (select 1 as id, a.ItemNo as StockNo, b.ProductName, c.StoreDate as StoreDate, null as DeliverBillNo, null as DeliverDate, c.ItemNo as StoreNo, null as CustName, a.UnitPrice, a.StockAmount, null as Amount, dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate) as RemainAmount, --dbo.uf_DeliverRemainAmount(b.ItemNo, 'c.StoreDate ') , --a.StockAmount as RemainAmount, (a.UnitPrice *dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate)) as RemainMoney, d.SuppName, e.Name, a.Comment from ((((tStock as a join tProduct as b on a.Prod_No=b.ItemNo) join tStoreBill as c on a.Store_No=c.ItemNo) join tSupplier as d on c.Sup_No=d.ItemNo) join tEmployee as e on c.Handler_Emp_No=e.ItemNo) where 1=1 union all select 2 as id, aa.Stock_No as StockNo, bb.ProductName, null as StoreDate, aa.DeliverBill_No as DeliverBillNo, cc.DeliverDate as DeliverDate, null as StoreNo, dd.CustName, aa.UnitPrice, null as StockAmount, aa.Amount, dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate) as RemainAmount, (aa.UnitPrice * dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate)) as RemainMoney, null as SuppName, ee.Name, aa.Comment from ((((tDeliver as aa join tProduct as bb on aa.Product_No=bb.ItemNo) join tDeliverBill as cc on aa.DeliverBill_No=cc.ItemNo) join tCustomer as dd on aa.Customer=dd.ItemNo) join tEmployee as ee on cc.Leader=ee.ItemNo) ) x order by isnull(x.StoreDate,x.DeliverDate)把前一個查詢套起來就可以了 join沒有order by 就不需要where條件了
a b Storedate DeliverDate 我想两个都显示出来。a是入库表,b是出库表。 按日期排序
select *
from table
order by (case when 入库时间 is null then 出库时间 else 入库时间 end)
select * form tablename
order by isnull(入库时间,出库时间)
select * form stock
order by isnull(StoreDate,DeliverDate)
order by StoreDate,DeliverDate
可是,这样就不行,order by isnull(StoreDate,DeliverDate) 显示是列名DeliverDate无效
把代码贴出来,大家看下吧
select 1 as id,
a.ItemNo as StockNo,
b.ProductName,
c.StoreDate as StoreDate,
null as DeliverBillNo,
null as DeliverDate,
c.ItemNo as StoreNo,
null as CustName,
a.UnitPrice,
a.StockAmount,
null as Amount,
dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate) as RemainAmount,
--dbo.uf_DeliverRemainAmount(b.ItemNo,'c.StoreDate') ,
--a.StockAmount as RemainAmount,
(a.UnitPrice *dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate)) as RemainMoney,
d.SuppName,
e.Name,
a.Comment
from ((((tStock as a join tProduct as b on a.Prod_No=b.ItemNo)
join tStoreBill as c on a.Store_No=c.ItemNo)
join tSupplier as d on c.Sup_No=d.ItemNo)
join tEmployee as e on c.Handler_Emp_No=e.ItemNo)
where 1=1
union all
select 2 as id,
aa.Stock_No as StockNo,
bb.ProductName,
null as StoreDate,
aa.DeliverBill_No as DeliverBillNo,
cc.DeliverDate as DeliverDate,
null as StoreNo,
dd.CustName,
aa.UnitPrice,
null as StockAmount,
aa.Amount,
dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate) as RemainAmount,
(aa.UnitPrice * dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate)) as RemainMoney,
null as SuppName,
ee.Name,
aa.Comment
from ((((tDeliver as aa join tProduct as bb on aa.Product_No=bb.ItemNo)
join tDeliverBill as cc on aa.DeliverBill_No=cc.ItemNo)
join tCustomer as dd on aa.Customer=dd.ItemNo)
join tEmployee as ee on cc.Leader=ee.ItemNo)
where 1=1
order by 这怎么写
---没道理吧
goinsert ta select 1,'2001-01-01',null
insert ta select 1,'2001-03-01',null
insert ta select 1,null,'2001-02-01'
insert ta select 1,null,'2001-05-01'
select *
from ta
order by isnull(col1,col2)
/*id col1 col2
----------- ------------------------------------------------------ ------------------------------------------------------
1 2001-01-01 00:00:00.000 NULL
1 NULL 2001-02-01 00:00:00.000
1 2001-03-01 00:00:00.000 NULL
1 NULL 2001-05-01 00:00:00.000(所影响的行数为 4 行)(所影响的行数为 1 行)*/
drop table ta
from(select 1 as id,
a.ItemNo as StockNo,
b.ProductName,
c.StoreDate as StoreDate,
null as DeliverBillNo,
null as DeliverDate,
c.ItemNo as StoreNo,
null as CustName,
a.UnitPrice,
a.StockAmount,
null as Amount,
dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate) as RemainAmount,
--dbo.uf_DeliverRemainAmount(b.ItemNo, 'c.StoreDate ') ,
--a.StockAmount as RemainAmount,
(a.UnitPrice *dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate)) as RemainMoney,
d.SuppName,
e.Name,
a.Comment
from ((((tStock as a join tProduct as b on a.Prod_No=b.ItemNo)
join tStoreBill as c on a.Store_No=c.ItemNo)
join tSupplier as d on c.Sup_No=d.ItemNo)
join tEmployee as e on c.Handler_Emp_No=e.ItemNo)
where 1=1
union all
select 2 as id,
aa.Stock_No as StockNo,
bb.ProductName,
null as StoreDate,
aa.DeliverBill_No as DeliverBillNo,
cc.DeliverDate as DeliverDate,
null as StoreNo,
dd.CustName,
aa.UnitPrice,
null as StockAmount,
aa.Amount,
dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate) as RemainAmount,
(aa.UnitPrice * dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate)) as RemainMoney,
null as SuppName,
ee.Name,
aa.Comment
from ((((tDeliver as aa join tProduct as bb on aa.Product_No=bb.ItemNo)
join tDeliverBill as cc on aa.DeliverBill_No=cc.ItemNo)
join tCustomer as dd on aa.Customer=dd.ItemNo)
join tEmployee as ee on cc.Leader=ee.ItemNo)
where 1=1)
order by isnull(StoreDate,DeliverDate)
x.*
from
(select 1 as id,
a.ItemNo as StockNo,
b.ProductName,
c.StoreDate as StoreDate,
null as DeliverBillNo,
null as DeliverDate,
c.ItemNo as StoreNo,
null as CustName,
a.UnitPrice,
a.StockAmount,
null as Amount,
dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate) as RemainAmount,
--dbo.uf_DeliverRemainAmount(b.ItemNo, 'c.StoreDate ') ,
--a.StockAmount as RemainAmount,
(a.UnitPrice *dbo.uf_deliverremainamount(a.Prod_No,c.StoreDate)) as RemainMoney,
d.SuppName,
e.Name,
a.Comment
from ((((tStock as a join tProduct as b on a.Prod_No=b.ItemNo)
join tStoreBill as c on a.Store_No=c.ItemNo)
join tSupplier as d on c.Sup_No=d.ItemNo)
join tEmployee as e on c.Handler_Emp_No=e.ItemNo)
where 1=1
union all
select 2 as id,
aa.Stock_No as StockNo,
bb.ProductName,
null as StoreDate,
aa.DeliverBill_No as DeliverBillNo,
cc.DeliverDate as DeliverDate,
null as StoreNo,
dd.CustName,
aa.UnitPrice,
null as StockAmount,
aa.Amount,
dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate) as RemainAmount,
(aa.UnitPrice * dbo.uf_DeliverRemainAmount(aa.Product_No,cc.DeliverDate)) as RemainMoney,
null as SuppName,
ee.Name,
aa.Comment
from ((((tDeliver as aa join tProduct as bb on aa.Product_No=bb.ItemNo)
join tDeliverBill as cc on aa.DeliverBill_No=cc.ItemNo)
join tCustomer as dd on aa.Customer=dd.ItemNo)
join tEmployee as ee on cc.Leader=ee.ItemNo)
) x
order by isnull(x.StoreDate,x.DeliverDate)把前一個查詢套起來就可以了
join沒有order by 就不需要where條件了
Storedate DeliverDate
我想两个都显示出来。a是入库表,b是出库表。
按日期排序