求一条极品语句

fancydong 2009-09-05 04:55:12
更新语句，要更新指定的一段日期，用t_rm_daysum里的数据按照相应分店和日期，更新用t_cash_daysum里的该分店该日期pay_amt金额最大的记录减掉指定商品10001的金额，举例如下，最后一个是我想得到的结果
数据t_rm_daysum
item_no(商品) branch_no(分店) oper_date(日期) sale_amt（销售金额）
10001 01 2009-08-01 88
10001 02 2009-08-01 66
10002 01 2009-08-01 100
10002 02 2009-08-01 200
10001 01 2009-08-02 200
10001 02 2009-08-02 300
10002 01 2009-08-02 200
10002 02 2009-08-02 700

数据表t_cash_daysum
oper_ID(操作员) branch_no(分店) oper_date(日期) pay_amt（收银金额）
9999 01 2009-08-01 150
8888 01 2009-08-01 33
9999 02 2009-08-01 100
7777 02 2009-08-01 166
9999 01 2009-08-02 100
2222 01 2009-08-02 300
3333 02 2009-08-02 100
1111 02 2009-08-02 900
更新过的，数据表t_cash_daysum
oper_ID(操作员) branch_no(分店) oper_date(日期) pay_amt（收银金额）
9999 01 2009-08-01 67 150-88
8888 01 2009-08-01 33
9999 02 2009-08-01 100
7777 02 2009-08-01 100 166-66
9999 01 2009-08-02 100
2222 01 2009-08-02 100 300-200
3333 02 2009-08-02 100
1111 02 2009-08-02 600 900-300
...全文
236 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
佐佐吉牧 2009-09-07
  • 打赏
  • 举报
 回复
大哥么么、、、你们写的不是代码。。是艺术啊！！！！！！
happybuttom 2009-09-07
  • 打赏
  • 举报
 回复
LZ怎么不给分呀，不是有正解吗？
华夏小卒 2009-09-07
  • 打赏
  • 举报
 回复
别笑话偶啊，
偶是节省时间，没排版啊
lihan6415151528 2009-09-07
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 js_szy 的回复:]
SQL codeIfnotobject_id('t_rm_daysum')isnullDroptable t_rm_daysumGoCreatetable t_rm_daysum (item_noint, branch_novarchar(2),oper_datedatetime, sale_amtint)Insert t_rm_daysumSelect10001 ,'01' ,'2¡­
[/Quote]
有意思~
liangCK 2009-09-07
  • 打赏
  • 举报
 回复 
--> 生成测试数据: @t_rm_daysum

DECLARE @t_rm_daysum TABLE (item_no INT,branch_no VARCHAR(2),oper_date DATETIME,sale_amt INT)

INSERT INTO @t_rm_daysum

SELECT 10001,'01','2009-08-01',88 UNION ALL

SELECT 10001,'02','2009-08-01',66 UNION ALL

SELECT 10002,'01','2009-08-01',100 UNION ALL

SELECT 10002,'02','2009-08-01',200 UNION ALL

SELECT 10001,'01','2009-08-02',200 UNION ALL

SELECT 10001,'02','2009-08-02',300 UNION ALL

SELECT 10002,'01','2009-08-02',200 UNION ALL

SELECT 10002,'02','2009-08-02',700

 

--> 生成测试数据: @t_cash_daysum

DECLARE @t_cash_daysum TABLE (oper_ID INT,branch_no VARCHAR(2),oper_date DATETIME,pay_amt INT)

INSERT INTO @t_cash_daysum

SELECT 9999,'01','2009-08-01',150 UNION ALL

SELECT 8888,'01','2009-08-01',33 UNION ALL

SELECT 9999,'02','2009-08-01',100 UNION ALL

SELECT 7777,'02','2009-08-01',166 UNION ALL

SELECT 9999,'01','2009-08-02',100 UNION ALL

SELECT 2222,'01','2009-08-02',300 UNION ALL

SELECT 3333,'02','2009-08-02',100 UNION ALL

