select billid,billcode,vendor,userdef10 =
case when b.qty > b.exeqty then '未完成' else '完成' end
from
P_ORDERD left join
(
select billid,sum(qty) qty,sum(exeqty) exeqty
from p_orderd
group by billid) B
on P_ORDERD.billid = b.billid
case when b.qty > b.exeqty then '未完成' else '完成' end
from
P_ORDERD left join
(
select billid,sum(qty) qty,sum(exeqty) exeqty
from p_orderd
group by billid) B
on P_ORDERD.billid = b.billid
解决方案 »
- SQL 工具书
- 怎样查询数据库连接的速度是快是慢?
- 不知道这么问题实现起来是不是很难
- 为什么存储过程中累加后变为null了?
- sql server2000 作业可不可以调用WEB服务?
- 数据库复制中出现的问题!着急呀 已经折腾我两天了。
- 一个datetime型的变量@zdate ,内容是2003-09-06,经过CAST(@zdate AS VARCHAR(10))变换以后怎么变成09 06 2003了呢?顺序变了,我想要原
- 将一个sql语句在前台执行和用存储过程在后台执行速度有差异吗?各自还有什么利弊?
- 请问:如何在sql服务器上实现出版和订阅的关系?
- 请问我在数据库中用自增的字段的问题!请教!
- SELECT如何实现上行数据和本行数据相加或相减
- 多表间的查询
create table p_order
(billid int,billcode varchar(100),vendor varchar(100),userdef10 varchar(100))
insert p_order
select 1001 , 'PO0907001' , 'A供应商' , NULL union all
select 1002 , 'PO0907002' , 'B供应商' , NULL union all
select 1003 , 'PO0907003', 'C供应商' , NULL create table p_orderd
(billid int,itemno int,goodsid int,qty int,exeqty int)
insert p_orderd
select 1001 , 1 , 71001 , 100 , 80 union all
select 1001 , 2 , 72003 , 200 , 200 union all
select 1002 , 1 , 8001 , 800 , 800 union all
select 1002 , 2 , 8002 , 700 , 700 union all
select 1003 , 1 , 9001 , 320 , 330 union all
select 1003 , 2 , 9003 , 500 , 500
select c.billid,c.billcode,c.vendor,
userdeff10=case when c.qty>c.exeqty then '完成'else '未完成' end
from
(select a.billid as billid,
a.billcode as billcode,
a.vendor as vendor,
SUM(qty) as qty,
SUM(exeqty)as exeqty
from p_order a join p_orderd b on a.billid =b.billid
group by a.billid,
a.billcode,
a.vendor)c
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 09:27:50
---------------------------------
--> 生成测试数据表-P_ORDER1if not object_id('P_ORDER') is null
drop table P_ORDER
Go
Create table P_ORDER([BILLID] int,[BILLCODE] nvarchar(9),[VENDOR] nvarchar(4),[USERDEF10] nvarchar(4))
Insert P_ORDER
select 1001,'PO0907001','A供应商',null union all
select 1002,'PO0907002','B供应商',null union all
select 1003,'PO0907003','C供应商',null
Go--> 生成测试数据表-P_ORDERDif not object_id('P_ORDERD') is null
drop table P_ORDERD
Go
Create table P_ORDERD([BILLID] int,[ITEMNO] int,[GOODSID] int,[QTY] int,[EXEQTY] int)
Insert P_ORDERD
select 1001,1,71001,100,80 union all
select 1001,2,72003,200,200 union all
select 1002,1,8001,800,800 union all
select 1002,2,8002,700,700 union all
select 1003,1,9001,320,330 union all
select 1003,2,9003,500,500
Go-->SQL查询如下:update a set
[USERDEF10]=case when [EXEQTY]<[QTY] then '未完成' else '已完成' end
from (
select [BILLID],sum([QTY]) [QTY],sum([EXEQTY]) [EXEQTY]
from P_ORDERD
group by [BILLID]
) as b,P_ORDER a
where a.[BILLID]=b.[BILLID]select * from P_ORDER/*
BILLID BILLCODE VENDOR USERDEF10
----------- --------- ------ ---------
1001 PO0907001 A供应商 未完成
1002 PO0907002 B供应商 已完成
1003 PO0907003 C供应商 已完成(3 行受影响)
*/
create table p_order
(billid int,billcode varchar(100),vendor varchar(100),userdef10 varchar(100))
insert p_order
select 1001 , 'PO0907001' , 'A供应商' , NULL union all
select 1002 , 'PO0907002' , 'B供应商' , NULL union all
select 1003 , 'PO0907003', 'C供应商' , NULL create table p_orderd
(billid int,itemno int,goodsid int,qty int,exeqty int)
insert p_orderd
select 1001 , 1 , 71001 , 100 , 80 union all
select 1001 , 2 , 72003 , 200 , 200 union all
select 1002 , 1 , 8001 , 800 , 800 union all
select 1002 , 2 , 8002 , 700 , 700 union all
select 1003 , 1 , 9001 , 320 , 330 union all
select 1003 , 2 , 9003 , 500 , 500
select c.billid,c.billcode,c.vendor,
userdeff10=case when c.qty<=c.exeqty then '完成'else '未完成' end
from
(select a.billid as billid,
a.billcode as billcode,
a.vendor as vendor,
SUM(qty) as qty,
SUM(exeqty)as exeqty
from p_order a join p_orderd b on a.billid =b.billid
group by a.billid,
a.billcode,
a.vendor)c
create function get_status(@billid int)
returns varchar(10)
as
begin
declare @status varchar(10)
SELECT @status = case WHEN sum(qty) - sum(exeqty) > 0 THEN '未完成'ELSE '完成' END
FROM p_orderd
WHERE billid = @billid
GROUP BY billid
return isnull(@status,'未完成')
end
go
ALTER TABLE p_order DROP COLUMN userdef10
go
ALTER TABLE p_order ADD userdef10 AS dbo.get_status(billid)
go
select * from p_order