SELECT 1111,'02','2009-08-02',900



--SQL查询如下:



UPDATE A SET

    pay_amt = A.pay_amt - B.sale_amt

FROM @t_cash_daysum AS A

    JOIN @t_rm_daysum AS B

ON A.branch_no = B.branch_no AND A.oper_date = B.oper_date

    AND B.item_no = 10001 AND A.oper_date BETWEEN '2009-08-01' AND '2009-08-02' --这里加时间

WHERE NOT EXISTS(SELECT * FROM @t_cash_daysum

                 WHERE branch_no = A.branch_no AND oper_date = A.oper_date

                      AND pay_amt > A.pay_amt);





SELECT * FROM @t_cash_daysum;
devilidea 2009-09-07
  • 打赏
  • 举报
 回复
写的不是代码写的是寂寞
fancydong 2009-09-07
  • 打赏
  • 举报
 回复
说明：需要修改一个时间段的数据。。。。。是否只要在下列where not exists里加入时间段条件
UPDATE A SET
pay_amt = A.pay_amt - B.sale_amt
FROM @t_cash_daysum AS A
JOIN @t_rm_daysum AS B
ON A.branch_no = B.branch_no AND A.oper_date = B.oper_date
AND B.item_no = 10001
WHERE NOT EXISTS(SELECT * FROM @t_cash_daysum
WHERE branch_no = A.branch_no AND oper_date = A.oper_date
AND pay_amt > A.pay_amt);
fancydong 2009-09-07
  • 打赏
  • 举报
 回复
还有没其他的方法
bancxc 2009-09-07
  • 打赏
  • 举报
 回复
不错
[Quote=引用 7 楼 liangck 的回复:]
SQL code-------------------------------------------
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-05 17:02:24
---------------------------------------------> 生成测试数据: @t_rm_daysumDECLARE@t_rm_daysumTABLE (item_noINT,branch_noVARCHAR(2),oper_dateDATETIME,sale_amtINT)INSERTINTO@t_rm_daysumSELECT10001,'01','2009-08-01',88UNIONALLSELECT10001,'02','2009-08-01',66UNIONALLSELECT10002,'01','2009-08-01',100UNIONALLSELECT10002,'02','2009-08-01',200UNIONALLSELECT10001,'01','2009-08-02',200UNIONALLSELECT10001,'02','2009-08-02',300UNIONALLSELECT10002,'01','2009-08-02',200UNIONALLSELECT10002,'02','2009-08-02',700--> 生成测试数据: @t_cash_daysumDECLARE@t_cash_daysumTABLE (oper_IDINT,branch_noVARCHAR(2),oper_dateDATETIME,pay_amtINT)INSERTINTO@t_cash_daysumSELECT9999,'01','2009-08-01',150UNIONALLSELECT8888,'01','2009-08-01',33UNIONALLSELECT9999,'02','2009-08-01',100UNIONALLSELECT7777,'02','2009-08-01',166UNIONALLSELECT9999,'01','2009-08-02',100UNIONALLSELECT2222,'01','2009-08-02',300UNIONALLSELECT3333,'02','2009-08-02',100UNIONALLSELECT1111,'02','2009-08-02',900--SQL查询如下:UPDATE ASET
pay_amt= A.pay_amt- B.sale_amtFROM@t_cash_daysumAS AJOIN@t_rm_daysumAS BON A.branch_no= B.branch_noAND A.oper_date= B.oper_dateAND B.item_no=10001WHERENOTEXISTS(SELECT*FROM@t_cash_daysumWHERE branch_no= A.branch_noAND oper_date= A.oper_dateAND pay_amt> A.pay_amt);SELECT*FROM@t_cash_daysum/*
oper_ID branch_no oper_date pay_amt
----------- --------- ----------------------- -----------
9999 01 2009-08-01 00:00:00.000 62
8888 01 2009-08-01 00:00:00.000 33
9999 02 2009-08-01 00:00:00.000 100
7777 02 2009-08-01 00:00:00.000 100
9999 01 2009-08-02 00:00:00.000 100
2222 01 2009-08-02 00:00:00.000 100
3333 02 2009-08-02 00:00:00.000 100
1111 02 2009-08-02 00:00:00.000 600

(8 row(s) affected)*/
[/Quote]
翼帆 2009-09-07
  • 打赏
  • 举报
 回复
pfpf!!小卒建表艺术，应该流行一下
config_man 2009-09-05
  • 打赏
  • 举报
 回复
围观....
guguda2008 2009-09-05
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 js_szy 的回复:]
SQL codeIfnotobject_id('t_rm_daysum')isnullDroptable t_rm_daysumGoCreatetable t_rm_daysum (item_noint, branch_novarchar(2),oper_datedatetime, sale_amtint)Insert t_rm_daysumSelect10001 ,'01' ,'2¡­
[/Quote]
小卒的建表这么艺术
zzz1975 2009-09-05
  • 打赏
  • 举报
 回复
多
lihan6415151528 2009-09-05
  • 打赏
  • 举报
 回复
happybuttom 2009-09-05
  • 打赏
  • 举报
 回复
实在是佩服小梁了。
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复 
/*

更新语句，要更新指定的一段日期，用t_rm_daysum里的数据按照相应分店和日期，

更新用t_cash_daysum里的该分店该日期pay_amt金额最大的记录减掉指定商品10001的金额

，举例如下，最后一个是我想得到的结果 

数据t_rm_daysum 

*/



DECLARE @t_rm_daysum TABLE (item_no INT,branch_no VARCHAR(2),oper_date DATETIME,sale_amt INT)

INSERT INTO @t_rm_daysum

SELECT 10001,'01','2009-08-01',88 UNION ALL

SELECT 10001,'02','2009-08-01',66 UNION ALL

SELECT 10002,'01','2009-08-01',100 UNION ALL

SELECT 10002,'02','2009-08-01',200 UNION ALL

SELECT 10001,'01','2009-08-02',200 UNION ALL

SELECT 10001,'02','2009-08-02',300 UNION ALL

SELECT 10002,'01','2009-08-02',200 UNION ALL

SELECT 10002,'02','2009-08-02',700





--> 生成测试数据: @t_cash_daysum

DECLARE @t_cash_daysum TABLE (oper_ID INT,branch_no VARCHAR(2),oper_date DATETIME,pay_amt INT)

INSERT INTO @t_cash_daysum

SELECT 9999,'01','2009-08-01',150 UNION ALL

SELECT 8888,'01','2009-08-01',33 UNION ALL

SELECT 9999,'02','2009-08-01',100 UNION ALL

SELECT 7777,'02','2009-08-01',166 UNION ALL

SELECT 9999,'01','2009-08-02',100 UNION ALL

SELECT 2222,'01','2009-08-02',300 UNION ALL

SELECT 3333,'02','2009-08-02',100 UNION ALL

SELECT 1111,'02','2009-08-02',900





select C.oper_ID,C.branch_no,C.oper_date,sum(C.pay_amt-R.sale_amt) pay_amt

 from 

(

select * from @t_rm_daysum R where not exists

(select * from @t_rm_daysum where R.branch_no=branch_no and R.oper_date=oper_date 

and R.sale_amt>sale_amt) and item_no='10001'

) R

join 

(

select * from @t_cash_daysum T where not exists

(select * from @t_cash_daysum where T.oper_date=oper_date and T.branch_no=branch_no 

and T.pay_amt<pay_amt)

) C 

on C.branch_no=R.branch_no and C.oper_date=R.oper_date

group by C.oper_ID,C.branch_no,C.oper_date

union all

select * from @t_cash_daysum T where not exists

(select * from @t_cash_daysum where T.oper_date=oper_date and T.branch_no=branch_no 

and T.pay_amt>pay_amt)



oper_ID     branch_no oper_date               pay_amt

----------- --------- ----------------------- -----------

1111        02        2009-08-02 00:00:00.000 600

2222        01        2009-08-02 00:00:00.000 100

7777        02        2009-08-01 00:00:00.000 100

9999        01        2009-08-01 00:00:00.000 62

8888        01        2009-08-01 00:00:00.000 33

9999        02        2009-08-01 00:00:00.000 100

9999        01        2009-08-02 00:00:00.000 100

3333        02        2009-08-02 00:00:00.000 100
华夏小卒 2009-09-05
  • 打赏
  • 举报
 回复 


If not object_id('t_rm_daysum ') is null

    Drop table t_rm_daysum 

Go

Create table t_rm_daysum (item_no int, branch_no varchar(2),oper_date datetime, sale_amt int)

Insert t_rm_daysum  Select

10001 ,           '01'        ,      '2009-08-01',      88  union all Select

10001  ,          '02'       ,       '2009-08-01'  ,    66 union all Select

10002   ,         '01'      ,        '2009-08-01' ,     100 union all Select

10002    ,        '02'     ,         '2009-08-01'  ,    200 union all Select

10001     ,       '01'    ,          '2009-08-02'   ,   200 union all Select

10001      ,      '02'   ,           '2009-08-02'    ,  300 union all Select

10002       ,     '01'  ,            '2009-08-02'     , 200 union all Select

10002        ,    '02' ,             '2009-08-02'      ,700 



If not object_id('t_cash_daysum') is null

    Drop table t_cash_daysum

Go

Create table t_cash_daysum(oper_ID int,branch_no varchar(2), oper_date datetime , pay_amt int)

Insert t_cash_daysum Select

9999    ,          '01'        ,      '2009-08-01',      150 union all select  

8888     ,         '01'       ,       '2009-08-01' ,     33  union all select  

9999      ,        '02'      ,        '2009-08-01'  ,    100  union all select  

7777       ,       '02'     ,         '2009-08-01'   ,   166  union all select  

9999        ,      '01'    ,          '2009-08-02'    ,  100  union all select  

2222         ,     '01'   ,           '2009-08-02'     , 300  union all select  

3333          ,    '02'  ,            '2009-08-02'      ,100  union all select  

1111           ,   '02' ,             '2009-08-02'      ,900 



update gx

set pay_amt=pay_amt-sale_amt

from(

select * from t_cash_daysum t

where not exists(select * from t_cash_daysum where branch_no=t.branch_no and oper_date=t.oper_date and pay_amt>t.pay_amt)

)gx,t_rm_daysum b

where gx.branch_no=b.branch_no and gx.oper_date=b.oper_date and item_no='10001' 



select * from t_cash_daysum



oper_ID     branch_no oper_date               pay_amt

----------- --------- ----------------------- -----------

9999        01        2009-08-01 00:00:00.000 62

8888        01        2009-08-01 00:00:00.000 33

9999        02        2009-08-01 00:00:00.000 100

7777        02        2009-08-01 00:00:00.000 100

9999        01        2009-08-02 00:00:00.000 100

2222        01        2009-08-02 00:00:00.000 100

3333        02        2009-08-02 00:00:00.000 100

1111        02        2009-08-02 00:00:00.000 600



(8 行受影响)
ws_hgo 2009-09-05
  • 打赏
  • 举报
 回复 
select C.oper_ID,C.branch_no,C.oper_date,sum(sum(C.pay_amt)-sum(isnull(R.sale_amt,0))) as pay_amt from t_cash_daysum C left join t_rm_daysum R on C.branch_no=R.branch_no and C.oper_date=R.oper_date
liangCK 2009-09-05
  • 打赏
  • 举报
 回复 
-------------------------------------------

--  Author : liangCK 小梁

--  Comment: 小梁 爱 兰儿

--  Date   : 2009-09-05 17:02:24

-------------------------------------------

 

--> 生成测试数据: @t_rm_daysum

DECLARE @t_rm_daysum TABLE (item_no INT,branch_no VARCHAR(2),oper_date DATETIME,sale_amt INT)

INSERT INTO @t_rm_daysum

SELECT 10001,'01','2009-08-01',88 UNION ALL

SELECT 10001,'02','2009-08-01',66 UNION ALL

SELECT 10002,'01','2009-08-01',100 UNION ALL

SELECT 10002,'02','2009-08-01',200 UNION ALL

SELECT 10001,'01','2009-08-02',200 UNION ALL

SELECT 10001,'02','2009-08-02',300 UNION ALL

SELECT 10002,'01','2009-08-02',200 UNION ALL

SELECT 10002,'02','2009-08-02',700

 

--> 生成测试数据: @t_cash_daysum

DECLARE @t_cash_daysum TABLE (oper_ID INT,branch_no VARCHAR(2),oper_date DATETIME,pay_amt INT)

INSERT INTO @t_cash_daysum

SELECT 9999,'01','2009-08-01',150 UNION ALL

SELECT 8888,'01','2009-08-01',33 UNION ALL

SELECT 9999,'02','2009-08-01',100 UNION ALL

SELECT 7777,'02','2009-08-01',166 UNION ALL

SELECT 9999,'01','2009-08-02',100 UNION ALL

SELECT 2222,'01','2009-08-02',300 UNION ALL

SELECT 3333,'02','2009-08-02',100 UNION ALL

SELECT 1111,'02','2009-08-02',900



--SQL查询如下:



UPDATE A SET

    pay_amt = A.pay_amt - B.sale_amt

FROM @t_cash_daysum AS A

    JOIN @t_rm_daysum AS B

ON A.branch_no = B.branch_no AND A.oper_date = B.oper_date

    AND B.item_no = 10001

WHERE NOT EXISTS(SELECT * FROM @t_cash_daysum

                 WHERE branch_no = A.branch_no AND oper_date = A.oper_date

                      AND pay_amt > A.pay_amt);





SELECT * FROM @t_cash_daysum



/*

oper_ID     branch_no oper_date               pay_amt

----------- --------- ----------------------- -----------

9999        01        2009-08-01 00:00:00.000 62

8888        01        2009-08-01 00:00:00.000 33

9999        02        2009-08-01 00:00:00.000 100

7777        02        2009-08-01 00:00:00.000 100

9999        01        2009-08-02 00:00:00.000 100

2222        01        2009-08-02 00:00:00.000 100

3333        02        2009-08-02 00:00:00.000 100

1111        02        2009-08-02 00:00:00.000 600



(8 row(s) affected)

*/
liangCK 2009-09-05
  • 打赏
  • 举报
 回复
67 150-88
???
加载更多回复（5）
Day03_07_if语句
选择结构： 条件语句可根据不同的条件执行不同的语句。条件语句包括if条件语句和switch多分支语句，本章介绍条件语句的用法。if/switch ...2.如果你的if里面只有一条语句，原则上是可以省略大括号的，但是
java函数return语句_一个函数应该只有一个return语句吗？
是否有充分的理由说明为什么在函数中仅包含一个return语句是一种更好的做法？还是在逻辑上正确地从函数中返回就可以，这意味着函数中可能有很多return语句？#1楼这可能是一个不同寻常的观点，但是我认为，任何认为...
信息收集----谷歌语句
天津特产”，搜索出的结果，一共搜索到7,750,000 条，并且都是和特产有关的。 2、减号（“―”） 减号和加号相反，就是取出的意思，他会在我们搜索结果中去除我们写入的内容、例如“天津 ―特产” 。 3、...
一分钟学会流程控制语句
1.Linux脚本是一种命令序列，使用脚本可以自动化执行一系列的操作。它们经常用于自动化重复的任务和批量处理任务。在Linux中，脚本是由一系列命令组成的纯文本文件，其中包含一些特定的脚本语言格式。2.脚本文件通常...
js return加分号_JavaScript 语句后应该加分号么？
而且，同样是一条特殊规则，行首加分号的规则比函数表达式后面加分号的规则其实要简单！ var a = function () { ... } [1,2,3].forEach(...) 还是以上面代码为例。 行首是否要加分号，我只要看本行的第一个字符就...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章